Insertion of UserId into tblOrder fails

Question

Hello Team,

The error message I'm getting says that UserId does not exist, and I tried to create a relationship between user table and tblOrder table but it also says foreign key conflict whenever I tried to insert data so i remove the relationship, the Schema for both tblUser and tblOrder is Order, and though the UserId is present in the tblUser, kindly assist.

public int AddOrder(OrderViewModel objOrderViewModel)
        {
    // Check if User Exists
            var userExists = objRestaurantDBEntities.tblUsers
                          .Any(u => u.UserId == objOrderViewModel.UserId);

            if (!userExists)
            {
                throw new Exception("? Error: UserId (" + objOrderViewModel.UserId + ") does not exist in tblUser.");
            }

    // Create Order Object
    tblOrder objOrder = new tblOrder
    {
        CustomerId = objOrderViewModel.CustomerId,
        FinalTotal = objOrderViewModel.FinalTotal,
        OrderDate = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"),
        OrderNumber = DateTime.Now.ToString("ddMMMyyyyHHmmss"),
        PaymentTypeId = objOrderViewModel.PaymentTypeId,
        UserId = objOrderViewModel.UserId
    };

objRestaurantDBEntities.tblOrders.Add(objOrder);
objRestaurantDBEntities.SaveChanges();

int OrderId = objOrder.OderId; // ? Ensure correct field name

// List to store Order Details
List<tblOrderDetail> orderDetailsList = new List<tblOrderDetail>();

    foreach (var item in objOrderViewModel.ListofOrderDetailViewModel)
    {
        tblOrderDetail objOrderDetails = new tblOrderDetail
        {
            OderId = OrderId,
            Discount = item.Discount,
            ItemId = item.ItemId,
            Total = item.Total,
            UnitPrice = item.UnitPrice,
            Quantity = Convert.ToInt32(item.Quantity),
            Vat = item.Vat
        };

orderDetailsList.Add(objOrderDetails);

        // Check if Item exists in stock before updating
        var tblQty = objRestaurantDBEntities.tblQuantities
                        .SingleOrDefault(x => x.ItemId == item.ItemId);

        if (tblQty != null)
        {
            int newQuantity = Convert.ToInt32(tblQty.Quantity) - Convert.ToInt32(item.Quantity);
            tblQty.Quantity = newQuantity >= 0 ? newQuantity : 0; // Prevent negative stock

            objRestaurantDBEntities.Entry(tblQty).State = EntityState.Modified;
        }
    }

    // Save all Order Details in one go
    objRestaurantDBEntities.tblOrderDetails.AddRange(orderDetailsList);
    objRestaurantDBEntities.SaveChanges(); // ? Single SaveChanges() for better performance

return OrderId;
}
}

}

Emmmanuel FIADUFE · Answer

Hello Amira, Thanks for your response, after removing the relationship there was no foreign key conflict again, but says UserId does not exist meanwhile it is the same username I used to login before trying to save the data into the database

Amira Bedhiafi · Answer

It is either the UserId doesn't exist (we can talk about foreign key constraint violation) or the relationship between tables is causing conflicts. Even though you removed the relationship, the database might still have constraints: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'tblOrder'; Or simply add explicit validation: // Before saving, verify the user exists with the exact ID var user = objRestaurantDBEntities.tblUsers .FirstOrDefault(u => u.UserId == objOrderViewModel.UserId); if (user == null) { throw new Exception($"User with ID {objOrderViewModel.UserId} not found"); }