Using "IN" with entity framework [duplicate]

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
– MarcinJuraszek, Commented Nov 15, 2016 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
– D Stanley, Commented Nov 15, 2016 at 19:28
Knowing that SQL x in (...) translates to LINQ (...).Contains(x), you could try typing your SQL query in LINQ. — Ivan Stoev
– Ivan Stoev, Commented Nov 15, 2016 at 19:47

Sampath · Accepted Answer · 2016-11-15 20:02:38Z

1

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, ... };

edited Nov 15, 2016 at 20:02

answered Nov 15, 2016 at 19:57

Sampath

66.2k70 gold badges327 silver badges461 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using "IN" with entity framework [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related