How to write Inline If Statement(SQL IIF) in EFCore Select?

Question

I have the following Customer table:

Id  First    Last   LocationId
0   John     Doe    2
1   Mary     Smith  4

My use case requires column level permissions(predicated on a value in the Entity's table).

How can I query like the following thru EFCore?

SELECT Id, First, IIF(LocationId in(2), Last, '') FROM Customer;

Whereby Last is returned only when LocationId == 2.

• Can this be accomplished in Linq-to-Entities as a dynamic type?
• If not, can I use FromSql() and QueryTypes?
• I found this SO How to create “inline if statement” with expressions in dynamic select for null checking. But I am not familiar with Expression type. This implies its possible however.

Answer 1

I believe you're looking to use the .Select() method and ternary operator. So something like this:

context.Customer.Select(c => new { c.Id, c.First, Last = c.LocationId == 2 ? c.Last : "" });