EF (LINQ to SQL) - How to query?

Question

I have the concept of a document that has keyword/s. EF abstracted out the document-keyword joining table to an association.

The structure looks like this

Document: ID (PK)

Document_Keyword: DocumentID (PK), Keyword (PK) 

Keyword: Keyword (PK)

I have the requirement to return a list of documents where they contain ALL keywords in a string[]

If I was doing this in SQL it would be similar to below

with t as (
    select 'keyword1' KEYWORD  union
    select 'keyword2'    
)
select DocumentID,count(*) from  [dbo].[Document_Keyword] p
inner join t on p.KEYWORD = t.KEYWORD
group by DocumentID
having count(*) = (select count(*) from t)

Im struggling to form a linq query that will give me the same result.

I have tried the following LINQ statement however it does returns documents that contain 1 or more of the keywords in the array. I require that documents are only returned if ALL keywords match.

var query = (from k in db.KEYWORD
                                     from b in k.DOCUMENT                                        
                                     join q in arrKeywords //array of string[]
                                        on k.KEYWORD equals q                              
                                     select new Document()
                                       {
                                           Filename = b.FILENAME,
                                           Description = b.TITLE                                               
                                       });

Any ideas? Cheers Jeremy

Your list of keywords is comparable to `roleIds` in the answer to the duplicate question. — Gert Arnold, Jan 07 '15 at 08:55
It's not the same as the duplicate question returns a list of employees with 1 or more roles. I require documents to be returned only if all keywords exist — gumby, Jan 07 '15 at 09:17
var query = db.DOCUMENT .Where(x => x.KEYWORD.All(r => arrKeywords.Contains(r.KEYWORD))); This does not work because it still returns documents that partial match the array. — gumby, Jan 08 '15 at 02:06
Reversing the statement does not seem to help either. var query = db.KEYWORD .Where(r => arrKeywords.Contains(r.KEYWORD)) .SelectMany(x => x.DOCUMENT); — gumby, Jan 08 '15 at 02:07
Your requirements are not clear. It seems to me now that you're looking for documents of which the key words *exactly* match the given key words (i.e. *all* given keywords and not more). Maybe you can specify this in your question and I'll reopen. — Gert Arnold, Jan 09 '15 at 08:14

score 0 · Answer 1 · answered Jan 10 '15 at 11:23

If I get you well you want entries of which all keywords match exactly, i.e. it doesn't have any other keywords. A way too achieve this is

var kwc = arrKeywords.Count();

var query = from k in db.KEYWORD
            let kw = k.DOCUMENT.Select(d => d.KEYWORD)
            where kw.All(kw1 => arrKeywords.Contains(kw1))
               && kw.Count() == kwc;

The generated query is still much longer than a hand-coded one would be, but I think the database's query optimizer should be able to handle this.

EF (LINQ to SQL) - How to query?

1 Answers1