efcore: Returning `SqlFragmentExpression` from `HasTranslation` doesn't work (switches to client evaluation)

I have the following function mapping with HasTranslation:

public static partial class EntityFrameworkExtensions
{
    public static int TotalRowCount()
        => throw new NotSupportedException();

    public static void HasTotalRowCountFunction(this ModelBuilder mb)
    {
        mb
            .HasDbFunction(() => TotalRowCount())
            .HasTranslation(arguments =>
            {
                return new SqlFragmentExpression("COUNT(*) OVER()");
            });
    }
}

But when I use the TotalRowCount function in a projection, the SqlFragmentExpression is ignored and the TotalRowCount function is evaluated client-side (the NotSupportedException is thrown). When I return a SqlFunctionExpression instead, the correct SQL is generated and the SQL function is correctly called.

I am observing a similar behavior when using an `IMethodCallTranslator´ to do the same translation. But in this case another strange behavior can be observed: If my function has a parameter, the translator is called, if it has no parameters (provided by LINQ query), like the method above, the translator isn’t even called.

I did look into the EF source code quite a bit but I am feeling kind of lost. Could you please give me a tip why this doesn’t work, how I could possibly achieve this simple mapping or at least some hints where I could look in the source code.

I really appreciate any help. Thank you.

About this issue

  • Original URL
  • State: open
  • Created 3 years ago
  • Comments: 18 (9 by maintainers)

Commits related to this issue

Most upvoted comments

Design decision: we think allowing SqlFragmentExpression to have a type mapping is reasonable; SqlFragmentExpression is a very advanced escape hatch, and it doesn’t make much sense to specifically block that there (especially given that people are already working around that limitation as above).

Ah I see, the part I was missing was that the COUNT(*) OVER () was already over the result of a complex query, but before the paging; so that to get the same you’d have to redo the query (without paging) and do COUNT(*) over that. The repetition of the query is the problem here.

So thanks, that’s a pretty convincing scenario for enabling some sort of support for window functions - I’ve added a note to https://github.com/dotnet/efcore/issues/12747#issuecomment-1933796780.

Problem 1: Can’t use SqlFragment directly

  • The 1st problem here is that a SqlFragmentExpression currently cannot have a type mapping, and our query pipeline generally wants nodes in the tree to have one; when a lambda is translated, we apply the default type mapping on the result (if it doesn’t already have one), and fail translation if that still has no type mapping.
  • The reason SqlFragmentExpression can’t have a type mapping, is that it wasn’t meant for use as an actual expression type, but rather just as something that’s used to generate some SQL (like the star in COUNT(*)).
  • We can consider relaxing this and allowing SqlFragmentExpression to have a type mapping - this would unblock this usage.
  • An argument against this could be that it would lead users dow the wrong path of using SqlFragmentExpression when it shouldn’t.
  • For example, using SqlFragmentExpression to express a COUNT(*) OVER () as is done above would make our query pipeline not recognize the node as an actual COUNT function call, in case there’s such logic (not saying there is specifically).
  • But as this issue shows, there are in any case ways around the type mapping problem (i.e. having a SqlFunctionExpression that is later converted to SqlFragmentExpression). In other words, if users have decided to extend the query pipeline, they’re already doing very advanced/risky stuff on their own, and blocking them via SqlFragmentExpression’s type mapping isn’t very useful.

Problem 2: Projection gets screwed up for subquery

  • In @alienwareone’s example, the ordering needs to be projected out of the subquery, since it’s preserved (the query really does return result in that order, as opposed to e.g. if the subquery where within Contains).
  • When projecting an ordering out of a subquery, EF simply takes the expression inside the ordering; it’s usually a column (e.g. Discount), but in this case it has been replaced by a SqlFragmentExpression, which EF knows nothing about - so it just lifts it.
  • This is an example of the limits of SqlFragmentExpression; the NULLS LAST component really is part of the ordering itself (the wrapping OrderingExpression), which is also where the ASC/DESC part is; it isn’t part of the thing inside the ordering, as is being hacked here.
  • So OTOH I don’t see a solution involving the SqlFragmentExpression hack - this really needs to be dealt with via a PostgreSQL-specific PgOrderingExpression, which has NULLS FIRST as a feature (that’s tracked by https://github.com/npgsql/efcore.pg/issues/627)

Was trying to do the same by returing an SqlFragmentExpression directly but it will throw an InvalidOperationException: ‘The LINQ expression ‘[expression]’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to ‘AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or ‘ToListAsync’.’

Returning a derived SqlExpression which then returns the SqlFragmentExpression works, but unfortunately not with subqueries.

Reproduction code that tries to workaround the NULLS LAST issue in PostgreSQL in a very hacky way 😉

#define PG_DESCENDING_WITH_NULLS_LAST_EXPRESSION

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

await using var dbContext = new ApplicationDbContext();
await dbContext.Database.EnsureDeletedAsync();
await dbContext.Database.EnsureCreatedAsync();

var sql1 = dbContext.Products
    .OrderBy(x => x.Discount.DescendingWithNullsLast())
    .ToQueryString();

Console.WriteLine(sql1);
// 👍
// SELECT p."Id", p."Discount", p."Name", p."Price"
// FROM "Products" AS p
// ORDER BY p."Discount" DESC NULLS LAST

var sql2 = dbContext.Products
    .Include(x => x.ProductAttributes)
    .OrderBy(x => x.Discount.DescendingWithNullsLast())
    .Skip(10)
    .Take(10)
    .ToQueryString();

Console.WriteLine(sql2);
// SELECT t."Id", t."Discount", t."Name", t."Price", p0."Id", p0."Name", p0."ProductId"
// FROM (                                                                     👇👇👇
//     SELECT p."Id", p."Discount", p."Name", p."Price", p."Discount" DESC NULLS LAST AS c
//     FROM "Products" AS p
//     ORDER BY p."Discount" DESC NULLS LAST
//     LIMIT @__p_0 OFFSET @__p_0
// ) AS t
// LEFT JOIN "ProductAttribute" AS p0 ON t."Id" = p0."ProductId"
// ORDER BY t.c, t."Id"

public sealed class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
        .UseNpgsql("Host=localhost;Username=postgres;Password=postgres;Database=EfCorePostgresqlNullsLast")
        .LogTo(Console.WriteLine, LogLevel.Debug)
        .EnableSensitiveDataLogging()
        .EnableDetailedErrors();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
#if PG_DESCENDING_WITH_NULLS_LAST_EXPRESSION
        modelBuilder
            .HasDbFunction(typeof(OrderByExtensions).GetMethod(nameof(OrderByExtensions.DescendingWithNullsLast), [typeof(int?)])!)
            .HasTranslation(e =>
            {
                var column = (ColumnExpression)e[0];
                return new PgDescendingWithNullsLastExpression(column, e[0].Type, e[0].TypeMapping);
            });
#else
        // System.InvalidOperationException: The LINQ expression 'DbSet<Product>().OrderBy(p => p.Discount.DescendingWithNullsLast())' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
        modelBuilder
            .HasDbFunction(typeof(OrderByExtensions).GetMethod(nameof(OrderByExtensions.DescendingWithNullsLast), [typeof(int?)])!)
            .HasTranslation(e =>
            {
                var column = (ColumnExpression)e[0];
                return new SqlFragmentExpression($"""{column.TableAlias}."{column.Name}" DESC NULLS LAST""");
            });
#endif
    }
}

public sealed class Product
{
    public int Id { get; private set; }
    public required string Name { get; set; }
    public required int Price { get; set; }
    public required int? Discount { get; set; }
    public ICollection<ProductAttribute> ProductAttributes { get; private set; } = [];
}

public sealed class ProductAttribute
{
    public int Id { get; private set; }
    public required string Name { get; set; }
}

public sealed class PgDescendingWithNullsLastExpression(
    ColumnExpression column,
    Type type,
    RelationalTypeMapping? typeMapping)
    : SqlExpression(type, typeMapping)
{
    protected override void Print(ExpressionPrinter expressionPrinter)
    {
    }

    protected override Expression VisitChildren(ExpressionVisitor visitor)
    {
        return new SqlFragmentExpression($"""{column.TableAlias}."{column.Name}" DESC NULLS LAST""");
    }
}

public static class OrderByExtensions
{
    // Only works inside OrderBy() and NOT OrderByDescending()
    public static int? DescendingWithNullsLast(this int? _)
    {
        throw new NotImplementedException();
    }
}

.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <LangVersion>preview</LangVersion>
    <Nullable>enable</Nullable>
    <WarningsAsErrors>nullable</WarningsAsErrors>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.0" />
  </ItemGroup>

</Project>

Any chance you can post a minimal console program that shows this happening?

I have found a way to achieve the desired result but it is VERY hacky:

public static void HasTotalRowCountFunction(this ModelBuilder mb)
{
    mb
        .HasDbFunction(() => TotalRowCount())
        .HasTranslation(arguments =>
        {
            return new SqlFunctionExpression(
                "COUNT",
                new SqlExpression[] { new SqlFragmentExpression("*) OVER(") },
                false,
                new[] { false },
                typeof(int),
                null);
        });
}

But I am afraid that evil ghosts will haunt me in my nights dreams for the rest of my life if I put this into production… So I would really prefer a cleaner solution for this.