CODEWITHSUNDEEP
c#sql-server
moztemur
moztemur

Reputation: 981

Update data table according to C# list efficiently

I have a database table with an Entity named 'Item'. Also, there is a list of Item in my program. I want to update the table according to this list. That is, I want to disable items that do not exist in the list from the database table, and add remaining ones to the database. I have tried some methods but they have seemed very inefficient. Here some code that I tried:

DbContext db = new DbContext();

List<Item> newItems = ... // contains lots of items

foreach (Item item in db.ItemSet)
{
    if (!newItems.Any(x => x.Id== item.Id))
    {
        item.Enable = false;
    }
}

db.SaveChanges();

The code above is inefficient and causes SQL Server memory usage to expand too much.

Upvotes: 1

Views: 2265

Answers (2)

Jim Crandall
Jim Crandall

Reputation: 84

I think

List<Item> updateEItems = db.ItemSet.Any(x => newItems.Any(y => x.Id == y.Id))

is the entity framework equivalent of SELECT WHERE Id IN ()

Upvotes: 0

David L
David L

Reputation: 33833

You can perform the query on the db to retrieve only records that do NOT already contain any of the new item IDs and flag only those records.

DbContext db = new DbContext();

List<Item> newItems = ... // contains lots of items

foreach (Item item in db.ItemSet.Where(x => !newItems.Contains(x.Id))
{
    item.Enable = false;
}

db.SaveChanges();

It is still technically inefficient since it will not batch update, resulting in an Update n+1 scenario. However, it IS more efficient than returning every single record to begin with and iterating over those records in memory in your app.

If you truly wanted something efficient, you could always use something like Dapper to update the records that do not contain your Ids in a single call.

using Dapper;
using (var c = new SqlConnection(connectionString))
{
    c.Open();
    c.Execute(string.Format("UPDATE ItemSet SET Enable = 0 WHERE Id NOT IN ({0})",
                        BuildIds(newItems.Select(x => x.Id).ToList()));
}    

public static class SqlHelpers
{
    internal static string BuildIds<T>(IReadOnlyList<T> ids)
    {
        if (ids.Count <= 0)
            return "''";

        var agsb = new StringBuilder(string.Format("'{0}'", ids[0]));
        for (var i = 1; i < ids.Count; i++)
        {
            agsb.AppendFormat(", '{0}'", ids[i]);
        }

        return agsb.ToString();
    }
}

Likewise, you could also write a stored procedure to handle this for you and invoke it directly.

Upvotes: 2

Related Questions