Using "IN" with entity framework

Question

I have 2 tables, Category and ProductToCategory.

Category has the name of all categories, and ProductToCategory has a list of all the categories assigned to a product. It is separated because 1 product can have multiple categories.

I am new to entity framework and am trying to get a list of categories that assigned to a specific product. I need to return the information from the Category table, but the ProductToCategory is where I search to get what is assigned.

I am trying to get all categories where the category ID is assigned to the specific product ID in the ProductTOCategory table.

In SQL I would type:

SELECT (Category Fields) FROM Category WHERE Category.Id 
IN (SELECT ProductToCategory.CategoryId FROM ProductToCategory 
WHERE ProductId = (THE PRODUCT ID))

Or something like that

It all depends on how your model is defined. With correctly defined navigation properties it should be as easy as `Products.Find(10).Categories`. That might end up with 2 calls to the database though, so you might want to do something like: `Categories.Where(c => c.Products.Select(p => p.ProductId).Contains(10))` — MarcinJuraszek, Nov 15 '16 at 19:27
First you need a collection of constant values, then use `Where(x => list.Contains(x))` to generate the `IN` clause. — D Stanley, Nov 15 '16 at 19:28
Knowing that SQL `x in (...)` translates to LINQ `(...).Contains(x)`, you could try typing your SQL query in LINQ. — Ivan Stoev, Nov 15 '16 at 19:47

Sampath · Accepted Answer · 2016-11-15T20:02:38.380

You can use join instead of IN here.

Hope you have a navigation property in between Category and ProductToCategory.Then you can do it as shown below.

var query =
    from t1 in context.Category
    where t1.ProductToCategory.ProductId == "THE-PRODUCT-ID"
    select new { t1.Col1, t1.Col2, ... };

If you don't have ,then use join as shown below.

var query =
    from t1 in context.Category
    join t2 in context.ProductToCategory
        on t1.Id equals t2.CategoryId
    where t2.ProductId == "THE-PRODUCT-ID"
    select new { t1.Col1, t1.Col2, ... };

Using "IN" with entity framework

1 Answers1