Entity Framework select with join and id

Question

Entity Framework select with join and id

Question

Simple question, but no luck with Google so far.

How do I turn this:

select * from Products 
left join ProductCategory on Products.ProductCode = ProductCategory.ProductCode
where ProductCategory.CategoryID = 1

into a query in Entity Framework Core?

This is what I have and isn't correct:

_context.Products
    .Where(p => p.ProductCategory.CategoryID == CategoryID)
    .ToList();

Thanks!

Edit

When I add an Include:

_context.Products
    .Include(p => p.ProductCategory)
    .Where(p => p.ProductCategory.CategoryID == CategoryID)
    .ToList();

I get this error in the image:

There is a CategoryID on ProductCategory, but it's trying to find one on the collection?

Rick Jolly

View more on Stack Overflow

m3n7alsnak3 · Accepted Answer · 4/23/2018 3:57:34 PM

You should use the Include() LINQ option - documentation. It is used for loading related data.

I'm not familiar with the exact names of your classes and properties, so treat this as a pseudo code, but it should look something like this:

_context.products.Include("Product_Categories")
                 .Where(p => p.product_category.category_id == category_id)

EDIT

It will be a lot easier if you post your classes, so that I can see the relations.

But I assume that your Product class has a list of ProductCategory and you want to get all the products, that have a specific category. Extending the approach that you already have, you need:

_context.products
.Include(p => p.product_category)
.Where(p => p.ProductCategory.Any(pc => pc.category_id == category_id))
.ToList();

Entity Framework select with join and id

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related