Linq join list and table

Question

I have a List<string> of ProductTypes such as Vegetables, Dairy, Meat which I receive from a query string in Asp.Net Core.

In the database, there is a Product table which contains a ProductType column that has each product's type in a text field)

I want to select from the Product database table all the Products that are in the ProductTypes list as received. I did this:

var Result =  (from p in _context.Product
             join t in ProductTypes on p.ProductType equals t  select p).ToList();

My effort above has an (incorrect) empty result except if only one ProductType is passed, in which case it works correctly.

How can I accomplish a correct Result set if multiple ProductTypes are passed?

Possible duplicate of [What is the syntax for an inner join in LINQ to SQL?](https://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql) — MethodMan, Dec 04 '17 at 06:06
@MethodMan my syntax is the same as the answers in the question you say I have duplicated. The only difference here is that because t comes from a list, not database, I can't use t.ProductType, I have to use t itself. — trajekolus, Dec 04 '17 at 06:15

score 2 · Accepted Answer · answered Dec 04 '17 at 06:17

2

You can use Contains instead of a join:

var result =
    from p in _context.Product
    where ProductTypes.Contains(p.ProductType)
    select p;

You rarely need joins in LINQ and in your special case where you have an in memory list and a database table, Contains is the way to go.

answered Dec 04 '17 at 06:17

Sefe

Thanks, this is helpful, and feels less awkward. But it still only works if only one ProductType is passed. – trajekolus Dec 04 '17 at 06:29
I have used contains like this countless times and it always worked. Are you sure the error is not somewhere else? You can get the SQL query from the LINQ query with `ToString` and try it directly on the DB. – Sefe Dec 04 '17 at 06:33
1

@jdoer1997: If your `ProductTypes` is `List` mentioned code should work. However if `ProductTypes` is csv then you have to use `ProductTypes.Split(',')` – Vandita Dec 04 '17 at 07:28
Sefe You are right, and my input was in fact csv as @Vandita pointed out – trajekolus Dec 05 '17 at 00:08
Just in case someone is wondering, this is a bad solution if you have a large list, especially when using guids, it makes the query super slow. It resolves into an in statement, and we know that "WHERE x IN ( [1000 guid here] )" is not good – Marius Van Der Berg Jun 03 '22 at 10:03

1 Answers1