How to build CASE statement when using ExecuteUpdate

[AnthonyFontaine-ES]

Hey!

I'm trying to use the ExecuteUpdate method so I can update some values, but instead of using constants or another columns value, I want to build a CASE... WHEN... THEN type of SQL Statement (see example below - using SQL Server AKA T-SQL).

UPDATE [A]
  SET [StatusID] = CASE WHEN [IsAdmin] = 1 THEN 1 WHEN [IsModerator] = 1 THEN 2 ELSE 0 END
FROM [dbo].[Permission] [A]
WHERE [A].[Enabled] = 1

dbContext.Permissions.Where(p => p.Enabled == true).ExecuteUpdate(...)

Not sure how to build that case statement within the ExecuteUpdate method call.

Any idea?

In my case, I'm using EF Core 7, but if an upgrade is required, it shouldn't be a blocker.

Edit: This case is a simplified one, but it's possible I have a dynamic number of condition to handle updates.

[roji]

Have you tried using the conditional operator, i.e. something like the following:

await ctx.Permissions.ExecuteUpdateAsync(setters => setters.
    SetProperty(p => p.StatusId, p => p.IsAdmin == 1 ? 1 : (IsModerator == 1 ? 2 : 0));

[AnthonyFontaine-ES]

I'll try it, but like mentioned in the Edit (you were too fast to answer 🙂 ), another case I have that is upcoming for us would require to build that expression dynamically.

[roji]

@AnthonyFontaine-ES building the expression dynamically is a completely different question. You can absolutely do that today, but it's a bit difficult, since you have to deal with the Expression builder APIs a lot. #32018 is about making that easier - but shouldn't make something possible that is currently impossible.

[AnthonyFontaine-ES]

@roji , is there documentation about how to use the Expression builder APIs?

[roji]

@AnthonyFontaine-ES the API docs are here, and there are many tutorials/resources on the Internet, such as this one.

I'll go ahead and close this as there doesn't seem to be anything specific needed from EF here - it's already possible to both express CASE/WHEN SQL with ExecuteUpdate, and also to build dynamic expression trees.