Force Entity Framework to update several records in one round trip.

Question

I have to update multiple records in EF and I've come up with the following two methods:

Method 1: ExecuteSqlCommand (direct SQL)

customDB.Database.ExecuteSqlCommand(
@"UPDATE  dbo.UserInfo
SET     dbo.UserInfo.Email = dbo.IAMUser.Email,
        dbo.UserInfo.Mobile = dbo.IAMUser.Phone
FROM    dbo.UserInfo
        INNER JOIN dbo.IAMUserMapping ON dbo.UserInfo.UserID = dbo.IAMUserMapping.fUserId
        INNER JOIN dbo.IAMUser ON IAMUser.IAMID = IAMUserMapping.fIAMID
WHERE   dbo.IAMUser.IAMID = @iamid ", new SqlParameter("@iamid", SqlDbType.UniqueIdentifier) { Value = IAMID });

Method 2: Linq foreach:

var ui = from userInfo in customDB.UserInfo
     join userMapping in customDB.IAMUserMapping 
         on userInfo.UserID equals userMapping.fUserId
     join iamUser in customDB.IAMUser 
         on userMapping.IAMUser.IAMID equals iamUser.IAMID
     where iamUser.IAMID == IAMID
     select new
     {
         userInfo.UserID,
         iamUser.Email,
         iamUser.Phone
     };

foreach (var x1 in ui)
{
    var u = new UserInfo
    {
        UserID = x1.UserID,
        Email = x1.Email,
        Mobile = x1.Phone
    };
    customDB.UserInfo.Attach(u);
    var entry = customDB.Entry(u);
    entry.Property(e => e.Email).IsModified = true;
    entry.Property(e => e.Mobile).IsModified = true;
}
customDB.SaveChanges();

Method #1 is the most efficient, resulting in a single SQL query.

Method #2 is as efficient on the SQL Server side of things, but it generates a lot more round trips to the server. 1 select to get the records, then 1 update for each record that is updated.

Method #2 will give a compile time error if anything in the DB is changed, while #1 will give a run time error.

What do people consider the best practice in cases like these?

Is there any way to get the best of both worlds?

There's a well known bug in SO's markup that causes issues with code formatting within numbered lists. I just rejected an edit that made it even worse, so I can't fix now. Just [edit] and get rid of the numbered list and format your code properly as code. — , Jul 07 '16 at 14:10
I saw it on post, fixed it by writing method in front. Ty for the edit. — devzero, Jul 07 '16 at 14:11
*the best practice* makes this question off-topic at Stack Overflow (opinion-based, and hard to answer because it's up to you to weigh the trade-offs of both methods). *any other way to optimize this query?* Is a better question to ask here, but even then one may argue that CodeReview is a better fit. — Gert Arnold, Jul 07 '16 at 14:18
The question is more about if these two methods are the best way to do it, or if there is a 3rd way that is strictly better. — devzero, Jul 07 '16 at 14:27
In situations like this your 1st approach is the best especially when it concerns a large number of records. An alternate approach is to use a Stored Proc which pretty much results again is something very similar to your first example. — Igor, Jul 07 '16 at 14:27
Don't you have navigation properties connecting userInfo, userMapping and IAMUserMapping? If so remove your joins. e.g. UserInfo.IAMUserMapping.IAMUser ? — Paul Zahra, Jul 07 '16 at 14:48
http://stackoverflow.com/questions/21592596/update-multiple-rows-in-entity-framework-from-a-list-of-ids — Paul Zahra, Jul 07 '16 at 14:52
Paul: IAMUserMapping and IAMUser dos have navigational mappings, userInfo does not map to either of them. I've written it out so it's easier to compare to the SQL join. — devzero, Jul 07 '16 at 21:37

score 0 · Answer 1 · answered Jul 07 '16 at 14:27

0

With first approach problems that you may faced will be much more critical and harder to solve, e.g. when renaming/restructuring of entities. Compile time errors are much better, easy to solve then runtime ones.

Also, I am not sure how ExecuteSqlCommand method works, but looks like this code is vulnerable to SQL Injection. So, I will definitelly choose linq approach.

answered Jul 07 '16 at 14:27

J.King

78
5

ExecuteSqlCommand is safe as long as you use parameters. The generated SQL if you trace using profiler is equal. – devzero Jul 07 '16 at 14:28
I disagree, on point 1 but this is more a matter of opinion. On point 2 it is parameterized so there is no risk of sql injection. In the OPs example the code even passes in a `SqlParameter` instance to illustrate it. – Igor Jul 07 '16 at 14:28
it depends of what you need, as some people answered you can do a store procedure, Now if you want to consume a large data, this is simple you can put this into a hosted background and run in the night there are less people doing transactions. but for sure you can parameters if people know how to do with native SqlConnection, SqlCommand, etc. – AbbathCL Oct 02 '20 at 22:26

Force Entity Framework to update several records in one round trip.

1 Answers1