Efcore: 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:-
``` //SQL View
public DbSet CustomerViews { get; set; }
if (Database.IsInMemory())
{
//In memory test query type mappings
modelBuilder.Entity().HasNoKey().ToQuery(Build_Customers_Sql_View_InMemory());
}
private Expression>> Build_Customers_Sql_View_InMemory()
{
Expression>> 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
.GroupJoin(
outer: DbSet,
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.CreateQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_01.<Execute>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.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetEnumerator()
at Microsoft.EntityFrameworkCore.Internal.InternalDbSet1.System.Collections.Generic.IEnumerable<TEntity>.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 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
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 https://github.com/dotnet/efcore/issues/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.