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)

Only with third-party tools, f/e https://stackoverflow.com/a/70819284/861716. — Gert Arnold, May 12 '22 at 14:43
It looks like this is the relevant feature request: https://github.com/dotnet/efcore/issues/13239 — matt, May 19 '22 at 19:55

Svyatoslav Danyliv · Accepted Answer · 2022-05-13T04:08:16.893

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:

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

edited May 13 '22 at 04:08

answered May 12 '22 at 16:41

Svyatoslav Danyliv

21,911
3
16
32

I don't really expect to have any reason to think 3.x would support it, I just wanted to get out ahead of "update everything" responses. It looks like FilterByItems builds a big WHERE clause like "(id=b[0].id && name=b[0].name) || (id=b[1].id && name=b[1].name) ...", which could actually work fine at the scale I'm working with. When you say "exactly the same SQL", do you mean as in my question or as FilterByItems generates? – matt May 12 '22 at 21:04
1

I mean SQL from question. – Svyatoslav Danyliv May 13 '22 at 04:06

score 0 · Answer 2 · answered May 13 '22 at 05:26

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.

EFCore join values

2 Answers2