Linq2Entities, many for many, and dynamic conditions where

Question

I am new to Linq and try my best to use dynamics where there are many relationships.

Database tables are as follows:

Products ↔ Products_SubCategories ↔ Subcategories

where Products_SubCategoriesis the table of links.

My complete linq instruction

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

So, ignoring all the lines Where(), I'm just trying to pull out all the products associated with the category 2 id, this does not work with

To extract properties from collections, you must use a subquery to iterate through the collection., Near multipart identifier, row 8, column 1.

I think, using SQL-esque syntax, I can make a subquery on this link . However, I'm not sure how to do this in lambda / chain syntax.

, , searchOrderBy, SELECT CASE. , , , .

!

Answer 1

:

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

SubCategories - . , SubCategoryID. , , , SubCategoryID. .

, , , , .

. . , .

, . - Visual Studio , IntelliSense :

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

, SubCategoryID. LINQ API . LINQ, , Any() - , :

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

. ? , , . ESQL, - :

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

, MS Dynamic Query ( "Dynamic LINQ" ) , Query Builder, .

Answer 2

.

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