Efcore: preview8 join giving not implemented

[dotnetshadow]

When upgrading from preview5 to preview8 the following query throws
"The method or operation is not implemented."

The following query taken from:
https://github.com/KalikoCMS/KalikoCMS.NextGen/blob/netcore3.0-experimental/KalikoCMS.Legacy/Data/Repositories/LegacyContentRepository.cs#L35

var nodes = (from content in _context.Pages
                         join contentLanguage in _context.PageInstances.Where(x => x.DeletedDate == null && (x.Status == ContentStatus.Published || (x.Status == ContentStatus.WorkingCopy && x.CurrentVersion == 0)))
                on content.PageId equals contentLanguage.PageId into grouped
                orderby content.TreeLevel
                select new ContentNode
                {
                    ContentId = content.PageId,
                    SortOrder = content.SortOrder,
                    TreeLevel = content.TreeLevel,
                    ParentId = content.ParentId,
                    ContentTypeId = ToGuid(content.PageTypeId),
                    ContentProviderId = PageContentProvider.UniqueId,
                    Languages = (from contentLanguage in grouped
                        select new LanguageNode
                        {
                            ContentLanguageId = contentLanguage.PageInstanceId,
                            ContentName = contentLanguage.PageName,
                            ChildSortOrder = contentLanguage.ChildSortOrder,
                            StopPublish = contentLanguage.StopPublish,
                            UpdateDate = contentLanguage.UpdateDate,
                            ChildSortDirection = contentLanguage.ChildSortDirection,
                            Status = contentLanguage.Status,
                            Author = contentLanguage.Author,
                            StartPublish = contentLanguage.StartPublish,
                            CreatedDate = contentLanguage.CreatedDate,
                            VisibleInSitemap = contentLanguage.VisibleInSitemap,
                            CurrentVersion = contentLanguage.CurrentVersion,
                            UrlSegment = contentLanguage.PageUrl,
                            VisibleInMenu = contentLanguage.VisibleInMenu,
                            LanguageId = contentLanguage.LanguageId
                        }).ToList()
                }).ToList();

It's saying that the GroupJoin can't be translated?
If I add .AsEnumerable to L35 then it works, because it's evaluating locally

I'm guessing the query needs to be rewritten, but not sure how it should look?

Exception message:

"The method or operation is not implemented."

Stack trace:
"   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupJoin(ShapedQueryExpression outer, ShapedQueryExpression inner, LambdaExpression outerKeySelector, LambdaExpression innerKeySelector, LambdaExpression resultSelector)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at KalikoCMS.Legacy.Data.Repositories.LegacyContentRepository.GetContentNodes() in D:\\Programming\\Projects\\Libraries\\KalikoCMS.NextGen\\KalikoCMS.NextGen\\KalikoCMS.Legacy\\Data\\Repositories\\LegacyContentRepository.cs:line 35\r\n   at KalikoCMS.Services.Content.ContentIndexService.Initialize() in D:\\Programming\\Projects\\Libraries\\KalikoCMS.NextGen\\KalikoCMS.NextGen\\KalikoCMS\\Services\\Content\\ContentIndexService.cs:line 30\r\n   at KalikoCMS.Services.Initialization.InitializationService.Initialize() in D:\\Programming\\Projects\\Libraries\\KalikoCMS.NextGen\\KalikoCMS.NextGen\\KalikoCMS\\Services\\Initialization\\InitializationService.cs:line 45"

Steps to reproduce

1. Download the project
   https://github.com/KalikoCMS/KalikoCMS.NextGen/tree/netcore3.0-experimental
   Change the EF core 3.0 reference from preview 5 to 8 in KalioCMS.Legacy and KalikoCMS.Data.SqlServer projects

Further technical details

EF Core version: 3.0 preview8
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system:
IDE: Visual Studio 2019 16.2 preview 2

[smitpatel]

Client side group join. This query cannot be translated to SQL directly. Adding AsEnumerable should be good way since it does not affect how much data is transferred from server.

[dotnetshadow]

Thanks for confirming, much appreciated.

Just for my understanding, this seems like a join that could be handled in the server.

1. Why didn't this translate on the server?
2. What causes it to think it should evaluate client side?

[smitpatel]

There is no SQL equivalent of GroupJoin.

[dotnetshadow]

Cheers, thanks for clearing it up, makes sense.

I was thinking perhaps i could of used a navigational property instead to get around the issue.

[xfoxfu]

I wonder how would I implement a left join in EF Core 3.0? As GroupJoin requires client-side evaluation and is disabled in 3.0, and Join is inner join, while the new LeftJoin is internal, currently I cannot figure out any approaches to perform a left join.

[smitpatel]

@coderfox - GroupJoin-DefaultIfEmpty-SelectMany is LINQ's way of writing Left join
e.g.
C# from c in cs join o in os on c.CustomerID equals o.CustomerID into grouping from o in grouping.DefaultIfEmpty() select { c, o }
This generates Customers LEFT JOIN Orders in database.

[dotnetshadow]

@smitpatel and that would translate obviously to client side evaluation? So you can't actually ever have a server side left join query?

[smitpatel]

@dotnetshadow - that would translate to

Select c.*, o.*
FROM Customers AS c
LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID

If you write a proper equivalent of LEFT JOIN on client side, we generate a left join. Not all variations of GroupJoin-DefaultIfEmpty-SelectMany are left join. LINQ has a lot more range variables in the scope than what LEFT JOIN could allow. So if you do it incorrectly, it is client evaluation. You can have server side left join query if you write it correctly. We have 100s of test verifying that.

[smitpatel]

Duplicate of #19930