Group By subquery when applied on another Query throws InvalidOperation Exception

[ankitmatrix08]

Not sure, if the same issue is being tracked with some other Id, but when the below combination of query is executed, the EFCore throws InvalidOperationException as listed below:

Query:

var grpQuery = (from u in context.Users
                            join usrSites in context.UserSiteAccesses
                            on (long?)u.Id
                            equals usrSites.UserId                            
                            group new { u, usrSites } by new { u.Id } into usrGrpingRS
                            select new { usr = usrGrpingRS.FirstOrDefault() }).Distinct();

var anotherQuery = from x in grpQuery
                               join y in context.UserEmailAddresses on x.usr.usrSites.UserId equals y.UserId
                               select x;

The output of first LINQ (grpQuery):

SELECT [t0].[Id], [t0].[CreatedById], [t0].[UserId], [t0].[c]
FROM (
    SELECT DISTINCT [u].[Id]
    FROM [Users] AS [u]
    INNER JOIN [UserSiteAccesses] AS [u0] ON [u].[Id] = [u0].[UserId]
    GROUP BY [u].[Id]
) AS [t]
LEFT JOIN (
    SELECT [t1].[Id], [t1].[CreatedById], [t1].[UserId], [t1].[c]
    FROM (
        SELECT [u1].[Id], [u1].[CreatedById], [u2].[UserId], 1 AS [c], ROW_NUMBER() OVER(PARTITION BY [u1].[Id] ORDER BY [u1].[Id], [u2].[Id]) AS [row]
        FROM [Users] AS [u1]
        INNER JOIN [UserSiteAccesses] AS [u2] ON [u1].[Id] = [u2].[UserId]
    ) AS [t1]
    WHERE [t1].[row] <= 1
) AS [t0] ON [t].[Id] = [t0].[Id]

Exception & Stack Trace: Occurs in the Second LINQ

The LINQ expression 'DbSet<EUser>()
    .Join(
        inner: DbSet<EUserSiteAccess>(), 
        outerKeySelector: e => (long?)e.Id, 
        innerKeySelector: e0 => e0.UserId, 
        resultSelector: (e, e0) => new TransparentIdentifier<EUser, EUserSiteAccess>(
            Outer = e, 
            Inner = e0
        ))
    .GroupBy(ti => new { Id = ti.Outer.Id })
    .Select(g => new { usr = g
        .AsQueryable()
        .Select(e1 => new { 
            u = IncludeExpression(
                IncludeExpression(
                    e1.Outer, 
                    EF.Property<UserApprovalStatusValues>(e1.Outer, "ApprovalStatus"), ApprovalStatus)
                , 
                EF.Property<Document>(e1.Outer, "ProfilePicture"), ProfilePicture)
            , 
            usrSites = e1.Inner
         })
        .FirstOrDefault() })
    .Distinct()
    .Join(
        inner: DbSet<EUserEmailAddress>(), 
        outerKeySelector: e3 => e3.usr.usrSites.UserId, 
        innerKeySelector: e4 => e4.UserId, 
        resultSelector: (e3, e4) => new TransparentIdentifier<<>f__AnonymousType2<<>f__AnonymousType0<EUser, EUserSiteAccess>>, EUserEmailAddress>(
            Outer = e3, 
            Inner = e4
        ))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.


   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   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.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

NOTE: The same query works fine and produces correct SQL in EF6:

First LINQ Query:

SELECT 
    [Distinct2].[C1] AS [C1], 
    [Distinct2].[C2] AS [C2], 
    [Distinct2].[Id] AS [Id], 
    [Distinct2].[CreatedById] AS [CreatedById], 
    [Distinct2].[Id1] AS [Id1], 
    [Distinct2].[CreatedById1] AS [CreatedById1], 
    [Distinct2].[UserId] AS [UserId], 
    FROM ( SELECT DISTINCT 
        1 AS [C1], 
        [Limit1].[Id] AS [Id], 
        [Limit1].[CreatedById] AS [CreatedById], 
        [Limit1].[Id1] AS [Id1], 
        [Limit1].[CreatedById1] AS [CreatedById1], 
        [Limit1].[UserId] AS [UserId], 
        [Limit1].[C1] AS [C2]
        FROM   (SELECT DISTINCT 
            [Extent1].[UserId] AS [UserId]
            FROM [dbo].[UserSiteAccesses] AS [Extent1] ) AS [Distinct1]
        OUTER APPLY  (SELECT TOP (1) 
            [Extent2].[Id] AS [Id], 
            [Extent2].[CreatedById] AS [CreatedById], 
            [Extent3].[Id] AS [Id1], 
            [Extent3].[CreatedById] AS [CreatedById1], 
            [Extent3].[UserId] AS [UserId], 
            1 AS [C1]
            FROM  [dbo].[Users] AS [Extent2]
            INNER JOIN [dbo].[UserSiteAccesses] AS [Extent3] ON [Extent2].[Id] = [Extent3].[UserId]
            WHERE [Distinct1].[UserId] = [Extent2].[Id] ) AS [Limit1]
    )  AS [Distinct2]

Second LINQ Query:

SELECT 
    [Distinct2].[C1] AS [C1], 
    [Distinct2].[C2] AS [C2], 
    [Distinct2].[Id] AS [Id], 
    [Distinct2].[CreatedById] AS [CreatedById], 
    [Distinct2].[Id1] AS [Id1], 
    [Distinct2].[CreatedById1] AS [CreatedById1], 
    [Distinct2].[UserId] AS [UserId], 
    FROM ( SELECT DISTINCT 
        1 AS [C1], 
        [Limit1].[Id] AS [Id], 
        [Limit1].[CreatedById] AS [CreatedById], 
        [Limit1].[Id1] AS [Id1], 
        [Limit1].[CreatedById1] AS [CreatedById1], 
        [Limit1].[UserId] AS [UserId], 
        [Limit1].[C1] AS [C2]
        FROM   (SELECT DISTINCT 
            [Extent1].[UserId] AS [UserId]
            FROM [dbo].[UserSiteAccesses] AS [Extent1] ) AS [Distinct1]
        OUTER APPLY  (SELECT TOP (1) 
            [Extent2].[Id] AS [Id], 
            [Extent2].[CreatedById] AS [CreatedById], 
            [Extent3].[Id] AS [Id1], 
            [Extent3].[CreatedById] AS [CreatedById1], 
            [Extent3].[UserId] AS [UserId], 
            1 AS [C1]
            FROM  [dbo].[Users] AS [Extent2]
            INNER JOIN [dbo].[UserSiteAccesses] AS [Extent3] ON [Extent2].[Id] = [Extent3].[UserId]
            WHERE [Distinct1].[UserId] = [Extent2].[Id] ) AS [Limit1] ) AS [Distinct2]
    INNER JOIN [dbo].[UserEmailAddresses] AS [Extent4] ON [Distinct2].[UserId] = [Extent4].[UserId]

Models:

public partial interface IUser
	{
                long Id { get; }
		long CreatedById { get; }
	}

public partial interface IUserSiteAccess
	{
                long Id { get; }
		long? UserId { get; }
                long CreatedById { get; }
	}
public partial interface IUserEmailAddress
	{
                long Id { get; }
                long? UserId { get; }
                string EmailAddress { get; }
		long CreatedById { get; }
	}
public partial class EUser : IUser
	{ 
		public EUser() 
		{
		}

                long Id { get; set; }
		long CreatedById { get; set; }
	}
public partial class EUserSiteAccess : IUserSiteAccess
	{ 
		public EUserSiteAccess() 
		{
		}

                long Id { get; set; }
		long? UserId { get; set;}
                long CreatedById { get; set; }
	}

public partial class EUserEmailAddress : IUserEmailAddress
	{ 
		public EUserEmailAddress() 
		{
		}

                long Id { get; set; }
		long? UserId { get; set;}
                string EmailAddress { get; set; }
                long CreatedById { get; set; }
	}

public partial class BananaContext : AbstractDbContext
    {
        public BananaContext()
        {
            ChangeTracker.AutoDetectChangesEnabled = false;
        }

        public BananaContext(DbContextOptions<BananaContext> options)
        {
        }
        public virtual DbSet<EUserSiteAccess> UserSiteAccesses { get; set; }
        public virtual DbSet<EUser> Users { get; set; }
        public virtual DbSet<EUserEmailAddress> UserEmailAddresses { get; set; }
   }

EF Core version: 7.0.0-preview.4.22229.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Win 10 Pro
IDE: Visual Studio 2022 v17.0.4

[ankitmatrix08]

@ajcvickers Items under this tag ef6-parity will be fixed and released with 7.0?

[ankitmatrix08]

@ajcvickers @AndriySvyryd
Could please answer on when this ef6-parity tagged issues will be fixed and released.
We were under the impression that queries which are running fine in EF6 will be mostly fixed in EFCore v7.

[ajcvickers]

@ankitmatrix08 We are making a best effort to cover all EF6 issues, but in some cases this may not be possible. @smitpatel Can likely provide some more specific information on this query.

[smitpatel]

This is in backlog and won't be fixed in 7.0

[ankitmatrix08]

@ajcvickers @smitpatel
This isn’t helpful, we are not able to plan our upgrade due to such uncertainties.

The first priority should have been given to narrowing the gap between EF6 and EFCore, otherwise how could the existing applications be migrated?

[smitpatel]

Please refer to https://docs.microsoft.com/en-us/ef/core/what-is-new/release-planning
This issue doesn't fall into high priority bucket.

[ankitmatrix08]

@smitpatel Shouldn’t all server side eval EF6 queries be on the priority list?

For applications to be successfully and with minimal efforts be migrated to EFCore, is it too much to ask?

Or kindly answer this - by which major release of EFCore should we expect all such gap be fixed so that we will only think of using EFCore from that version onward?

[smitpatel]

Customers who are migrating EF6 apps to EF Core, is a subset of all the customers of EF Core. There are many people who starts with EF Core, need new features, different providers. Even there exists good amount of customers who migrated their app from EF6 to EF Core. We acknowledge that some customers are not yet able to migrate. We have to provide value to all our customers not just a subset of it.

[ankitmatrix08]

Thank you @smitpatel your answer sums it up!
We will take a decision precisely on the point you mentioned.

[ajcvickers]

@ankitmatrix08

By which major release of EFCore should we expect all such gap be fixed so that we will only think of using EFCore from that version onward

I don't think there will ever be a release of EF Core that means this expectation. EF Core is a fundamentally different architecture and codebase than EF6, and as such there are always likely to be differences. In particular, when EF6 created very poor translations resulting in a pit-of-failure for anyone using them, it is sometimes better to explicitly not do this in EF Core, but instead block translation such that the LINQ query can be rewritten in a way that can be translated well. This is an ongoing process. where we expect to do better translations each release, and provide better messaging when we can't translate, while not providing pit-of-failure translations.