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.

Why would you do that in SQL? It seems like the difficulties in implementing it in SQL (EF specifically) could easily be replaced by the caller. — Erik Philips, Sep 05 '19 at 20:39
`IIF` is basically a syntactic sugar for `CASE` expression... https://stackoverflow.com/questions/46560686/how-can-i-achieve-sql-case-statement-from-linq — Salman A, Sep 05 '19 at 20:39
Why not just use the C# ternary operator `?:` ? (`from c in Customer select new { c.Id, c.First, Last = (new[] { 2 }.Contains(c.LocationId) ? c.Last : "") }`) — NetMage, Sep 05 '19 at 21:39

score 1 · Accepted Answer · answered Sep 05 '19 at 21:52

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 : "" });

answered Sep 05 '19 at 21:52

Xipooo

Sweet!, Thank you. I feel ignorant for missing that. Also thanks to @NetMage – ttugates Sep 05 '19 at 21:57

1 Answers1