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

Question

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.

Normally, 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)

Answer 1

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;

Answer 2

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,
             };