CODEWITHSUNDEEP
linqentity-frameworklinq-to-entities
Sierramike
Sierramike

Reputation: 481

EF6 How to query where children contains all values of a list

Say I have a document table, with doc_id (PK) and doc_name fields, a category table with cat_id (PK) and cat_name fields, and a document_categories table with doc_id (PK, FK) and cat_id (PK, FK) fields, so I can attribute one or many categories to each document.

I have generated a model with EF6 in "Database first" mode, which gives me two entities: document and category, each containing a field which is a collection of children.
document contains a categories field which lists the categories of the document, and vice-versa in the category entity.

Now, I want to query all documents that contain category 1 AND category 2. Let's say the database contains the following documents:

Doc A: Categories 1, 3
Doc B: Categories 1, 2
Doc C: Categories 1
Doc D: Categories 1, 2, 3

My query should return docs B and D.

How can I achieve that with EF6 using Linq?

Searched long on this site and in Google but found nothing for this particular request ... Thanks for your help

Upvotes: 4

Views: 2869

Answers (1)

Taher Rahgooy
Taher Rahgooy

Reputation: 6696

Use this:

var ids = new int[]{1,2};
var docs = context.Documents
           .Where(d=> ids.All(cid=> d.Categories.Any(dc=>dc.cat_id == cid))).ToList();

Or

var ids = new int[]{1,2};
var result = from d in context.Documents
         where ids.All(id => d.Categories.Any(dc=> dc.cat_id == id))
         select s;

Upvotes: 8

Related Questions