Linq2Entities, many to many and dynamic where clause

I'm fairly new to Linq and struggling using dynamic where over a many to many relationship.

Database tables are like so:

Products <-> Products_SubCategories <-> SubCategories

with Products_SubCategories being a link table.

My full linq statement is

 db.Products.Where("it.SubCategories.SubCategoryID = 2")
                   .Include("SubCategories")
                   .OrderBy(searchOrderBy)
                   .Skip(currentPage * pageSize)
                   .Take(pageSize)
                   .ToList()
                   .ForEach(p => AddResultItem(items, p));

So ignoring everything bar the Where() I'm just trying to pull out all products which are linked to sub category ID 2, this fails with

To extract pro开发者_高级运维perties out of collections, you must use a sub-query to iterate over the collection., near multipart identifier, line 8, column 1.

I think using the SQL-esque syntax I can do a subquery as per this link. However I'm not sure how to do that in the lambda / chaining syntax.

This is the start of a search function and I would like to build up the where string dynamically, as I have with the searchOrderBy string to avoid a large SELECT CASE. Products is linked to another table via a link table that I will need to include once I understand how to do this example.

Any help would be much appreciated!

Thanks

This is wrong:

db.Products.Where("it.SubCategories.SubCategoryID = 2")

SubCategories is a list. It does not have a property called SubCategoryID. Rather, it contains a group of entities which each have a property called SubCategoryID. That's a critical distinction.

When you run into a situation where you don't know how to proceed in there are multiple problems, it is good to break the problem down into several, smaller problems.

Let's start by removing the dynamic query. It will be easier to solve the problem with a non-dynamic query. Once you've done that, you can go back and make it dynamic again.

So start by using the non-dynamic syntax. Type something like this in Visual Studio, and see what IntelliSense does for you:

db.Products.Where(p => p.SubCategories.

You will quickly see that there is no SubCategoryID property. Instead, you will see a bunch of LINQ API methods for working with lists. If you know LINQ well, you will recognize that the Any() method is what you want here:

db.Products.Where(p => p.SubCategories.Any(sc => sc.SubCategoryID == 2))

Go ahead and run that query. Does it work? If so, you can move ahead to making it dynamic. I'm no ESQL expert, but I'd start with something along the lines of:

db.Products.Where("EXISTS(SELECT SC FROM it.SubCategories AS SC WHERE SC.SubCategoryID = 2");

As an aside, I use MS Dynamic Query ("Dynamic LINQ") for this sort of thing rather than Query Builder, as it's more testable.

It worked for me.

db.Products.Where("SubCategories.Any(SubCategoryID = 2)")