Support join with enumerable on client

[dotlogix]

Describe what is not working as expected.
Why EF Core cant translate this query, its is a simple join on multiple columns and looks the same as in many tutorials for EF.
Is this not implemented yet and the tutorials are related to ef6 or what's wrong with this implementation?

Is there any way to solve this kind of Problem in EF Core?

protected override Task<List<TEntity>> LoadEntitiesAsync(DbContext context, IEnumerable<(string fsymId, string feItem, DateTime feFpEnd)> keys) {
return context.Set<TEntity>()
        .Join(keys,
          e => new { p1= e.FsymId, p2=e.FeItem, p3 = e.FeFpEnd },
          i => new { p1= i.fsymId, p2=i.feItem, p3 = i.feFpEnd },
          (e, i) => e)
        .ToListAsync();
		}

Further technical details

EF Core version: 2.2.6.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system:
IDE: (e.g. Visual Studio 2019)

[smitpatel]

keys is client side enumerable. The join is purely client side. You can just get all TEntity-ies from server and do join on client. If not then how are you expecting this to be translated to server? Can you write a SQL translation for it?

[dotlogix]

Sure I would expect sth like this:

SELECT e.* FROM [table] e
JOIN (SELECT * FROM VALUES ((p1, p2, p3), ...) AS i (p1, p2, p3)) ON e.p1=i.p2 AND e.p2=i.p2 AND e.p3=i.p3

It should be used to select multiple entities by a 3 column composite key

[smitpatel]

That is invalid SQL in SqlServer.

[dotlogix]

I executed this command on our local SQL instance and it worked as expected. I will try it again be be it is just a Syntax error. I wrote the Statement by Hand so maybe thats why it is invalid Syntax

SELECT *
FROM (VALUES (1,2)
, (3,4)
) t1 (c1, c2)

This is where I got it from it is valid SQL Server Statement

https://modern-sql.com/de/anwendung/select-ohne-from

[PawelGerr]

Interessting idea for JOIN with smaller collections.
The current implementation of EF requires the type (to join with) to be registered in OnModelCreating, i.e. something like

class MyClass
{ 
    string FsymId
    string FeItem
    DateTime FeFpEnd
}

void OnModelCreating(ModelBuilder builder)
{
    modelBuilder.Query<MyClass>();
}

After that we need a custom method/expression to generate the SQL. The easiest way would probably be the use of FromSql.

IQueryable<MyClass> keysQuery = ctx.CreateMagicallyQueryableFrom(keys);

----

var keysQuery = ctx.Query<MyClass>().FromSql("
SELECT * FROM ( VALUES ... )
");

Now, we could join with the keysQuery.

Don't know if it helps in your use case. But I'm usually working with big collection having hundreds or thousands or records so I'm using SqlBulkCopy to insert some data into temp tables then I use this temp table for joins. It looks like

// config
void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.ConfigureTempTableEntity<MyClass>();
}

// usage
IEnumerable<MyClass> keys = ...;

using(ITempTableQuery<MyClass> tempTableQuery = await ctx.BulkInsertIntoTempTableAsync(keys))
{
    IQueryable<MyClass> keysQuery = tempTableQuery.Query;
   // join with keysQuery the usual way
}

[roji]

This is now possible as part of #30426, by packing the local collection to JSON (or an array on PG). For example, see this test in our test suite:

LINQ:

public virtual Task Column_collection_Join_parameter_collection(bool async)
{
    var ints = new[] { 11, 111 };
    return AssertQuery(
        async,
        ss => ss.Set<PrimitiveCollectionsEntity>()
            .Where(c => c.Ints.Join(ints, i => i, j => j, (i, j) => new { I = i, J = j }).Count() == 2),
        entryCount: 1);
}

SQL:

@__ints_0='[11,111]' (Size = 4000)

SELECT [p].[Id], [p].[Bool], [p].[Bools], [p].[DateTime], [p].[DateTimes], [p].[Enum], [p].[Enums], [p].[Int], [p].[Ints], [p].[NullableInt], [p].[NullableInts], [p].[String], [p].[Strings]
FROM [PrimitiveCollectionsEntity] AS [p]
WHERE (
    SELECT COUNT(*)
    FROM OPENJSON([p].[Ints]) WITH ([value] int '$') AS [i]
    INNER JOIN OPENJSON(@__ints_0) WITH ([value] int '$') AS [i0] ON [i].[value] = [i0].[value]) = 2

[roji]

Duplicate of #30426