How to run EF Core ExecuteUpdate using multiple Ids using different conditions for each Id

Question

I have this ExecuteUpdate call below inside a for loop and I want to know if it's possible to eliminate the For loop and run ExecuteUpdate as one call?

foreach (var registrant in yogabandEvent.Registrants) 
{
    result = await _dbContext.Users
        .Where(p => p.Id == registrant.UserId)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => p.Attended, registrant.RegistrantType == RegistrantType.Student ? p => p.Attended + 1 : p => p.Attended)
        .SetProperty(p => p.Hosted, (registrant.RegistrantType == RegistrantType.Host || registrant.RegistrantType == RegistrantType.HostInstructor) ? p => p.Hosted + 1 : p => p.Hosted)
        .SetProperty(p => p.Instructed, registrant.RegistrantType == RegistrantType.Instructor ? p => p.Instructed + 1 : p => p.Instructed)
    );
}

Gert Arnold · Accepted Answer · 2024-07-31 18:55:52Z

0

ExecuteUpdate generates one update statement that is send to the database. Therefore, it's impossible to use complex local data, like yogabandEvent.Registrants, that are supposed to be read while the statement runs, and as such there's no way to eliminate the loop.

However, in your case the update can easily be restructured to create one update statement that does the job. Since you only update Users of which yogabandEvent.Registrants have type Student, you can first collect these Ids and use Contains in the update statement:

var studentIds = yogabandEvent.Registrants
    .Where(r => r.RegistrantType == RegistrantType.Student)
    .Select(r => r.UserId)
    .ToList();

result = await _dbContext.Users
               .Where(p => studentIds.Contains(p.Id))
               .ExecuteUpdateAsync(setters => setters
                   .SetProperty(p => p.Attended, p => p.Attended + 1));

edited Jul 31, 2024 at 18:55

answered Jul 31, 2024 at 18:27

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

chuckd Over a year ago

Hi thanks for the help. I actually have something a little more complex, but I left part of it out thinking it would be easier to get help. I'll edit my post above.

Gert Arnold Over a year ago

Then do it in three parts, for each SetProperty call.

chuckd Over a year ago

How are the conditions checked?

Gert Arnold Over a year ago

I mean, collect the Ids three times for each of the registrant.RegistrantType == ... predicates and for each of these listst, do the above with the matching SetProperty call.

chuckd Over a year ago

you mean run 3 different execute updates? Can this be done with 1 execute update?

|

chuckd · Accepted Answer · 2024-08-01 16:34:56Z

I followed a users post who has since deleted it, but provided me with what I was looking for, so I will post my solution to my own question. You can see below that now I can provide a single call with no for loop that has conditions within each SetProperty that can be applied to a list of ID's, there by eliminating any for loop or executing multiple ExecuteUpdate calls.

 var studentIds = yogabandEvent.Registrants
    .Where(x => x.RegistrantType == RegistrantType.Student)
 .Select(x => x.UserId);

 var hostId = 1;          
 var instructorId = 2

 result = await _dbContext.Users
    .Where(p => yogaClass.Registrants.Select(x => x.UserId).Contains(p.Id))
 .ExecuteUpdateAsync(setters => setters
    .SetProperty(p => p.Attended, p => p.Attended + (studentIds.Contains(p.Id) ? 1 : 0))
    .SetProperty(p => p.Hosted, p => p.Hosted + (p.Id == hostId ? 1 : 0))
    .SetProperty(p => p.Instructed, p => p.Instructed + (p.Id == instructorId ? 1 : 0))
);

Collectives™ on Stack Overflow

How to run EF Core ExecuteUpdate using multiple Ids using different conditions for each Id

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related