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
- Allow SqlFragmentExpression to have Type/type mapping Closes #32969 — committed to roji/efcore by roji 3 years ago
- Allow SqlFragmentExpression to have Type/type mapping Closes #32969 — committed to roji/efcore by roji 3 years ago
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 doCOUNT(*)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
COUNT(*)).COUNT(*) OVER ()as is done above would make our query pipeline not recognize the node as an actualCOUNTfunction call, in case there’s such logic (not saying there is specifically).Problem 2: Projection gets screwed up for subquery
Discount), but in this case it has been replaced by a SqlFragmentExpression, which EF knows nothing about - so it just lifts it.NULLS LASTcomponent 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.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 😉
.csproj
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:
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.