Add and Update in ASP.NET Core with Entity Framework

Question

I am doing add and update JSON data into the SQL table.

Below Code working for adding and updating a new set of records

List<CatalogProduct> ListKp = new List<CatalogProduct>();
using (var transaction = _context.Database.BeginTransaction())
{
    try
    {   
        int numP = 0;
        var catalogProducts = _context.CatalogProducts.ToList();
        foreach (var kp in ListKp)
        {
            if (!catalogProducts.Any(x => x.Name == kp.Name))
            {
                _context.CatalogProducts.Add(kp);
            }
            else
            {
                //Use AutoMapper automatically do the mapping
                var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>().ForMember(c => c.Id, opt => opt.Ignore()));
                var oldone = catalogProducts.FirstOrDefault(c => c.Name == kp.Name);
                var mapper = config.CreateMapper();
                oldone = mapper.Map<CatalogProduct, CatalogProduct>(kp, oldone);
                _context.CatalogProducts.Update(oldone);
            }
        }
        numP = _context.SaveChanges();                        
        transaction.Commit();
        return Json("No conflicts. " + numP + " product details saved.");
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        return Json("Error occurred." + ex.Message);
        throw new Exception();
    }
}

Sample JSON data

{
    "title": "Brown eggs",
    "type": "dairy",
    "description": "Raw organic brown eggs in a basket",
    "filename": "0.jpg",
    "height": 600,
    "width": 400,
    "price": 28.1,
    "rating": 4
},
{
    "title": "Sweet fresh stawberry",
    "type": "fruit",
    "description": "Sweet fresh stawberry on the wooden table",
    "filename": "1.jpg",
    "height": 450,
    "width": 299,
    "price": 29.45,
    "rating": 4
},

First I will add [type] key object values from the above JSON into the products table [NAME] field. It will add a new set of records.

When I try to update the products table [NAME] field with [title] key object values, again it will add a new set of records.

Need to update products table [NAME] field without adding again.

I don't know how to check already existing records in a table with model list values. Already spent much time on this. I am new to EF Core, please anybody can help me

Complete Code

 [HttpPost]
public IActionResult InsertProductDetails()
{
                using WebClient wc = new WebClient();
                string contentString = wc.DownloadString(baseurl);

                List<Dictionary<string, string>> ListJsonProductContent = new List<Dictionary<string, string>>();
                var token = JToken.Parse(contentString);
                if (token.Type == JTokenType.Array)  // "["
                {
                    ListJsonProductContent = JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(contentString);
                }
                else if (token.Type == JTokenType.Object) // "{"
                {
                    var ObjectResponse = JsonConvert.DeserializeObject<Dictionary<string, object>>(contentString);
                    foreach (var x in ObjectResponse)
                    {
                        string key = x.Key.ToString();
                        string val = x.Value.ToString();
                        foreach (var dicItemML in JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(val))
                        {
                            ListJsonProductContent.Add(dicItemML);
                        }
                    }
                }                

                List <K360MappingMaster> ListMappedDataDb = new List<K360MappingMaster>();
                var VLinqQuery = from KMM in _context.K360MappingMasters
                                 where KMM.ThirdPartyBaseUrlName != null && KMM.ThirdPartyBaseUrlName == baseurl
                                 select KMM;
                ListMappedDataDb = VLinqQuery.ToList();

                foreach (var dicItemML in ListJsonProductContent)
                {                   
                    Dictionary<string, string> updItem = new Dictionary<string, string>();
                    foreach (var itemMl in dicItemML)
                    {                        
                       
                        if (ListMappedDataDb.Select(s => s.ApiCatalog).ToList().Contains(itemMl.Key))
                        {
                            if (updItem.ContainsKey(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault()))
                            {
                                if (ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault() == "Specification")
                                {
                                    updItem[ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault()] += "<p>" + itemMl.Key + " :" + itemMl.Value + "<p>";
                                }
                                else
                                {
                                    updItem[ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault()] += " " + itemMl.Value;
                                }
                            }
                            else
                            {
                                if (ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault() == "Specification")
                                {
                                    updItem.Add(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault(), "<p>" + itemMl.Key + " :" + itemMl.Value + "<p>");
                                }
                                else
                                {
                                    updItem.Add(ListMappedDataDb.Where(s => s.ApiCatalog == itemMl.Key).Select(s => s.K360Catalog).FirstOrDefault(), itemMl.Value);
                                }
                            }
                        }
                        dicItemML.Remove(itemMl.Key);
                    }
                    foreach (var itemM2 in updItem)
                    {
                        dicItemML.Add(itemM2.Key, itemM2.Value);
                    }
                }

                List<CatalogProduct> ListKp = new List<CatalogProduct>();
                foreach (var dicItem in ListJsonProductContent)
                {                   
                    CatalogProduct Ctgkp = new CatalogProduct
                    {
                        Name = dicItem.ContainsKey("Name") ? dicItem["Name"] : "No Product",
                        Slug = dicItem.ContainsKey("Name") ? string.Concat(dicItem["Name"].Where(c => !char.IsWhiteSpace(c))).ToLower() : "No Slug",
                        Price = dicItem.ContainsKey("Price") ? decimal.Parse(dicItem["Price"], CultureInfo.InvariantCulture) : default,
                        ShortDescription = dicItem.ContainsKey("ShortDescription") ? dicItem["ShortDescription"] : null,
                        Description = dicItem.ContainsKey("Description") ? dicItem["Description"] : null,
                        Specification = dicItem.ContainsKey("Specification") ? dicItem["Specification"] : null,
                        RatingAverage = dicItem.ContainsKey("RatingAverage") ? double.Parse(dicItem["RatingAverage"], CultureInfo.InvariantCulture) : null};
                ListKp.Add(Ctgkp);
               }
using (var transaction = _context.Database.BeginTransaction())
                {
                    try
                    {
                        int numP = 0;
                        var catalogProducts = _context.CatalogProducts.ToList();
                        foreach (var kp in ListKp)
                        {
                            if (!catalogProducts.Any(x => x.Name == kp.Name))
                            {
                                _context.CatalogProducts.Add(kp);
                            }
                            else
                            {
                                //Use AutoMapper automatically do the mapping
                                var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>().ForMember(c => c.Id, opt => opt.Ignore()));
                                var oldone = catalogProducts.FirstOrDefault(c => c.Name == kp.Name);
                                var mapper = config.CreateMapper();
                                oldone = mapper.Map<CatalogProduct, CatalogProduct>(kp, oldone);
                                _context.CatalogProducts.Update(oldone);
                            }
                        }
                        numP = _context.SaveChanges();
                        (from q in _context.K360MappingMasters
                         where q.ThirdPartyBaseUrlName == baseurl
                         select q).ToList().ForEach(x => x.InsertStatusFlag = true);
                        _context.SaveChanges();
                        transaction.Commit();
                        return Json("No conflicts. " + numP + " product details saved.");
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return Json("Error occurred." + ex.Message);
                        throw new Exception();
                    }
                }

Answer 1

For the core code:

var catalogProducts = _context.CatalogProducts.ToList();
foreach (var kp in ListKp)
{
    if (!catalogProducts.Any(x => x.Name == kp.Name))
        _context.CatalogProducts.Add(kp);
    else
    {
        //Use AutoMapper automatically do the mapping
        var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>().ForMember(c => c.Id, opt => opt.Ignore()));
        var oldone = catalogProducts.FirstOrDefault(c => c.Name == kp.Name);
        var mapper = config.CreateMapper();
        oldone = mapper.Map<CatalogProduct, CatalogProduct>(kp, oldone);
        _context.CatalogProducts.Update(oldone);
    }
}

The first detail will be whether your DbContext was set up to disable change tracking proxies. For instance if the following code is in your DbContext constructor or when the DbContext is created:

Configuration.AutoDetectChangesEnabled = false;
Configuration.ProxyCreationEnabled = false;

Many teams turn off proxies when they encounter performance problems tripping over lazy loading. (instead of fixing the data access approach to avoid the lazy loading) Disabling proxies and change tracking makes update scenarios uglier. If these are off by default then I would recommend enabling them temporarily for this operation:

_context.Configuration.AutoDetectChangesEnabled = true;
_context.Configuration.ProxyCreationEnabled = true;

var catalogProducts = _context.CatalogProducts.ToList(); is going to fetch all catalog products from your DB. Depending on your data size this may not be practical. I'd suggest reading these one by one or consolidating the product names from the XML into a list and fetch the desired ones using Contains. Fetching each item by name /w change tracking enabled can be simplified to:

// Avoid re-initializing automapper EVERY loop iteration.
var config = new MapperConfiguration(cfg => cfg.CreateMap<CatalogProduct, CatalogProduct>()
            .ForMember(c => c.Id, opt => opt.Ignore()));
var mapper = config.CreateMapper();

foreach (var kp in ListKp)
{
    var existingCatalogProduct = _context.CatalogProducts.SingleOrDefault(x => x.Name == kp.Name);
    if (existingCatalogProduct == null)
        _context.CatalogProducts.Add(kp);
    else
        mapper.Map<CatalogProduct, CatalogProduct>(kp, existingCatalogProduct);
}

// ...
_context.SaveChanges();

If the Product entity has any navigation properties I would recommend setting those to Ignore as well.

We don't need to call Update when using change tracking. EF will automatically compose UPDATE statements for changed values on records if those values actually change as a result of the map copying over new values. With Mapper.Map<src,dest> I'm not sure what the return value references, if it passes back "dest" or a new object reference. You can ignore the return value, the method will update the values in the "dest" object reference. If the return value is a different object reference than "dest" then that could explain why it is treating the record as a new row as it would be a separate reference.

When you are expecting a single record, use the Single / SingleOrDefault rather than the First variant methods. First should only be used if you expect possible multiples and should always be used with an OrderBy clause.

Update:

If Name is not enough to uniquely identify a record to update vs. insert then you can change the filter to provide enough detail to try and identify the row before determining if an insert is required:

var existingCatalogProduct = _context.CatalogProducts
    .SingleOrDefault(x => x.Name == kp.Name 
        && x.ProductType = kp.ProductType
        && x.Category = kp.Category /*etc*/ );

This way if you find an existing catalog product row you can update the remaining details based with the Map call, or insert a new entry if no row is returned.