Update a List of entries using a List of ID's in EntityFramework

Question

I'm using the following code to store a list of connectionID's in a List<string>:

List<string> connectionIds =
                    connectedUsers.Where(x => x.UserId == userId).Select(x => x.ConnectionId).ToList();

I need to update the Database to set the connection status to false when it finds the corresponding ID's. So far, I am using the following code:

if (connectionIds.Any())
                {
                    foreach (string connectionId in connectionIds)
                    {
                        Connection curConn = db.Connection.FirstOrDefault(x => x.ConnectionID == connectionId);
                        if (curConn != null)
                            curConn.Connected = false;
                        db.SaveChanges();
                    }
                }

However, this makes a call to the DB for each connection... Is there any simple way to update the connection in an easier process?

It actually makes two calls - one to load the object, one to save the changes. — Maarten, Jan 06 '14 at 07:35

score 3 · Accepted Answer · answered Jan 06 '14 at 07:31

3

You can use the Contains method. This will result in a single query for loading the connection objects. Once you have the result of the query, loop through the results to update the Connected flag, and then save the changes.

List<string> connectionIds = ...;

if (connectionIds.Any()) {
    var data = db.Connection
        .Where(x => connectionIds.Contains(x.ConnectionID))
        .ToList();
    foreach (var item in data) {
        item.Connected = false;
    }
    db.SaveChanges();
}

answered Jan 06 '14 at 07:31

Maarten

21,887
3
47
66

Perfect! Thank you :) – TheGeekZn Jan 06 '14 at 07:47
Please note that the list is 'given' to SQL using parameters, and there is a maximum amount of parameters for a query (I think somewhere around 2000), so if you expect more connection-ids, then this solution will not work. – Maarten Jan 06 '14 at 08:25
Oh wow - thanks for that info! Never knew about these limits at all.. Will check it out IF I ever get that amount (Shouldn't though). – TheGeekZn Jan 06 '14 at 08:45

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

You want to perform batch updates in one SQL statement with EF. EF doesn't support this in a straightforward manner. See here for a possible solution.

edited May 23 '17 at 12:19

Community

1
1

answered Jan 06 '14 at 07:30

zmbq

36,789
13
91
160

Update a List of entries using a List of ID's in EntityFramework

2 Answers2