How to add the current row from a list to a DbSet

Question

I'm trying to iterate in each row from a list, if the current row from the list has no record yet in the database hence add the current row, otherwise continue to next iteration. how can i add the current row in the list to my DbSet.

list.ForEach(x =>
{
    objInsuredList = (from obj in _db.dbLifeData
    where (obj.identifier == x.identifier) && (obj.plan == x.plan) && (obj.policyno == x.policyno)
    select obj).ToList(); //get all existing record
    
    var query = _db.dbLifeData.FirstOrDefault(y => y.identifier == x.identifier 
      && y.policyno == x.policyno && y.plan == x.plan); //check current in list if it exists

    if(query != null)
    {
     query.status = x.status;
     _db.Entry(query).State = EntityState.Modified;
     _db.SaveChanges();

    }
    else //current row has no record yet in the dbLifeData database
    {

    }
});

score 2 · Answer 1 · edited Jun 25 '22 at 07:40

2

In EF Core you have to know your entity then you need this code:

DbSet<Object>().Add(query);
efDataContext.SaveChanges();

edited Jun 25 '22 at 07:40

RBT

24,161
21
159
240

answered Jun 24 '22 at 08:29

user19401132

21
1

i have updated the code. I can overwrite an existing record in my entity but not add a new row in the entity from a current row in the list that has no record yet. if(query != null) { query.status = x.status; _db.Entry(query).State = EntityState.Modified; _db.SaveChanges(); – Veck Jun 24 '22 at 08:34
1

you have to create a new entity for example you have a entity who you want to change some values and add another row in DB, 'var newRow = new entity()' then you can fill the properties and save it as a new record – user19401132 Jun 24 '22 at 08:49
thanks @user19401132 . it think was able to figure out. it's similar to your suggestion :) just added a new entity var list_ = new List(); – Veck Jun 24 '22 at 08:56

score 1 · Accepted Answer · answered Jun 24 '22 at 08:55

I would suggest to improve your code, otherwise you will have big performance issues.

Copy FilterByItems helper extension to your code base

// get existing items in one DB roundtrip
var existing = _db.dbLifeData
    .FilterByItems(list, (obj, x) => (obj.identifier == x.identifier) && (obj.plan == x.plan) && (obj.policyno == x.policyno), true)
    .AsEnumerable()
    .ToDictionary(x => (x.identifier, x.plan, x.policyno));

foreach(var x in list)
{
    var key = (x.identifier, x.plan, x.policyno);
    if (existing.TryGetValue(key, out var found))
    {
        // EF Core smart enough to detect changes in properties
        found.status = status;
    }
    else
    {
        var newRecord = new dbLifeData
        {
            status = x.status,
            identifier = x.identifier, 
            plan = x.plan,
            policyno = x.policyno
        };

        _db.dbLifeData.Add(newRecord);
    }
}

// save everything in one batch
_db.SaveChanges();

Thank you for your suggestion, appreciate @svyatoslav. will try this method :) — Veck, Jun 24 '22 at 09:03
tried the code however for 'FilteredByItems' no accessible extension method — Veck, Jun 28 '22 at 06:47

How to add the current row from a list to a DbSet

2 Answers2