EFCore join values

Question

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)

Answer 1

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:

1. 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);

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

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();

Answer 2

You could solve this problem with a Table Value Parameter.

First define a database type;

IF TYPE_ID(N'[IdName]') IS NULL
    CREATE TYPE [IdName] AS TABLE (
        [Id] int NOT NULL
        [Name] nvarchar(max) NOT NULL
    )

Then you can build an SqlParameter from an IEnumerable;

public class IdName
{
    public int Id { get; set; }
    [Required]
    public string Name { get; set; }

    public static SqlParameter ToParameter(IEnumerable<IdName> values)
    {
        var meta = new SqlMetaData[]{
            new SqlMetaData(nameof(Id), SqlDbType.Int),
            new SqlMetaData(nameof(Name), SqlDbType.NVarChar, int.MaxValue)
        };

        return new SqlParameter()
        {
            TypeName = nameof(IdName),
            SqlDbType = SqlDbType.Structured,
            Value = values.Select(v => {
                var record = new SqlDataRecord(meta);
                record.SetInt32(0, v.Id);
                record.SetString(1, v.Name);
                return record;
            })
        };
    }
}

Then define an EF Core query type, and you can turn that SqlParameter into an IQueryable;

public void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<IdName>(e =>
    {
        e.HasNoKey();
        e.ToView(nameof(IdName));
    });
}

public static IQueryable<IdName> ToQueryable(DbContext db, IEnumerable<IdName> values)
    => db.Set<IdName>().FromSqlInterpolated($"select * from {ToParameter(values)}");

And now you can use that IQueryable in a Linq join.