Adding to or updating an entity in a foreach loop takes too long time before calling SaveChanges()?

c# entity-framework entity-framework-6

Question

I have this method that saves an entity with its related items (many-to-many relationship),

private static void Save<T>(TbCommonHistoryLog log, List<T> lstDetails) where T : IHasSerial
{   
    foreach (var item in lstDetails.OrderBy(x => x.Serial))
    {
        var ser = SerializeObject(item);
        var record = oContext.TbHistoryLog_Lists.FirstOrDefault(x => x.ListObjectJson == ser);
        if (record == null) //add new list item
        {
            TbCommonHistoryLog_Lists listObject = new TbCommonHistoryLog_Lists()
            {
                ListObjectJson = SerializeObject(item)
            };
            var details = new TbCommonHistoryLogDetails { TbHistoryLog = log, TbHistoryLog_Lists = listObject };
            oContext.TbHistoryLogDetails.Add(details);
        }
        else //attach an existing list item
        {
            var o = oContext.TbHistoryLog_Lists.Find(record.Id);
            oContext.TbHistoryLog_Lists.Attach(o);
            var details = new TbCommonHistoryLogDetails { TbHistoryLog = log, TbHistoryLog_Lists = o };
            oContext.TbHistoryLogDetails.Add(details);
        }
    }
    oContext.BulkSaveChanges();
}

I have two tables: TbCommonHistoryLog, TbCommonHistoryLog_Lists, that are in many to many relationship, the joining table is TbCommonHistoryLogDetails, What I'm doing here is an auditing for master-detail models, all audits are serialized to JSON in DB, I save the head object in the TbCommonHistoryLog table, and every list item in the TbHistoryLog_Lists table, in the mthod above I check if the list item is already exists in the database or not to avoid duplicating. but this process takes more than 15 seconds which is a very long time, I can't figure out what am I doing wrong here.. please help?

1
0
10/15/2017 10:04:02 AM

Accepted Answer

For every single item in collection you're querying database. My suggestion is to save records in var, then ask the variable if the item is in database.

var databaseRecords = oContext.TbHistoryLog_Lists.ToList();

Then in the loop:

var record = databaseRecords.FirstOrDefault(x => x.ListObjectJson == ser);
1
10/15/2017 10:20:48 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow