GroupJoin throws when applied on an anonymous return type output of another Linq

[ankitmatrix08]

Models:

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

public partial interface IUserSiteAccess
	{
                long Id { get; }
		bool IsActive { get; }
		long? UserId { get; }
                long CreatedById { get; }
	}
public partial class EUser : IUser
	{ 
		public EUser() 
		{
		}

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

                long Id { get; set; }
		bool IsActive { get; set; }
		long? SiteConfigId { get; set;}
		long? UserId { 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<EUse> Users { get; set; }
   }

Working Sample (without anonymous type in the first query):

var userSiteIds1 = from u in context.UserSiteAccesses
                              select u.UserId;
                          
var finalQuery1 = (from users in context.Users
                              join userSites in userSiteIds1 on users.Id equals userSites into otherUserSitesRS
                              from otherUserSites in otherUserSitesRS.DefaultIfEmpty()
                              where otherUserSites == null
                              select users);

**SQL Produced:**
SELECT [u].[Id], [u].[CreatedById], [u].[IsActive]
FROM [Users] AS [u]
LEFT JOIN [UserSiteAccesses] AS [u0] ON [u].[Id] = [u0].[UserId]
WHERE [u0].[UserId] IS NULL

Failing Case:

var userSiteIds = from u in context.UserSiteAccesses
                     select new
                     {
                         u.UserId
                     };

var finalQuery = (from users in context.Users
                       join userSites in userSiteIds on users.Id equals userSites.UserId into otherUserSitesRS
                       from otherUserSites in otherUserSitesRS.DefaultIfEmpty()
                       where otherUserSites == null
                       select users);

Exception & Stack Trace:

The LINQ expression 'DbSet<EUser>()
    .SelectMany(
        collectionSelector: e => DbSet<EUserSiteAccess>()
            .Where(e0 => (long?)e.Id == e0.UserId)
            .DefaultIfEmpty()
            .Select(e0 => new { UserId = e0.UserId }), 
        resultSelector: (e, c) => new TransparentIdentifier<EUser, <>f__AnonymousType0<long?>>(
            Outer = e, 
            Inner = c
        ))
    .Where(ti => ti.Inner == null)' 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 above failing query works fine in EF6 and produces the below SQL:

SELECT [Extent1].[Id], [Extent1].[CreatedById], [Extent1].[IsActive]
FROM [Users] AS [Extent1]
LEFT OUTER JOIN [UserSiteAccesses] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]
WHERE [Extent1].[UserId] IS NULL

Include provider and version information

EF Core version: 7.0.0-preview.4 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Win 10 IDE: Visual Studio Professional 2022 (64-bit) Version 17.2.4

[ankitmatrix08]

@smitpatel @ajcvickers any luck on this?

[ajcvickers]

@ankitmatrix08 This is a pattern we may consider in the future.

[bricelam]

I hit this. We had a query to a very wide table filtering out unnecessary columns via .Select(x => new Entity { Id = x.Id, NeededProperty1 = x.NeededProperty1, /*etc.*/ }) then tried to compose GroupJoin..DefaultIfEmpty..SelectMany on top of it and it failed.

It also fails for just Join.