How to check for the absence of any value in a list from a set server-side in LINQ? (SQL given)

Question

Given a property foo of an entity bar for which the entity set contains a large number of values, how can we check for the absence (from bar) of any value in an arbitrary list of values, using LINQ-to-Entities?

For example:

• bar contains ALPHA and BETA.
• We have a list of three items: [ALPHA, BETA, GAMMA] which we want to send to the EF backend (DBMS). We expect the backend to reply with a single scalar result (1 or 0 / true or false). In this case, it would return 0, since at least one value is absent (GAMMA).
• If all values from the list are in the entity set, the query would return 1. For example, when the list is: [ALPHA, BETA] or simply [ALPHA] or [BETA].
• We definitely don't want to transfer the whole entity set to the EF client.
• We definitely don't want to issue multiple queries for each value in the list.

---

Example dynamic T-SQL:

select case when not exists (
    select * from (
        select 'ALPHA' foo
        union select 'BETA' foo
        union select 'GAMMA' foo) a
    left join (select distinct foo from bar) b
    on a.foo = b.foo
    where b.foo is null)
    then 1
    else 0
end;

How could we formulate a LINQ-to-Entity query that would generate this SQL query (or equivalent)?

Note: I wrote this SQL query in the most natural way I could think of (it literally reads like "check that there is no value in the list for which there is no corresponding value in the bar table"). It's very likely there's a better way to do this.

Answer 1

You are looking for the All method

This will give you the correct answer, but it will result in a query for each item in list.

var list = new[] { "ALPHA", "BETA", "GAMMA" };

bool result = list.All(f => ctx.Bars.Any(b => b.Foo == f));

---

However, if you write it as a subselect, it will generate a single query.

bool result = 
    ctx.Bars.Select(x => 

              list.All(f => ctx.Bars.Any(b => b.Foo == f)) //Same as above but encapsulated 

            ).FirstOrDefault();

Notice that ctx.Bars.Select(x => is never actually used and it's only purpose is to encapsulate your All logic.

---

Another option is to use SelectMany on the context and then selecting only the list. The query sytnax looks like this:

bool result = 
    (from b in ctx.Bars
     from l in list
     select l).All(f => ctx.Bars.Any(b => b.Foo == f));

So like the solution above, we are only using from b in ctx.Bars to force the query to be an IQueryable expression rather than an IEnumerable. Which will generate 1 query, rather than N queries

Answer 2

Something like this?

bool result = !context.Bars.Any(b => list.Contains(b.Foo));

EDIT: The only option I can see is to use a raw SQL query. This will limit which back-end DBMS you can use, since different database systems support different syntax.

You'll also need to hard-code the table and column names - they won't get picked up if you refactor your code.

Assuming Microsoft SQL Server, something like this should work:

var list = new List<string> { "A", "B", "C" };

// Generate a parameter placeholder for each item in the list:
var listParameters = list.Select((item, index) => "@p" + index);

// Generate a "UNION" query for the parameters:
string listValues = string.Join(" UNION SELECT ", listParameters);

// The final query to execute:
string query = @"SELECT CASE WHEN NOT EXISTS
(
    SELECT 1
    FROM (SELECT " + listValues + @") As L (foo)
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM bar As b
        WHERE b.foo = L.foo
    )
) THEN CAST(1 As bit) ELSE CAST(0 As bit) END";

bool result = context.Database.SqlQuery<bool>(query, list).First();

Answer 3

I'm borrowing an answer from here but maybe you could do something like:

var itemList = new List<string> { ... };

// select just foo
var result = context.Bars.Select(b => b.Foo)
   // get the distinct values of foo
   .Distinct()
   // count how many values of foo are in itemList and check if the counts are equal
   // because the query is distinct, the count would match iff all items were matched
   .Count(f => itemList.Contains(f)) == itemList.Count;

The question for the answer I linked does also include other options that could be adapted for your case, like using Except with Any.