linq to sql "All" operator type query

String[] codes = new[] {"C1", "C2" }

Below is the schema for SomeEn开发者_Python百科tity

fkey  code
--------------
f1    C1
f1    C2
f2    C2
f2    C3
f2    C4
f3    C5

I have to write queries that can get all the entities based on 2 condition -

1) does the entity have any of the codes 2) does the entity have all the codes

Below is what i wrote for 1st condition
-----------------------------------------
from f in dc.GetTable<SomeEntity>
where codes.Contains(f.code)
select f;

I tried to write 2nd codition by using All operator but "All" operator is not valid for linq to sql.

Question: How to write a query that will return only those entities that have all the codes ?

var codeCount = dc.GetTable<SomeEntity>.Distinct(e => e.Code);
var matching = (from f in dc.GetTable<SomeEntity>
   group f by f.Key into grouped
   select new { f.Key, values = grouped}).Where(v => v.values.Count() == codeCount);

Probably a hideous query, a stored proc mapped to a function on the data context might be a better solution to be honest

You could probably do the codeCount as let binding in the query and carry on the linq statement rather than close out to the .Where call. Not sure if it'd make it more performant but would save an extra trip to the DB

Try this.

• Group by fkey (which will create distinct fkeys)
• Filter the groups where
  • All members of the list of codes are contained within the codes of that group

See below

var matching = dc.GetTable<SomeEntity>()
    .GroupBy(e => e.fkey)
    .Where(group => codes.All(c => 
        group.Select(g => g.code).Contains(code)))
    .Select(group => group.Key);