Windowed functions don't work? * is server-side method.

[virzak]

I'm getting an error 'RowNumber' is server-side method.

Can't find a window function test for anything with an Over clause When I created one it broke.

This line https://github.com/linq2db/linq2db.EntityFrameworkCore/blob/83dc47ed869cb60c5a0882c91134301c2def7335/Tests/LinqToDB.EntityFrameworkCore.SqlServer.Tests/IssueTests.cs#L53

was changed to test the window function:

                        var q = ctx.Issue73Entities
                                .Where(x => x.Name == "Name1_3")
-                               .Select(x => x.Parent!.Name + ">" + x.Name);
+                               .Select(x => Sql.Ext.RowNumber().Over().OrderBy(x.Name).ToValue());

                        var efItems = q.ToList();
                        var linq2dbItems = q.ToLinqToDB().ToList();

I wonder if there should be an options turned on or something similar to this code usually found in DbContext.OnConfiguring(DbContextOptionsBuilder optionsBuilder) method:

.UseSqlServer("conn-string", options => options.AddRowNumberSupport());

Here is the full stack trace:

System.InvalidOperationException
  HResult=0x80131509
  Message=An exception was thrown while attempting to evaluate a LINQ query parameter expression. See the inner exception for more information. To show additional information call 'DbContextOptionsBuilder.EnableSensitiveDataLogging'.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.GetValue(Expression expression, String& parameterName)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Evaluate(Expression expression, Boolean generateParameter)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression)
   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression)
   at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
   at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.ExtractParameters(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExtractParameters(Expression query, IParameterValues parameterValues, IDiagnosticsLogger`1 logger, Boolean parameterize, Boolean generateContextAccessors)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at LinqToDB.EntityFrameworkCore.SqlServer.Tests.IssueTests.Issue73Test() in Q:\Code\Libraries\linq2db.EntityFrameworkCore\Tests\LinqToDB.EntityFrameworkCore.SqlServer.Tests\IssueTests.cs:line 55

  This exception was originally thrown at this call stack:
    LinqToDB.AnalyticFunctions.RowNumber(LinqToDB.Sql.ISqlExtension)
    Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.GetValue(System.Linq.Expressions.Expression, out string)

Inner Exception 1:
LinqException: 'RowNumber' is server-side method.

[sdanyliv]

Actually no. We completely replace EF Core LINQ translator, so just option "I need some feature" will not work. If you need Window Functions - call ToLinqToDB()

[virzak]

I see. So should the example provided be breaking? Do you have a good SqlServer example with over clause?

[sdanyliv]

Which SQL do you need for translating? I can create sample right here. And what is not clear from documentation?

[virzak]

Ok, I got it to work after removing the var efItems = q.ToList(); line from the test case above.

What's confusing is that your main documentation for Window functions has no mention of ToLinqToDB().

Also, I'm a bit curious as to why your approach was to replace EF Core translator as opposed to using it as Thinktecture.EntityFrameworkCore. Not urgent though.

[sdanyliv]

Actually because it is linq2db documentation, which is lightweight ORM. This extension is connection point between two worlds. Our translator can fix a lot of EF gaps and weaknesses, especially in bulk operations vial LINQ.

I do not see source code for Thinktecture.EntityFrameworkCore to check what they are doing and probably it is paid or will be paid.

[virzak]

Thinktecture.EntityFrameworkCore's source code is here

There are also a few articles describing the approach. Here is one of them.

[sdanyliv]

My fault, jumped to link and messed up that I moved to Azure DevOps with repository. Not bad, but it's completely new and I see a lot of limitations and only Sqlite and SqlServer. Much better than other extensions. IMHO several years to make it comparable to linq2db. Anyway, if you need just RowNumber, this library is enough.

[virzak]

The ultimate goal is to get the last non null using window functions.

SELECT  Date,
        col1,
        CAST(  SUBSTRING( MAX( CAST(date AS BINARY(4)) + CAST(col1 AS BINARY(4)) ) OVER( ORDER BY date ROWS UNBOUNDED PRECEDING ),5, 4) AS DECIMAL(18,6)) AS lastval
FROM dbo.T1;

The approach is discussed here with the original solution linked, but behind a registration wall.

Since I'm only starting with your library, I'm not sure if something like this will be possible.

[sdanyliv]

What this part CAST(date AS BINARY(4)) + CAST(col1 AS BINARY(4)) should do?

[virzak]

Best explained in the Itzik Ben-Gan's article

[sdanyliv]

It is possible, but better to show whole SQL which works for you. I'm asking about these conversion to understand maybe linq2db already has such transformations. Even if not, it is not a problem.

[virzak]

Not sure what you mean by "whole sql". The article shows the entire sql for the last non null problem. See solution 2. The part CAST(date AS BINARY(4)) + CAST(col1 AS BINARY(4)) is explained there in its entirety.

[sdanyliv]

My fault, read several sentences and missed this part.

Ok, Binary conversion is not present in linq2db, but easily expandable:

public static class SqlExt
{
    [Sql.Function(ServerSideOnly = true)]
    public static int Binary(int length) => throw new NotImplementedException();
}

And you can use it for your query:

var query = table.Select(x => new
{
    x.Date,
    x.Col1,
    lastval = Sql.Ext
        .Max(Sql.Convert(Sql.Decimal(18, 6),
            Sql.Convert(SqlExt.Binary(4), x.Date) + Sql.Convert(SqlExt.Binary(4), x.Col1)))
        .Over()
        .OrderBy(x.Date)
        .Rows.UnboundedPreceding
        .ToValue()
});

Generated SQL:

SELECT
    [x].[Date],
    [x].[Col1],
    MAX(Convert(Decimal(18, 6), Convert(Binary(4), [x].[Date]) + Convert(Binary(4), [x].[Col1]))) OVER(ORDER BY [x].[Date] ROWS UNBOUNDED PRECEDING)
FROM
    [SampleClass] [x]

[virzak]

Thanks,

This is very close to what's needed.

A few issues:

• I can't seem to do anything after the window function returns, such as Sql.Ext.Max(...) + 1
• Does SqlToDb support bitwise operations? Both EF Core and Sql Server do .Select(z => z.Col1 & 0xff)
• This might be a showstopper. A single query cannot use both EF Core and LinqToDb it seems (correct me if I'm wrong). The project I'm working on generates a lot of expressions, compatible with EF Core and its extensions, such as Thinktecture. If the implementation was to incorporate LinqToDb, we couldn't have anything breaking, such as the two examples mentioned above.
• Namespace collision is also a problem, but a minor one.

Cheers

[sdanyliv]

I can't seem to do anything after the window function returns, such as Sql.Ext.Max(...) + 1

You forgot .ToValue()

Does SqlToDb support bitwise operations? Both EF Core and Sql Server do .Select(z => z.Col1 & 0xff)

Sure, why not.

This might be a showstopper. A single query cannot use both EF Core and LinqToDb it seems (correct me if I'm wrong). The project I'm working on generates a lot of expressions, compatible with EF Core and its extensions, such as Thinktecture. If the implementation was to incorporate LinqToDb, we couldn't have anything breaking, such as the two examples mentioned above.

If you use extensions from Thinktecture we will fail. Anyway, there is extension point and Expression Tree can be corrected to be parseable by our LINQ Translator.

Namespace collision is also a problem, but a minor one.

Yeah, asyncs

[MaceWindu]

Closing as answered