Select multiple columns from table based on different set of multiple columns

Question

I'm trying to get query like this from LINQ/EF6 in C#

SELECT "ID_column" 
FROM "Entity" 
WHERE ("ColumnA","ColumnB") IN (('Value_0_0','Value_0_1'),('Value_1_0','Value_1_1'), ...);

But I'm getting the following error for the code bellow

Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context.

context.Entities
       .Where(e => values.Any(v => v.ColumnA == e.ColumnA && v.ColumnB == e.ColumnB))
       .Select(e => e.ID_column);

or

context.Entities
       .Where(e => values.Contains(new {e.ColumnA, e.ColumnB}))
       .Select(e => e.ID_column);

Is there any way how to select multiple columns from table based on different set of multiple columns from the same table in one query using LINQ?

I know there is a lot of questions related to the error I get, but I didn't find any related to multiple columns or how to solve this.

Your LINQ doesn't reflect your Sql query. Shouldn't your condition be something like `.Where(e => valueListOne.Contains(e.ColumnA) && valueListTwo.Contains(e.ColumnB))` — Vidmantas Blazevicius, Apr 23 '18 at 22:00
No, because it's multiple values which makes the row unique with AND operator, not OR. So the values collection contains objects of these unique combinations based on which I'm trying to get other values in the table. — user1833414, Apr 23 '18 at 22:05

gnud · Answer 1 · 2018-04-23T22:32:21.190

0

EF can't create IN queries like this, it seems. You can use LinqKit, or build the Expressions directly, in order to build up a predicate dynamically. This example builds a massive SQL statement filled with AND/OR and null checks, but it will run on the SQL server and not on the application side.

var predicate = LinqKit.PredicateBuilder.New<Entity>();
foreach(var v in values) {
  predicate.Or(e => v.ColumnA == e.ColumnA && v.ColumnB == e.ColumnB);
}
context.Entities
   .Where(predicate)
   .Select(e => e.ID_column);

Remember - even with an IN query you have to move all the criteria to the server, so this isn't that much worse.

edited Apr 23 '18 at 22:32

answered Apr 23 '18 at 22:13

gnud

77,584
5
64
78

Thank you. I know I have to move the predicate to the server. What I don't want is to get all the data to the client side and do the filtering on his side. – user1833414 Apr 23 '18 at 22:21
yes, I meant that constructing a massive "OR" SQL query isn't _that_ much worse than a massive IN query :) This query will also happen server side, the genererated SQL will be filled with variables and OR clauses. – gnud Apr 23 '18 at 22:30

score 0 · Answer 2 · answered Apr 23 '18 at 23:01

0

It seems like there isn't a good solution, but the best bet may be to double filter - once on the server and once on the client:

context.Entities
       .Where(e => values.Select(v => v.ColumnA).Contains(e.ColumnA) && values.Select(v => v.ColumnB).Contains(e.ColumnB))
       .AsEnumerable()
       .Where(e => values.Contains(new { e.ColumnA, e.ColumnB }))
       .Select(e => e.ID_column);

answered Apr 23 '18 at 23:01

NetMage

26,163
3
34
55

This is exactly what I don't want to do, because a lot of rows will contain at least one of the values so I would end up loading huge amount of data to the client to filter them out right after that. That's why I want to do it in one query when it's possible, on the server side, of course. – user1833414 Apr 23 '18 at 23:18
@user1833414 Unfortunately LINQ is not going to be helpful then. I think you will need to load the data to a temp table and then query it, assuming there is a significant amount in `values`. OR convert the data into a single value (not indexable). – NetMage Apr 23 '18 at 23:44

Select multiple columns from table based on different set of multiple columns

2 Answers2