ItemId is not able to save into the tblQuantities

Hello team.

As I tried to save data, you can see it below

Hello Emmanuel,

Have you checked in step-3 and line,

objQnty.ItemId = objItem.ItemId;

Are you getting objITem.ItemId we are getting correct ItemId after saveChanges on item table. If not then we need to check here why itemId not comming here.

Please check here and you will find root cause for this.

Hello Emmanuel,

Please check on below code, you need to do below changes, When you are using EF then please use transaction, it will help to maintain data integrity.

public ActionResult SaveItem(ItemViewModel itemModel)
{
    bool result = false;

    using (var transaction = objRestaurantDBEntities.Database.BeginTransaction())
    {
        try
        {
            // Step 1: Save Category
            tblCategory objCategory = objRestaurantDBEntities.tblCategories
                .SingleOrDefault(x => x.CategoryId == itemModel.CategoryId) ?? new tblCategory();

            if (itemModel.CategoryId <= 0)
            {
                objCategory.CategoryName = itemModel.CategoryName;
                objRestaurantDBEntities.tblCategories.Add(objCategory);
                objRestaurantDBEntities.SaveChanges(); // Save to generate CategoryId
            }

            // Ensure objCategory has an ID
            if (objCategory.CategoryId <= 0)
            {
                return Json(new { success = false, message = "Failed to save category." });
            }

            // Step 2: Save Item
            tblItem objItem = objRestaurantDBEntities.tblItems
                .SingleOrDefault(x => x.ItemId == itemModel.ItemId) ?? new tblItem();

            objItem.PCode = itemModel.PCode;
            objItem.ItemName = itemModel.ItemName;
            objItem.CostPrice = itemModel.CostPrice;
            objItem.ItemPrice = itemModel.ItemPrice;
            objItem.Vat = itemModel.Vat;
            objItem.ExpiringDate = itemModel.ExpiringDate;
            objItem.Active = itemModel.Active;
            objItem.CategoryId = objCategory.CategoryId; 

            if (itemModel.ItemId <= 0)             {
                objRestaurantDBEntities.tblItems.Add(objItem);
            }

            objRestaurantDBEntities.SaveChanges();
            if (objItem.ItemId <= 0)
            {
                return Json(new { success = false, message = "Failed to save item. ItemId not generated." });
            }

            // Step 3: Save Quantity (Ensure itemModel.tblQuantities is not null)
            if (itemModel.tblQuantities != null)
            {
                tblQuantity objQnty = objRestaurantDBEntities.tblQuantities
                    .SingleOrDefault(x => x.QuantityId == itemModel.tblQuantities.QuantityId) ?? new tblQuantity();

                objQnty.InitialQty = itemModel.tblQuantities.InitialQty;
                objQnty.Quantity = itemModel.tblQuantities.Quantity;
                objQnty.ItemId = objItem.ItemId;

                if (itemModel.tblQuantities.QuantityId <= 0) // New Quantity Entry
                {
                    objRestaurantDBEntities.tblQuantities.Add(objQnty);
                }

                objRestaurantDBEntities.SaveChanges(); // Save Quantity Data
            }

            transaction.Commit();

            result = true;
        }
        catch (Exception ex)
        {
            transaction.Rollback(); // Rollback all changes if any step fails

            return Json(new { success = false, message = $"An error occurred: {ex.Message}" });
        }
    }

    return Json(new { success = result, message = "Item saved successfully." });
}

Hello Mr Pual ,

thank you for your resposnse but I'm getting error from where we are calling the SaveOrUpdate quantity.

Error    2    The best overloaded method match for 'SmartRestaurantManagement.Controllers.HomeController.SaveOrUpdateQuantity(SmartRestaurantManagement.ViewModel.QuantityViewModel, int)' has some invalid arguments    

A refactored version of the method:

public ActionResult SaveItem(ItemViewModel itemModel)
{
    try
    {
        // Step 1: Save or Update Category
        var objCategory = SaveOrUpdateCategory(itemModel);
        if (objCategory.CategoryId <= 0)
        {
            return Json(new { success = false, message = "Failed to save category." });
        }

        // Step 2: Save or Update Item
        var objItem = SaveOrUpdateItem(itemModel, objCategory.CategoryId);
        if (objItem.ItemId <= 0)
        {
            return Json(new { success = false, message = "Failed to save item." });
        }

        // Step 3: Save or Update Quantity
        if (itemModel.tblQuantities != null)
        {
            SaveOrUpdateQuantity(itemModel.tblQuantities, objItem.ItemId);
        }

        return Json(new { success = true, message = "Item saved successfully." });
    }
    catch (Exception ex)
    {
        // Log the error properly
        Console.WriteLine(ex.ToString());
        return Json(new { success = false, message = $"An error occurred: {ex.Message}" });
    }
}

private tblCategory SaveOrUpdateCategory(ItemViewModel itemModel)
{
    var objCategory = objRestaurantDBEntities.tblCategories
        .SingleOrDefault(x => x.CategoryId == itemModel.CategoryId) ?? new tblCategory();

    if (itemModel.CategoryId <= 0)
    {
        objCategory.CategoryName = itemModel.CategoryName;
        objRestaurantDBEntities.tblCategories.Add(objCategory);
        objRestaurantDBEntities.SaveChanges();
    }

    return objCategory;
}

private tblItem SaveOrUpdateItem(ItemViewModel itemModel, int categoryId)
{
    var objItem = objRestaurantDBEntities.tblItems
        .SingleOrDefault(x => x.ItemId == itemModel.ItemId) ?? new tblItem();

    objItem.PCode = itemModel.PCode;
    objItem.ItemName = itemModel.ItemName;
    objItem.CostPrice = itemModel.CostPrice;
    objItem.ItemPrice = itemModel.ItemPrice;
    objItem.Vat = itemModel.Vat;
    objItem.ExpiringDate = itemModel.ExpiringDate;
    objItem.Active = itemModel.Active;
    objItem.CategoryId = categoryId;

    if (itemModel.ItemId <= 0)
    {
        objRestaurantDBEntities.tblItems.Add(objItem);
    }

    objRestaurantDBEntities.SaveChanges();
    return objItem;
}

private void SaveOrUpdateQuantity(tblQuantityViewModel quantityModel, int itemId)
{
    var objQnty = objRestaurantDBEntities.tblQuantities
        .SingleOrDefault(x => x.QuantityId == quantityModel.QuantityId) ?? new tblQuantity();

    objQnty.InitialQty = quantityModel.InitialQty;
    objQnty.Quantity = quantityModel.Quantity;
    objQnty.ItemId = itemId;

    if (quantityModel.QuantityId <= 0)
    {
        objRestaurantDBEntities.tblQuantities.Add(objQnty);
    }

    objRestaurantDBEntities.SaveChanges();
}

Error Handling

• Issue : The error message in the catch block uses string interpolation incorrectly ("An error occurred: {ex.Message}"). This will not replace {ex.Message} with the actual exception message.

• Fix : Use proper string interpolation: 

  return Json(new { success = false, message = $"An error occurred: {ex.Message}" });

  Improvement : Instead of logging errors to the console, use a proper logging framework like Serilog , NLog , or Microsoft.Extensions.Logging .

Null Checks

• Issue : The code assumes itemModel.tblQuantities is null-checked but does not validate other properties of itemModel (e.g., CategoryName, PCode, etc.).
• Fix : Add null checks and validation for all required fields:

if (string.IsNullOrWhiteSpace(itemModel.CategoryName))
{
    return Json(new { success = false, message = "Category name is required." });
}

Database Context Management

• Issue : The code directly uses objRestaurantDBEntities.SaveChanges() multiple times without wrapping it in a transaction. If one step fails after earlier steps succeed, the database could end up in an inconsistent state.
• Fix : Use a transaction to ensure atomicity:

using (var transaction = objRestaurantDBEntities.Database.BeginTransaction())
{
    try
    {
        // Save Category, Item, and Quantity here
        objRestaurantDBEntities.SaveChanges();
        transaction.Commit();
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        return Json(new { success = false, message = $"An error occurred: {ex.Message}" });
    }
}

Performance Optimization

• Issue : The code calls SingleOrDefault multiple times to check for existing records. This can result in unnecessary database queries.
• Fix : Optimize by caching results or combining queries where possible

var existingCategory = objRestaurantDBEntities.tblCategories
    .FirstOrDefault(x => x.CategoryId == itemModel.CategoryId);

var objCategory = existingCategory ?? new tblCategory();

JSON Response

• Issue : The JSON response lacks detailed information about what went wrong in some cases (e.g., "Failed to save category").
• Improvement : Provide more specific error messages or include additional details (e.g., CategoryId or ItemId).

Code Readability

• Issue : The code is lengthy and could be refactored into smaller methods for better readability and maintainability.
• Fix : Break the logic into reusable methods:

private tblCategory SaveOrUpdateCategory(ItemViewModel itemModel)
{
    var objCategory = objRestaurantDBEntities.tblCategories
        .SingleOrDefault(x => x.CategoryId == itemModel.CategoryId) ?? new tblCategory();

    if (itemModel.CategoryId <= 0)
    {
        objCategory.CategoryName = itemModel.CategoryName;
        objRestaurantDBEntities.tblCategories.Add(objCategory);
        objRestaurantDBEntities.SaveChanges();
    }

    return objCategory;
}

Key Steps

1. Category Handling :

   • If CategoryId is less than or equal to 0, it assumes the category is new and creates it.
   • Otherwise, it retrieves the existing category from the database.
   • After saving the category, it ensures the CategoryId is valid before proceeding.

2. Item Handling :

   • Similar to the category logic, it checks if the ItemId is less than or equal to 0 to determine if the item is new.
   • It assigns the CategoryId (from the previously saved category) to the item.
   • The item is either added (if new) or updated (if existing).

3. Quantity Handling :

   • If tblQuantities is provided in the itemModel, it saves or updates the quantity record.
   • It associates the quantity with the saved item using the ItemId.

4. Error Handling :

   • A try-catch block is used to handle exceptions.
   • Errors are logged (via Console.WriteLine) and returned as part of the JSON response.

5. JSON Response :

   • Returns a JSON object with success (boolean) and message (string) properties.

The provided code is a method in an ASP.NET MVC controller that handles saving an item, its associated category, and quantity information to a database.

The SaveItem method performs the following tasks:

• Saves or updates a Category (tblCategory) if it doesn't already exist.
• Saves or updates an Item (tblItem) and associates it with the saved category.
• Saves or updates a Quantity (tblQuantity) for the item.

It returns a JSON response indicating success or failure, along with an appropriate message.

Any help will be highly appreciated 

Data got save in the item table alright but the quantity table remain empty,

Kindly help.

It's still not working 

It seems that the issue you are facing is related to saving the Quantity data into tblQuantities. The problem lies in how the QuantityId is handled within the code snippet you provided. Let's break it down and identify the potential issue:

1. The code snippet attempts to save the Quantity data by checking if itemModel.tblQuantities is not null.

2. It then tries to retrieve a tblQuantity object based on itemModel.tblQuantities.QuantityId.

3. If the retrieved tblQuantity object is null (i.e., not found in the database), a new tblQuantity object is created.

The potential problem arises when trying to save the Quantity data because the QuantityId is used as a unique identifier. However, the QuantityId is not being properly handled when creating a new Quantity entry.

In the code snippet:

tblQuantity objQnty = objRestaurantDBEntities.tblQuantities
    .SingleOrDefault(x => x.QuantityId == itemModel.tblQuantities.QuantityId) ?? new tblQuantity();

The QuantityId comparison `x.QuantityId == itemModel.tblQuantities.QuantityId` assumes that the QuantityId from the itemModel is already assigned or retrieved from the database. If QuantityId is not set or generated correctly in itemModel.tblQuantities, it will not match any existing QuantityId in tblQuantities, causing a new entry to be created every time, even when it should be associating the Quantity with an existing Item.

To address this issue, ensure that the QuantityId is correctly generated or retrieved for the specific Quantity related to the Item. If QuantityId is supposed to be auto-generated by the database, make sure that the correct mechanisms are in place to handle this auto-generation and association with the Item.

Additionally, if QuantityId needs to be assigned or retrieved before saving the Quantity, consider updating the logic to handle QuantityId generation or retrieval before creating a new tblQuantity object.

By addressing how QuantityId is managed and associated with the Item, you should be able to resolve the issue of ItemId not saving into tblQuantities effectively. Let me know if you need further clarification or assistance with this matter!