EF Core: Linq, Where with object list does work

Question

I have Database Table with a composite primary key, so want to check if both key pairs are matching

  
public async Task<IList<AccountingAccountCategoryMap>> GetList(IEnumerable<AccountingAccountCategoryKey> keys)
        {
            return await Query.Where(item => keys.Any(x =>
                x.CategoryId == item.CategoryId && x.AccountingAccountId == 
                item.AccountingAccountId)).ToListAsync();
        }

I get this error:

{ "Message": "The LINQ expression 'x => x.CategoryId == EntityShaperExpression: \r\n
Orderlyze.Service.DL.Contract.Entity.AccountingAccountCategoryMap\r\n ValueBufferExpression: \r\n ProjectionBindingExpression: EmptyProjectionMember\r\n IsNullable: False\r\n.CategoryId && x.AccountingAccountId == EntityShaperExpression: \r\n
Orderlyze.Service.DL.Contract.Entity.AccountingAccountCategoryMap\r\n ValueBufferExpression: \r\n ProjectionBindingExpression: EmptyProjectionMember\r\n IsNullable: False\r\n.AccountingAccountId' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.", "Inner": "" }

The Question is why does work? And is there way to fix it without Client Evaluation?

You have a database, not a list. EF Core will translate the LINQ query has to be translated to a SQL query. You can't use expressions that can't be translated to SQL. The query you wrote tries to execute a client-side search based on database data. — Panagiotis Kanavos, Jun 30 '22 at 11:20
In fact, this LINQ query looks like an old-style JOIN : `FROM table1, table2 where table1.ID=table2.table1ID and table1.categoryID=table2.CategoryID`. You can't join client and server-side data using EF Core, no matter how you write the query. To get this to work you'd have to use a SQL query with table-valued parameters and JOIN between the table parameter and the actual table. Or write a chain of `OR` clauses that compare the table data against pairs of client keys. An `IN` clause is essentially a chain of `OR` equality checks — Panagiotis Kanavos, Jun 30 '22 at 11:24

score 0 · Answer 1 · answered Jun 30 '22 at 11:16

What is happening here is EF cannot execute the query fully in the server. That’s probably because of the keys.

If the keys are stored in the database then you could try referring to the table in the database instead. Or if there are a limited number of keys you could try specifying them explicitly (or possibly using ToList on them).

EF Core: Linq, Where with object list does work

1 Answers1