Lambda expression filtering included related data with Entity Framework

Question

I need to filter only the visibles products from a category, but it's not working.

Category category = db.Categories
            .Include(c => c.Products.Where(p => p.IsVisible))
            .First(c => c.CategoryID == id);

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.

UPDATE

 var result = (from c in db.Categories
                             where c.CategoryID == id
                             select new
                             {
                                 CategoryID = c.CategoryID,
                                 Description = c.Description,
                                 Products = (from p in db.Products
                                             where p.IsVisible
                                             && p.CategoryID == c.CategoryID
                                             orderby p.DateSent descending
                                             select p)
                             }).FirstOrDefault();

but now i need to cast the anonymousType to Category

stackoverflow.com/questions/10035261/…

rubens.lopes
– rubens.lopes

2012-07-04 19:55:06 +00:00
Commented Jul 4, 2012 at 19:55 — rubens.lopes
– rubens.lopes, Commented Jul 4, 2012 at 19:55

test · Accepted Answer · 2012-07-04 19:34:42Z

1

Your query doesn't make sense if you want:

the visibles products from a category

If you genuinely want the visible products, try this:

var visibleProducts = db.Categories
                        .Where(c => c.CategoryID == id)
                        .Select(c => c.Products.Where(p => p.IsVisible));

Note: untested

answered Jul 4, 2012 at 19:34

test

2,6492 gold badges28 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rubens.lopes Over a year ago

i'll test, but i need the category data plus a list of visible products

test Over a year ago

You already have the id of the category, you could just make it 2 queries. First query gets the category, second query gets the visible products from the category. That way you have both.

rubens.lopes Over a year ago

i know that i can use 2 queries, but due performece it would be nice to make it using only 1.

test Over a year ago

There won't be a performance issue, it already has to do those 2 queries anyway. Might as well just store the results from both separately.

Kek · Accepted Answer · 2012-07-04 20:26:29Z

0

Maybe something like:

var category = db.Products.Where(p=>p.IsVisible && p.CategoryID == id).Include("Category").ToList().Select( p=> p.Category).Distinct();

It may not be ideal because of the ToList... but I can see no other way right now.

Maybe you could change the Distinct into a FirstOrDefault()...

var category = db.Products.Where(p=>p.IsVisible && p.CategoryID == id).Include("Category").ToList().FirstOrDefault().Category;

Not tested either...

answered Jul 4, 2012 at 20:26

Kek

3,2052 gold badges23 silver badges26 bronze badges

Collectives™ on Stack Overflow

Lambda expression filtering included related data with Entity Framework

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related