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

given property foo of entity bar entity set contains large number of values, how can check absence (from bar) of any value in arbitrary list of values, using linq-to-entities?

for example:

• bar contains alpha , beta.
• we have list of 3 items: [alpha, beta, gamma] want send ef backend (dbms). expect backend reply single scalar result (1 or 0 / true or false). in case, return 0, since @ least 1 value absent (gamma).
• if all values list in entity set, query return 1. example, when list is: [alpha, beta] or [alpha] or [beta].
• we don't want transfer whole entity set ef client.
• we don't want issue multiple queries each value in list.

---

example dynamic t-sql:

select case when not exists (     select * (         select 'alpha' foo         union select 'beta' foo         union select 'gamma' foo)     left join (select distinct foo bar) b     on a.foo = b.foo     b.foo null)     1     else 0 end; 

how formulate linq-to-entity query generate sql query (or equivalent)?

note: wrote sql query in natural way think of (it literally reads "check there no value in list there no corresponding value in bar table"). it's there's better way this.

you looking all method

this give correct answer, result in query each item in list.

var list = new[] { "alpha", "beta", "gamma" };  bool result = list.all(f => ctx.bars.any(b => b.foo == f)); 

---

however, if write subselect, generate single query.

bool result =      ctx.bars.select(x =>                 list.all(f => ctx.bars.any(b => b.foo == f)) //same above encapsulated               ).firstordefault(); 

notice ctx.bars.select(x => never used , it's purpose encapsulate all logic.

---

another option use selectmany on context , selecting list. query sytnax looks this:

bool result =      (from b in ctx.bars      l in list      select l).all(f => ctx.bars.any(b => b.foo == f)); 

so solution above, using from b in ctx.bars force query iqueryable expression rather ienumerable. generate 1 query, rather n queries