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

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.

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 16 (9 by maintainers)

Most upvoted comments

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

sdanyliv on Nov 30, 2021

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]

sdanyliv on Nov 29, 2021