CODEWITHSUNDEEP
c#.netlinqasp.net-mvc-3
richman64
richman64

Reputation: 93

LINQ to entities comparing collections

I'm having issues with a Linq query that will actually do what I need it to. I've constructed a search that needs to find articles in a database (entity framework) and the search then uses a list of checkboxes to allow the user to only search within certain areas of practice. I've seen posts about linq joins & multiple collections, but my problem is that what I'm trying to select from is the parent table Article and I need to comppare the .Practices to a Practices list I've compiled from the checkbox list.

Before it was a checkbox list, it was a dropdown and this query worked to search for 1 practice:

ar = (from a in db.Articles
      from p in a.Practices
      where p.ID == practiceID
      select a);

Now, I need the where clause to work like an "IN" from regular SQL. My collection of Practices compiled from a checkbox list need to be compared to db.Articles.Practices for my search.

Any suggestions or clarification needed?

Upvotes: 0

Views: 1437

Answers (2)

Gary.S
Gary.S

Reputation: 7131

Assuming: List practiseIds

ar = (from a in db.Articles
  from p in db.Practices
  where practiseIds.Contains(p.ID)
  select a);

However some clarification is needed:

IN is not a first class supported SQL method in EF so the above code will in fact turn into a list of or's (this may have been resolved in the .net 4 release of EF - http://blogs.msdn.com/b/adonet/archive/2009/08/05/improvements-to-the-generated-sql-in-net-4-0-beta1.aspx)

Psuedo Code -

Select * From db.Articles 
inner join db.Practices on <relation>
Where db.Practices.practiceid = @p0 || db.Practices.practiceid = @p1 || etc..

Upvotes: 2

BrokenGlass
BrokenGlass

Reputation: 160942

Just pass in the id collection as array practiceIds and use a Contains() query:

ar = (from a in db.Articles
      from p in a.Practices
      where practiceIds.Contains(p.ID)
      select a);

Upvotes: 2

Related Questions