[net5.0] Sequence '[Microsoft.EntityFrameworkCore.Query.QueryRootExpression]' cannot be converted to SQL

[RouR]

linq2db 3.1.6 linq2db.EntityFrameworkCore 5.0.2 Microsoft.EntityFrameworkCore 5.0.0 TargetFramework net5.0

var query = dbContext.SomeItems.Where(x => x.IsDeleted && x.Updated < dateTime).Take(20);

return dbContext.SomeItems
                .Where(x => query
                    .Select(y => y.PrimaryKeyStringId)
                    .Contains(x.PrimaryKeyStringId)
                )
                .ToLinqToDB()
                .DeleteAsync();

LinqToDB.Linq.LinqException : Sequence '[Microsoft.EntityFrameworkCore.Query.QueryRootExpression]' cannot be converted to SQL.
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertSequence(BuildInfo buildInfo, ParameterExpression param, Boolean throwExceptionIfCantConvert)
   at LinqToDB.Linq.Builder.WhereBuilder.Convert(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo, ParameterExpression param)
   at LinqToDB.Linq.Builder.MethodCallBuilder.Convert(ExpressionBuilder builder, BuildInfo buildInfo, ParameterExpression param)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertSequence(BuildInfo buildInfo, ParameterExpression param, Boolean throwExceptionIfCantConvert)
   at LinqToDB.Linq.Builder.TakeSkipBuilder.Convert(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo, ParameterExpression param)
   at LinqToDB.Linq.Builder.MethodCallBuilder.Convert(ExpressionBuilder builder, BuildInfo buildInfo, ParameterExpression param)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertSequence(BuildInfo buildInfo, ParameterExpression param, Boolean throwExceptionIfCantConvert)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertExpressionTree(Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.OptimizeExpressionImpl(Expression expr, HashSet`1 currentParameters)
   at LinqToDB.Linq.Builder.ExpressionBuilder.<>c__DisplayClass45_0.<OptimizeExpression>b__0(Expression e)
   at LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func)
   at LinqToDB.Linq.Builder.ExpressionBuilder.OptimizeExpression(Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertSelect(MethodCallExpression method)
   at LinqToDB.Linq.Builder.ExpressionBuilder.OptimizeExpressionImpl(Expression expr, HashSet`1 currentParameters)
   at LinqToDB.Linq.Builder.ExpressionBuilder.<>c__DisplayClass45_0.<OptimizeExpression>b__0(Expression e)
   at LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func)
   at LinqToDB.Expressions.Extensions.Transform2[T](IEnumerable`1 source, Func`2 func)
   at LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func)
   at LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func)
   at LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func)
   at LinqToDB.Linq.Builder.ExpressionBuilder.OptimizeExpression(Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertWhere(MethodCallExpression method)
   at LinqToDB.Linq.Builder.ExpressionBuilder.OptimizeExpressionImpl(Expression expr, HashSet`1 currentParameters)
   at LinqToDB.Linq.Builder.ExpressionBuilder.<>c__DisplayClass45_0.<OptimizeExpression>b__0(Expression e)
   at LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func)
   at LinqToDB.Expressions.Extensions.Transform2[T](IEnumerable`1 source, Func`2 func)
   at LinqToDB.Expressions.Extensions.Transform(Expression expr, Func`2 func)
   at LinqToDB.Linq.Builder.ExpressionBuilder.OptimizeExpression(Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertExpressionTree(Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder..ctor(Query query, IDataContext dataContext, Expression expression, ParameterExpression[] compiledParameters)
   at LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext, Expression expr)
   at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr)
   at LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache)
   at LinqToDB.Linq.ExpressionQuery`1.LinqToDB.Async.IQueryProviderAsync.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at LinqToDB.LinqExtensions.DeleteAsync[T](IQueryable`1 source, CancellationToken token)

It was ok before upgrade to 5.0

[sdanyliv]

I see. Will check. Could you please try the following as a workaround:

using var db = dbContext.CreateLinqToDBConnection();
var query = db.GetTable<SomeItem>.Where(x => x.IsDeleted && x.Updated < dateTime).Take(20);

return await db.GetTable<SomeItem>
                .Where(x => query
                    .Select(y => y.PrimaryKeyStringId)
                    .Contains(x.PrimaryKeyStringId)
                )
                .DeleteAsync();

[RouR]

workaround is work, thx

[sdanyliv]

Just for clarification. Why query is so complicated? You should have the same result with simplified query

using var db = dbContext.CreateLinqToDBConnection();
var query = db.GetTable<SomeItem>.Where(x => x.IsDeleted && x.Updated < dateTime).Take(20);

return await query.DeleteAsync();

[RouR]

"simplified query" is not work

DELETE FROM
    [SomeItem]
WHERE
    [SomeItem].[IsDeleted] = 1 AND DateTime([SomeItem].[UpdatedUtc]) < DateTime(@dateTimeOffset)
LIMIT @take

SQLite Error 1: 'near "LIMIT": syntax error'

complicated query is ok

DELETE FROM
    [SomeItem]
WHERE
    [SomeItem].[SomeItemId] IN (
        SELECT
            [t1].[SomeItemId]
        FROM
            (
                SELECT
                    [x].[SomeItemId]
                FROM
                    [SomeItem] [x]
                WHERE
                    [x].[IsDeleted] = 1 AND DateTime([x].[UpdatedUtc]) < DateTime(@dateTimeOffset)
                LIMIT @take
            ) [t1]
    )

[sdanyliv]

I see. Looks like we should handle that automatically. But thanks for pointing which provider do you use ;)

[RouR]

thanks