Custom scalar database function sometimes evaluated on client

[martin-hronsky]

I have strange issue when call custom database function added to the Db context.
In following query:

(from b in basicPolicyData
                         join e in extendedPolicyData on new
                                                             {
                                                                 b.PolicyId,
                                                                 b.VersionNo
                                                             }
                             equals new
                                        {
                                            e.PolicyId,
                                            e.VersionNo
                                        }
                         join c in policyCollectionAccount on e.PolicyId equals c.PolicyId
                         where b.PolicyId == policyId &&
                               b.VersionNo == policyVersionNo
                         select new
                                    {
                                        Basic = b,
                                        Extended = e,
                                        CollectionAccount = c,
                                        AnniversaryDay = DbFunctions.GetPolicyAnniversaryDay(c.AnniversaryDateModeId, b.BeginDate, b.TechnicalBeginDate, c.AnniversaryDate),
                                        NextAnniversaryDate =
                                            DbFunctions.GetPolicyAnniversaryDateGreaterOrEqualToDate(c.AnniversaryDateModeId, b.BeginDate, b.TechnicalBeginDate, c.AnniversaryDate, b.VersionValidFrom, true),
                                    })

both functions are evaluated on client.
The same query, but without retrieving table "b":

(from b in basicPolicyData
                         join e in extendedPolicyData on new
                                                             {
                                                                 b.PolicyId,
                                                                 b.VersionNo
                                                             }
                             equals new
                                        {
                                            e.PolicyId,
                                            e.VersionNo
                                        }
                         join c in policyCollectionAccount on e.PolicyId equals c.PolicyId
                         where b.PolicyId == policyId &&
                               b.VersionNo == policyVersionNo
                         select new
                                    {
                                        //Basic = b,
                                        Extended = e,
                                        CollectionAccount = c,
                                        AnniversaryDay = DbFunctions.GetPolicyAnniversaryDay(c.AnniversaryDateModeId, b.BeginDate, b.TechnicalBeginDate, c.AnniversaryDate),
                                        NextAnniversaryDate =
                                            DbFunctions.GetPolicyAnniversaryDateGreaterOrEqualToDate(c.AnniversaryDateModeId, b.BeginDate, b.TechnicalBeginDate, c.AnniversaryDate, b.VersionValidFrom, true),
                                    })

results in proper SQL statement - both database function calls are included in the query.
Even more strange is that when I query for all "b" table columns by naming them explicitly (instead of asking for "b" table):

(from b in basicPolicyData
                         join e in extendedPolicyData on new
                                                             {
                                                                 b.PolicyId,
                                                                 b.VersionNo
                                                             }
                             equals new
                                        {
                                            e.PolicyId,
                                            e.VersionNo
                                        }
                         join c in policyCollectionAccount on e.PolicyId equals c.PolicyId
                         where b.PolicyId == policyId &&
                               b.VersionNo == policyVersionNo
                         select new
                                    {
                                        b.PolicyId,
                                        b.VersionNo,
                                        ...
                                        Extended = e,
                                        CollectionAccount = c,
                                        AnniversaryDay = DbFunctions.GetPolicyAnniversaryDay(c.AnniversaryDateModeId, b.BeginDate, b.TechnicalBeginDate, c.AnniversaryDate),
                                        NextAnniversaryDate =
                                            DbFunctions.GetPolicyAnniversaryDateGreaterOrEqualToDate(c.AnniversaryDateModeId, b.BeginDate, b.TechnicalBeginDate, c.AnniversaryDate, b.VersionValidFrom, true),
                                    }).FirstOrDefault();

both database function calls are properly included in the SQL statment.

We are using EF Core v 2.1.3 against SQL Server 2012 database.

[pmiddleton]

The functions will be evaluated on the client if something else in the query forces client evaluation. Pulling back that entire entity must be causing client eval, though I'm not sure why.

Does the sql look correct if you run the first example without the 2 function calls?

[martin-hronsky]

I also thought, that something on entity may cause that client evaluation. That's why I first tried to retrieve just one column from it, later added more of them and it turned out that even query with all columns from that table works. But only if they are named explicitly.
SQL profiler proved that query looks as expected - all columns nicely enumerated and function call also generated as expected. Without function call, but with whole table (at once, not by making columns) the SQL query looked very similar to the query explicitly with all columns (I did not made literal comparison, but I will do it).
I thought my mapping may be contains something strange, so also tried to remove mapping (left only table name and primary key). The behaviour was the same.
I will try to isolate the problem into small console application and share it so that may be you will spot the source of the problem.

[pmiddleton]

Yes a small repo project would be useful to track down the issue.

cc: @smitpatel - he might already have run into this.

[smitpatel]

Duplicate of #11186

[martin-hronsky]

Thank you very much for your help. I will prepare small repo for it (I wonder if it can be reproduce even outside of project).
It indeed looks similar to #11186 which I have also encountered with some other query (and overcome using joins).
On the other hand, in this case, there are two more tables included into projection and they do not cause this behaviour. When table "e" and "c" are in the projection, everything works fine. Only when I include also table "b" into projection, it stops working. Is there some limit on number of projected tables/columns?

In scenario when EF is not able to include custom scalar function call into query I would expect that it will run a separate query (just like in #11186) and not do evaluation on client. I might be wrong, but I think, it is very likely that custom scalar function will not have any client side fallback implementation (in my case just an exception is thrown as implementation). From performance perspective it is of course not the best but at least it would work.
May be some extra parameter to model builder configuration or DbFunction attribute would help EF to decide, if in these scenarios the client side evaluation is even possible or separate query must be issued. Developer will decide if he will try to rewrite the query, duplicate server side function code also to client side or accept performance penalty.
Of course, preffered is to have it all in one query without any sepparate calls.

[ackava]

@ajcvickers I found root cause of this problem.

When a query is divided into subquery (and DBFunctiion is used in SELECT clause), client evaluation is performed while joining results at client.

For example,

   var customers = db.Customers.Where( ... )
            .Select( x => new { 
                  // this will be executed in server
                  Property = DbFunctions.Some( ... ) ,
                  Orders = x.Orders
                                       .Where( ... )
                                       .Select( o => new {
                                             // when you call dbfunction with parameters from customer and order
                                             // this will be executed at client
                                              OrderProperty = DbFunctions.Some ( x.CustomerProperty, o.OrderProperty)
                                       } )
            } )

The only solution to this is, Static function should contain logic for client side evaluation.

           [DbFunction("MaxDate")]
           public static DateTime? MaxDate( DateTime? d1, DateTime? d2) {

                   // this is wrong...
                   // throw new NotSupportedException();

                  // this is correct...
                  return d1 > d2 ? d2 : d1;
           }

DbFunction static must contain appropriate logic for client side evaluation, this is required for splitting subqueries as well as in memory unit testing.

[martin-hronsky]

@ackava duplicating code is not an option and I would not make it a recommendation or any official preferred approach. It is maintenance nightmare in enterprise applications to have the same code twice.
We have more that 800k lines of code in DB functions and procedures and to have them duplicate in .NET is wrong.

One possible workaround is to have .NET implementation of function installed as CLR code also in SQL server so T-SQL implementation will be replaced by SQL .NET CLR version (without breaking other T-SQL code). But this approach works also only for simplier cases (function/procedures without DB access).

In this particular case (until official solution is available), we will either separate this query into two DB calls or pull required entities by naming their attributes instead of pulling whole entities (since that works properly).

[ackava]

@martin-hronsky I definitely agree with maintenance nightmare. The problem lies with outer apply which i not supported in mysql, postgresql and other non relational systems. Such queries were working correctly in EF6, but it was using sql server specific feature.

@ajcvickers
There is one way though, query translator can fetch additional parent columns and translate sql in Select clause instead of client evaluation, it is certainly possible.

[ajcvickers]

@smitpatel to follow up

[smitpatel]

The underlying issue for this particular case has been fixed in #11186. Given client eval is only supported in projection, we try to evaluate as much of projection code on server side as possible, hence DbFunction will translate to server when possible. The only case where that is not the case is if the arguments of DbFunction are causing client evaluation. In such case, it is ok to call into client code or throw exception because we cannot improve the scenario in any other way.

[ajcvickers]

Closing this as duplicate. Additional notes:

• Client eval for most parts of query is no longer a concern in 3.0
• Client eval in a projection could, theoretically, execute the function. If it does, then this should either work or throw based on whether or not a client implementation has been written.