linq2db / linq2db.EntityFrameworkCore

Bring power of Linq To DB to Entity Framework Core projects
MIT License
449 stars 39 forks source link

`TempTable<string>` doesn't work with `In` #366

Open AntonC9018 opened 8 months ago

AntonC9018 commented 8 months ago

The code below throws the exception Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'String'.

var values = Enumerable.Range(0, 100).Select(i => i.ToString()).ToArray();
await using var linq2dbContext = dbContext.CreateLinqToDBContext();
await using var tempTable = await linq2dbContext.CreateTempTableAsync(values);
var result = await dbContext
    .Set<SomeTypeWithCodeStringProperty>()
    // ReSharper disable once AccessToDisposedClosure
    .Where(cp => cp.Code.In(tempTable))
    .ToArrayAsync();

If I change ToArrayAsync to ToArrayAsyncLinqToDB, it throws Microsoft.Data.SqlClient.SqlException (0x80131904): The multi-part identifier "tempdb..#String" could not be bound.

AntonC9018 commented 8 months ago

If wrapping the string is required (why would it? I'd rather not), the exception should say clearly what's wrong.

AntonC9018 commented 8 months ago

To workaround, you can wrap the strings, and then select them back again from the table. Of course, this does mean a bunch of garbage will be created for no reason.

public sealed record WrappedString(string Value);

// ...

IAsyncDisposable? disposable = null;
IQueryable<TModel> tempTableQueryable;
if (typeof(TModel) == typeof(string))
{
    var valuesStrings = (string[]) (object) values;
    var wrappedStrings = new WrappedString[valuesStrings.Length];
    for (int i = 0; i < valuesStrings.Length; i++)
        wrappedStrings[i] = new(valuesStrings[i]);

    var tempTable = await linq2dbContext.CreateTempTableAsync(
        wrappedStrings,
        cancellationToken: cancellationToken);
    disposable = tempTable;
    tempTableQueryable = (IQueryable<TModel>) tempTable.Select(t => t.Value);
}
else
{
    var tempTable = await linq2dbContext.CreateTempTableAsync(
        values,
        cancellationToken: cancellationToken);
    disposable = tempTable;
    tempTableQueryable = tempTable;
}

// ...
// (in a catch or finally, presumably)

if (disposable is not null)
    await disposable.DisposeAsync();