EFCore join values-CodePudding

I'm trying to join a big table to a small list of data pairs using EFCore 2.1.1. I want this join to happen server-side, rather than trying to download the whole table, e.g translating to something like:

SELECT a.*
FROM Groups AS a  
INNER JOIN (VALUES (1, 'admins'), (2, 'support'), (1, 'admins')) AS b(organization_id, name)   
ON a.organization_id = b.organization_id AND a.name = b.name;

or something equivalent (e.g. using common table expressions). Is this possible? If so, how? Passing a list of objects to a LINQ .join seems to always get handled client-side.

Due to massive testing debt and the EFCore 3 breaking change on client-side evaluation, upgrading is not an option for us at this time (but answers relevant to newer versions may help us push management)

CodePudding user response：

If you expect that EF Core 3.x can support this, you are wrong. If you plan to upgrade your application, better think about EF Core 6 and .net 6.

Anyway I know several options:

With extension method FilterByItems or similar

var items = ...

var query = context.Groups
    .FilterByItems(items, (q, b) => q.organization_id == b.organization_id && q.name == i.name, true);

With third party extension inq2db.EntityFrameworkCore version 2.x, note that I'm one of the creators. It will generate exactly the same SQL.

var items = ...

var query = 
    from g in context.Groups
    join b in items on new { g.organization_id, g.name } equals new { b.organization_id, b.name }
    select g;

var result = query.ToLinqToDB().ToList();