EF Core 3.1 Urgent Issue:- ToQuery Method with Left Outer Join Causes an Error although it was working Properly with Previous Version

[ahmedtolba1984]

In EF Core 2.1, I was able to test SQL View with InMemory Provider using a custom ToQuery() method, like below:-

        public DbSet<CustomerView> CustomerViews { get; set; }
        if (Database.IsInMemory())
            {
                //In memory test query type mappings
                modelBuilder.Entity<CustomerView>().HasNoKey().ToQuery(Build_Customers_Sql_View_InMemory());
            }
        private Expression<Func<IQueryable<CustomerView>>> Build_Customers_Sql_View_InMemory()
        {
            Expression<Func<IQueryable<CustomerView>>> query = () =>
                from customer in Customers
                join customerMembership in CustomerMemberships on customer.Id equals customerMembership.CustomerId into
                    nullableCustomerMemberships
                from customerMembership in nullableCustomerMemberships.DefaultIfEmpty()
                select new CustomerView
                {
                    Id = customer.Id,
                    Name = customer.Name,
                    CustomerMembershipId = customerMembership != null? customerMembership.Id : default(int?),
                    CustomerMembershipName = customerMembership != null ? customerMembership.Name: ""
                };
            return query;
        }

but after i upgrade to EF Core 3.1, I got an exception "System.InvalidOperationException : Processing of the LINQ expression" although my ToQuery method code works properly outside the method.
Kindly, check the stack trace and working sample that produce the issue

System.InvalidOperationException : Processing of the LINQ expression 'DbSet<Customer>
    .GroupJoin(
        outer: DbSet<CustomerMembership>, 
        inner: customer => customer.Id, 
        outerKeySelector: customerMembership => customerMembership.CustomerId, 
        innerKeySelector: (customer, nullableCustomerMemberships) => new { 
            customer = customer, 
            nullableCustomerMemberships = nullableCustomerMemberships
         })' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitConstant(ConstantExpression constantExpression)
   at System.Linq.Expressions.ConstantExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   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 Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.System.Collections.Generic.IEnumerable<TEntity>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EFInMemoryProviderSQLViewIssue.CustomerTests.Create_New_Customer()
Got Exceptions? Include both the message and the stack trace

-->



### Further technical details

EF Core version: 3.1.1
Database provider: (Microsoft.EntityFrameworkCore.InMemory)
Target framework: (.NET Core 3.1)

[EFInMemoryProviderSQLViewIssue.zip](https://github.com/dotnet/efcore/files/4112526/EFInMemoryProviderSQLViewIssue.zip)
IDE: (Visual Studio 2019 16.4.2)

[ahmedtolba1984]

@ajcvickers @smitpatel @AndriySvyryd @roji @bricelam

[ahmedtolba1984]

EFInMemoryProviderSQLViewIssue.zip

[ahmedtolba1984]

Dears, any help ?

[ErikEJ]

@ahmedtolba1984 If you need urgent help, there are paid support options

[ajcvickers]

@ahmedtolba1984 I can reproduce what you are seeing, but I'm not sure why it is failing.

@maumar @roji Could you guys take a look? We might need @smitpatel to take a look when he is back.

[maumar]

GroupJoin method translation is not supported. Problem is that for regular query we run pre-processing step that flattens SelectMany-GroupJoin-DefaultIfEmpty into LeftJoin, which we can translate. When using view however, at the time of flattening the query doesn't have it's Defining query extracted, so the SM-GJ-DIE pattern is not available for flattening. We only peek into defining query during nav rewrite

[maumar]

Fix is to run missing pre-processing steps after the defining query has been extracted, but before the extracted query is put thru nav rewrite itself.

[maumar]

related to #17270

[ahmedtolba1984]

@maumar Thanks for feedback but when this problem will be solved or Is there any workaround for such issue?

[maumar]

@ahmedtolba1984 no good workaround, at least if you want to use ToQuery. If the issue is very urgent for you, the best approach is probably to apply the fix yourself:
1.) fork the current master
2.) in the code for NavigationExpandingExpressionVisitor, in line 120 (method is VisitConstant, just before processedDefiningQueryBody = Visit(processedDefiningQueryBody);

add the following line of code:

processedDefiningQueryBody = new GroupJoinFlatteningExpressionVisitor().Visit(processedDefiningQueryBody);

[ahmedtolba1984]

@maumar
Thanks, but i have a question, Does SQLlite support SQL View?
I think if so, i can change my InMemory Provider with SQL Lite

[maumar]

@ahmedtolba1984 unfortunately this issue affects all providers

[maumar]

@ahmedtolba1984 actually, workaround for your specific case could be to write a query already it its LeftJoin form:

            Expression<Func<IQueryable<CustomerView>>> query = () =>
                QueryableExtensions.LeftJoin(Customers, CustomerMemberships, c => c.Id, cm => cm.CustomerId, (customer, customerMembership) => new CustomerView
                {
                    Id = customer.Id,
                    Name = customer.Name,
                    CustomerMembershipId = customerMembership != null ? customerMembership.Id : default(int?),
                    CustomerMembershipName = customerMembership != null ? customerMembership.Name : ""
                });

[smitpatel]

ToQuery method may be removed in #17270.

[prashantaggarwal1990]

I am facing an issue after converting the project from .Net core 2.1 to 3.1. I have following linq code:

var query = _context.FileDescriptor.Where(x => x.ClientId == clientId);
var finalQuery = from entity in testquery
join nextVersion in testquery on entity.Id equals nextVersion.PreviousRevisionId into nextVersions
where !nextVersions.Any() && !entity.IsDeleted && entity.FolderId == folderId
select entity
return await finalQuery.FirstOrDefaultAsync(d => !d.IsDeleted && d.FolderId == folderId && d.FileName == fileName, token);

Getting the below error while executing:
System.InvalidOperationException: Processing of the LINQ expression 'DbSet
.Where(x => x.ClientId == __clientId_0)
.GroupJoin(
outer: DbSet
.Where(x => x.ClientId == __clientId_0),
inner: entity => (Nullable)entity.Id,
outerKeySelector: nextVersion => nextVersion.PreviousRevisionId,
innerKeySelector: (entity, nextVersions) => new {
entity = entity,
nextVersions = nextVersions
})' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, LambdaExpression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable1 source, Expression`1 predicate, CancellationToken cancellationToken)
Please help..

[smitpatel]

@prashantaggarwal1990 - As the milestone indicates this issue is fixed in 5.0 release and it is not supposed to work in 3.1.

[prashantaggarwal1990]

Any workaround for this?

…

________________________________ From: Smit Patel ***@***.***> Sent: Thursday, April 29, 2021 8:18:28 PM To: dotnet/efcore ***@***.***> Cc: prashantaggarwal1990 ***@***.***>; Mention ***@***.***> Subject: Re: [dotnet/efcore] EF Core 3.1 Urgent Issue:- ToQuery Method with Left Outer Join Causes an Error although it was working Properly with Previous Version (#19708) @prashantaggarwal1990<https://github.com/prashantaggarwal1990> - As the milestone indicates this issue is fixed in 5.0 release and it is not supposed to work in 3.1. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#19708 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AD2W3EORCNENAJPL6TE533DTLFWTZANCNFSM4KLSPOZA>.

[smitpatel]

There are few work-around mentioned in the comments above. Apart from that either upgrade to 5.0 or avoid using ToQuery.