EF 4 LINQ Expression load items based on related entity

Question

Here is the expression

x => x.stf_Category.CategoryID == categoryId

x refers to an Product Entity that contains a Category. I am trying to load all Products that match given categoryId.

In the db the Product table contains a Foreign Key reference to Category (via CategoryId).

Question: I think I am doing it wrong. Is there something else one has to do in EF4 to create a LINQ expression of this type?

Are there any good examples of EF4 Linq expressions out there? Specifically something that queries on the basis of related entities such as my problem ?

Thanks !

Answer 1

You're looking for the Include method.

var query = db.Products.Include("Categories");

This is commonly referred to as eager loading.

Entity Framework will 'infer' the JOIN constraint based on the mapping you have specified.

The "magic string" needs to match the Entity Set name on your EDMX.

Check out this post for more info.

EDIT

I'm a little confused as to whether you want the Products and Categories, or just the Products which have a specific Category ID.

If the latter, this is the way to go:

var query = from p in db.products
            join c in db.categories
            on p.CategoryId equals c.CategoryId
            where c.CategoryId == someCategoryId
            select p;

Keep in mind though, the above query is exactly the same result as your original query.

If p is a product, then p.Categories will look at the Navigational Property of your Product entity on the EDMX, in which case it will be your Category FK.

As long as you setup your Navigational properties right, p.Categories is fine.

Answer 2

If you are using EF4 and the association between Category and Product classes has been picked up and defined in your Model, then all products with a specific categoryID can be selected as simple as:

x => x.CategoryID == categoryID

You don't need to join nor an eager loading for that.