2

Can anyone help me with the following? I'm using Linq to entities (code first).

I have a Products object which is mapped (1 to many) in my db context to my ProductAreas Object.

This query works and brings me back a list of enabled products including their associated areas:

IQueryable<Product> products = from Product in db.Product.Include(p=>p.ProductAreas)
                           where (Product.BrandId == brandid && Product.Enabled == true)
                           select Product;

I want to add in a filter so that I'm also only getting back ProductAreas which are enabled.

This is what I initially tried:

  IQueryable<Product> products = from Product in db.Product.Include(p=>p.ProductAreas.Where(pa=>pa.Enabled == true))
      where (Product.BrandId == brandid && Product.Enabled == true)

but when I run my code I get the following error: "The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path"

I presume I want to add the filter in my where somehow? I tried this but it only brings me back products which have areas (and still bring backs ProductAreas which are not enabled)

IQueryable<Product> products = from Product in db.Product.Include(p=>p.ProductAreas)
                           where (Product.BrandId == brandid && Product.Enabled == true && Product.ProductAreas.FirstOrDefault(a=>a.Enabled == true) != null)
                           select Product;

Any help very much appreaciated (as you can probably tell I'm just learning Linq to Entities!!)

carok
  • 648
  • 6
  • 14

2 Answers2

0

You should be able to use a projection in this case:

var results = from product in db.Product
              where (product.BrandId == brandid && product.Enabled)
              select new 
              { 
                  Product = product, 
                  ProductAreas = product.ProductAreas.Where(pa=>pa.Enabled)
              };
IQueryable<Product> products = results.Select( x => x.Product);
BrokenGlass
  • 158,293
  • 28
  • 286
  • 335
  • Hi, thanks so much for your answer, I have tried both yours and Wouter de Kort's, obviously they are very similar and I'm getting the same results for both. I'm still getting back all ProductAreas even those where enabled is false...just wondering if there is anything else I'm missing? I'm presuming my mappings in my context class are correct as I'm getting the correct product/area combinations back... Also I had to add the virtual property to my ProductAreas definition in my Products class to get back any areas at all, if possible I would like to only load these when I need them? – carok Nov 18 '11 at 10:53
0

Filtering on an include relation is something which you can do in the following way:

var products = (from p in db.Product
               where (p.BrandId == brandid && p.Enabled)
select new
{
  Product = p,
  ProductAreas = p.ProductAreas.Where(pa=>pa.Enabled)
}).Select(p => p.Product);

Because of the automatic relation fixup in EF this works. It's a known 'side effect'.

Wouter de Kort
  • 39,090
  • 12
  • 84
  • 103
  • Hi, thanks so much for your answer, I have tried both yours and BrokenGlass's, obviously they are very similar and I'm getting the same results for both. I'm still getting back all ProductAreas even those where enabled is false...just wondering if there is anything else I'm missing? I'm presuming my mappings in my context class are correct as I'm getting the correct product/area combinations back... Also I had to add the virtual property to my ProductAreas definition in my Products class to get back any areaa at all, if possible I would like to only load these when I need them? – carok Nov 18 '11 at 10:57