LINQ queries with many-to-many tables in Entity Data Model

I'm trying to use LINQ to query the following Entity Data Model

based on this db model

I'd like to be able to pull a list of products based on ProductFacets.FacetTypeId.

Normal开发者_运维知识库ly, I'd use joins and this wouldn't be a problem but I don't quite understand how to query many-to-many tables under the Entity DataModel.

This is an example sql query:

select p.Name, pf.FacetTypeId from Products p
inner join ProductFacets pf on p.ProductId = pf.ProductId   
where pf.FacetTypeId in(8, 12)

Presuming EF 4:

var facetIds = new [] { 8, 12 };
var q = from p in Context.Products
        where p.FacetTypes.Any(f => facetIds.Contains(f.FacetTypeId))
        select p;

In EF (assuming the mapping is done correctly), joins are hardly ever used; navigation properties are used instead.

Your original SQL returns a tuple with repeated Name entries. With LINQ, it's often easier to "shape" the queries into non-tuple results.

The following should be the same as the SQL, only instead of returning (Name, FacetTypeId) pairs with repeated Names, it will return a type that has a Name and a sequence of FacetTypeIds:

var facetIds = new [] { 8, 12 };
var result = from p in db.Products
             select new
             {
                p.Name,
                FacetTypeIds = from pf in p.FacetTypes
                               where pf.FacetTypeId == 8 || pf.FacetTypeId == 12
                               select pf.FacetTypeId,
             };