Efcore: Query: Translate Exists

[smitpatel]

```C#
var query = db.Blogs.FirstOrDefault(b => b.Posts.Any(p => p.Name == "test"));

Translate to
```SQL
      SELECT TOP(1) [b].[Id]
      FROM [Blogs] AS [b]
      WHERE EXISTS (
          SELECT 1
          FROM [Post] AS [p]
          WHERE ([p].[Name] = N'test') AND ([b].[Id] = [p].[BlogId]))

But
C# var query = db.Blogs.FirstOrDefault(b => b.Posts.Exists(p => p.Name == "test"));
client evals. 🤦‍♂️

[ilmax]

@smitpatel Should exists have the same semantics as any here?

[smitpatel]

@ilmax - Yes. Based on implementation of both. It should be exact copy.

[ilmax]

I'm giving it a try. Just to be sure, for exists you mean this method right?

[smitpatel]

Yes & Array.Exists may be.

[ilmax]

👍 Will try!

[ilmax]

@smitpatel I made some small progress on this, I just want to check I took the right direction. What I've done is implementing a new ExistsToAnyResultOperatorExpressionRewriter : ExpressionVisitorBase that rewrites a MethodCallExpression similar to SomeNavigationPropertyOfTypeList.Exists( aPredicateExpression ) into something like SomeNavigationPropertyOfTypeList.Any( aFuncExpression ) e.g.

var query = db.Blogs.FirstOrDefault(b => b.Posts.Exists(p => p.Name == "test"));

will be translated into

var query = db.Blogs.FirstOrDefault(b => b.Posts.Any(p => p.Name == "test"));

I've plugged in the new translator in the Optimize method because looked like the correct place to me.

Am I on the right track?

I saw that @SSkovboiSS PR were recently merged, so I can plug my translator before the AllAnyToContainsResultOperatorExpressionRewriter to avoid client side eval of the query.

[ilmax]

@smitpatel I'm not sure about the array part because it's a static method and not an instance one, so just to double check, are you suggesting to translate this query:

var query = db.Blogs.FirstOrDefault(b => Array.Exists(b.Posts, p => p.Name == "test"));

I'm asking because it feels a bit strange as a query to me and, as a EF user, I'm not expecting a query like the one above to be translated to a store query.

Now I've completed the implementation following the aforementioned approach, I'll now integratethis PR and check if it works as expected

[smitpatel]

@ilmax - Yes that method call. I wasn't aware it was static method.
@ajcvickers @divega @anpete - Do we want to translate Array.Exists in query (like shown in post above this) to server?

[ilmax]

@smitpatel Ok, will wait feedback on the array part.

On my end EF doesn't generate the exists query with Any, e.g. the following query (from NorthwindModel)

AssertSingleResult<Customer>(
    cs => cs.Where(c => c.CustomerID == "ALFKI" && c.Orders.Any(o => o.ShipCity == "Milan")));

client eval and this is the sql produced (Comes from QueryBaseline.cs):

@"SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
FROM [Customers] AS [c]
WHERE [c].[CustomerID] = N'ALFKI'",
//
@"@_outer_CustomerID='ALFKI' (Size = 5)

SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE @_outer_CustomerID = [o].[CustomerID]");

Am I missing something obvious?

[smitpatel]

@ilmax - Perhaps ShipCity is not mapped in the model. Check NorthwindContext.cs file to see which are the properties ignored from model.

[smitpatel]

@ilmax - In EF Core navigation property cannot be of Array type so Array.Exists won't be server correlated. We can exclude translating it to server.

cc: @roji - As Array could map in PostgreSQL as scalar, you could potentially add some translation of Array.Exists on server (similar to Array.Length).

[roji]

@smitpatel opened https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/356 to track adding Array.Exists() for server evaluation

[ilmax]

@smitpatel Ok, thanks for the info.

[divega]

@smitpatel @ilmax direction seems good.

FWIW, there is that other space of scenarios in which Any / Exists can be applied to a client collection with a condition that is store correlated. We currently don't translate those but there is a subset that could be rewritten to Enumerable.Contains.

[ilmax]

@divega ok, great! I had to made a small additional change to the approach discussed above in order to make it work so I'm trying to get feedback on the new one.

After solving the client eval of the any (ShipCity was not mapped indeed 😢, thank you @smitpatel) and after realizing the first version of the rewriter, EF was still executing two queries, so I checked the differences between the Any query and the Exists one and (after a long debugging session 😄) I found that rewriting a MethodCallExpression into another MethodCallExpression wasn't enough and I had to return a new SubQueryExpression parsed from the rewritten MethodCallExpression.

To create a new SubQueryExpression I need an instance of the IQueryModelGenerator which wasn't available in the QueryOptimizer so I had to inject it (we can avoid changing the constructor moving the rewriter construction and call in other places e.g. EntityQueryModelVisitor).

Does what I've explained sounds reasonable to you guys? If this is reasonable, I'll add some few more test and then create a PR.

[smitpatel]

'(from Blog b in DbSet<Blog>
where bool [b].Posts.Exists((Post p) => p.Name == "test")
select [b]).FirstOrDefault()'

'(from Blog b in DbSet<Blog>
where
  (from Post p in [b].Posts
  where [p].Name == "test"
  select [p]).Any()
select [b]).FirstOrDefault()'

Above are query models for Exists & Any queries. Essentially we have to convert former to the latter. I understood where you hit issue since Exist is just a method call, if you convert to another method call we need relinq parsing to generate query model. So let's just create query model straight away.
In the methodCallExpression (lets call it method),
method.Object becomes FromExpression for MainFromClause.
Generate QSRE for mainFromClause.
put QSRE into selectclause & generate a query model.
method.Arguments[0] would be a lambdaExpression, replace parameter with the QSRE and put the lambda inside whereclause. Add whereclause to QM.BodyClause.
Add AnyResultOperator to QM and wrap it inside SubQueryExpression to return.
This is the best i could find in current codebase on how to generate a QM out of just simple expression.
https://github.com/aspnet/EntityFrameworkCore/blob/73d7db06e5defa2bb62e39d2a181158c5eae7c42/src/EFCore/Query/Internal/QueryOptimizer.cs#L426-L437

Let me know if you have issues. You can also submit a PR with non-working code to ask any specific questions around the code. It will allow us to inspect the code you have written and suggest what is missing.

[ilmax]

@smitpatel thank you for the info, I got it working with a similar approach, I was just unsure about having relinq to re-parse the newly created expression.

I'm now running all the test to check everything works as expected. this is the code of the rewriter:

protected override Expression VisitMethodCall(MethodCallExpression methodCallExpression)
{
    var newMethodCallExpression = (MethodCallExpression)base.VisitMethodCall(methodCallExpression);

    if (newMethodCallExpression.Arguments.Count == 1 &&
        newMethodCallExpression.Method.Name == nameof(List<int>.Exists) &&
        newMethodCallExpression.Method.DeclaringType.IsGenericType &&
        newMethodCallExpression.Method.DeclaringType.GetGenericTypeDefinition() == typeof(List<>))
    {
        var parameters = newMethodCallExpression.Method.DeclaringType.GetGenericArguments();
        if (parameters.Length == 1 && newMethodCallExpression.Arguments[0] is LambdaExpression actualLambdaExpression)
        {
            var type = newMethodCallExpression.Method.DeclaringType.GetGenericArguments()[0];
            var enumerableAny = typeof(Enumerable).GetTypeInfo()
                .GetDeclaredMethods(nameof(Enumerable.Any))
                .Single(m => m.GetParameters().Length == 2);

            var funcType = typeof(Func<,>).MakeGenericType(new[] { type, typeof(bool) });
            var newLambdaExpression = Expression.Lambda(funcType, actualLambdaExpression.Body, actualLambdaExpression.Parameters);

            var newExpression = Expression.Call(enumerableAny.MakeGenericMethod(type),
                newMethodCallExpression.Object,
                newLambdaExpression);

            var subQueryModel = _queryModelGenerator.ParseQuery(newExpression);

            return new SubQueryExpression(subQueryModel);
        }
    }

    return newMethodCallExpression;
}

Will create a PR after adding few more test cases , maybe to ensure also !Exists is correctly rewitten into !Any/All

[smitpatel]

@ilmax - We would want to avoid calling ParseQuery again since, QM is easy to construct and more performant.

[ilmax]

Ok, Will change the code to the suggested approach, other than this does the code looks reasonable to you?

[ilmax]

@smitpatel I'm having a hard time try to figure out how to replace the parameter in the LambdaExpresison with the QSRE.
I've made it to work rewriting the body of the lamda expression, but the approach seems a bit fragile to me.

Here you can find the code.

var mainFromClause = new MainFromClause(
    "<generated>_",
    type,
    newMethodCallExpression.Object);

var qsre = new QuerySourceReferenceExpression(mainFromClause);
var queryModel = new QueryModel(
    mainFromClause,
    new SelectClause(qsre));

mainFromClause.ItemName = queryModel.GetNewName(mainFromClause.ItemName);

// this is too fragile
var property = (MemberExpression)((BinaryExpression)actualLambdaExpression.Body).Left;
var expr = Expression.MakeBinary(actualLambdaExpression.Body.NodeType, Expression.MakeMemberAccess(qsre, property.Member), ((BinaryExpression)actualLambdaExpression.Body).Right);

queryModel.BodyClauses.Add(new WhereClause(expr));
queryModel.ResultOperators.Add(new AnyResultOperator());
queryModel.ResultTypeOverride = typeof(bool);

return new SubQueryExpression(queryModel);

Other than this, all test passes and the following query:

cs => cs.Where(c => c.CustomerID == "ALFKI" && c.Orders.Exists(o => o.OrderDate == new DateTime(2008, 10, 24)))

is correctly translated into the following Sql

@"SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
FROM [Customers] AS [c]
WHERE ([c].[CustomerID] = N'ALFKI') AND EXISTS (
    SELECT 1
    FROM [Orders] AS [o]
    WHERE ([o].[OrderDate] = '2008-10-24T00:00:00.000') AND ([c].[CustomerID] = [o].[CustomerID]))"

Any suggestion is highly appreciated 😄

[smitpatel]

This is how you would replace one expression with other lambda body 😉
https://github.com/aspnet/EntityFrameworkCore/blob/8f71f93f0e35d55c91a4807015e2b64f7de545e4/src/EFCore/Query/ExpressionVisitors/Internal/ModelExpressionApplyingExpressionVisitor.cs#L143-L148