ArgumentException thrown When GroupJoin is applied on a DbSet as a first join and NOT when applied as a second or later

[ankitmatrix08]

1. Exception Case:
LINQ: Here, I have a query which is doing a GroupJoin on an DbSet and then adding other inner joins.
Repository.ConvertToBigIntTable -> This is an output of a call to a Table-Valued function which returns an IQueryable (T is a Keyless entity having a long Id field)

private IQueryable<IDocumentTypePermission> GetDocumentTypePermissionsTest<T>(IQueryable<T> entitySet) where T : class, IDocumentType
        {
            var userGroupIds = RunComponent(GetActiveUserGroupsOfCurrentUser.New(new GetActiveUserGroupsOfCurrentUserParam() { CurrentUserId = CurrentUserId })).UserGroupIds;
            return (from documentType in entitySet
                    join userGroupId in Repository.ConvertToBigIntTable(userGroupIds, "userGroupId")
                    on documentType.Id equals userGroupId.Id into UserGroupIds
                    from userGroupId in UserGroupIds.DefaultIfEmpty()
                    join documentTypePermission in Repository.DocumentTypePermissions
                    on documentType.Id equals documentTypePermission.DocumentTypeId
                    join userSelectionParam in Repository.UserSelectionParams
                    on documentTypePermission.UserSelectionId equals userSelectionParam.Id

                    
                    where documentTypePermission.IsActive && ((userSelectionParam.UserGroupId != null && userGroupId.Id != null)
                                                              || (userSelectionParam.UserId != null && userSelectionParam.UserId == CurrentUserId))
                    select documentTypePermission);
        }

DbSet<EDocumentType>()
    .GroupJoin(
        inner: BananaContext.ConvertCSVToBigIntTable(
            List: <>c__DisplayClass958_0.List, 
            Delim: <>c__DisplayClass958_0.Delim), 
        outerKeySelector: documentType => documentType.Id, 
        innerKeySelector: userGroupId => userGroupId.Id, 
        resultSelector: (documentType, UserGroupIds) => new { 
            documentType = documentType, 
            UserGroupIds = UserGroupIds
         })
    .SelectMany(
        collectionSelector: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.UserGroupIds
            .DefaultIfEmpty(), 
        resultSelector: (<>h__TransparentIdentifier0, userGroupId) => new { 
            <>h__TransparentIdentifier0 = <>h__TransparentIdentifier0, 
            userGroupId = userGroupId
         })
    .Join(
        inner: DbSet<EDocumentTypePermission>(), 
        outerKeySelector: <>h__TransparentIdentifier1 => (long?)<>h__TransparentIdentifier1.<>h__TransparentIdentifier0.documentType.Id, 
        innerKeySelector: documentTypePermission => documentTypePermission.DocumentTypeId, 
        resultSelector: (<>h__TransparentIdentifier1, documentTypePermission) => new { 
            <>h__TransparentIdentifier1 = <>h__TransparentIdentifier1, 
            documentTypePermission = documentTypePermission
         })
    .Join(
        inner: DbSet<EUserSelectionParam>(), 
        outerKeySelector: <>h__TransparentIdentifier2 => <>h__TransparentIdentifier2.documentTypePermission.UserSelectionId, 
        innerKeySelector: userSelectionParam => (long?)userSelectionParam.Id, 
        resultSelector: (<>h__TransparentIdentifier2, userSelectionParam) => new { 
            <>h__TransparentIdentifier2 = <>h__TransparentIdentifier2, 
            userSelectionParam = userSelectionParam
         })
    .Where(<>h__TransparentIdentifier3 => <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.documentTypePermission.IsActive && <>h__TransparentIdentifier3.userSelectionParam.UserGroupId != null && (long?)<>h__TransparentIdentifier3.<>h__TransparentIdentifier2.<>h__TransparentIdentifier1.userGroupId.Id != null || <>h__TransparentIdentifier3.userSelectionParam.UserId != null && <>h__TransparentIdentifier3.userSelectionParam.UserId == (long?)AbstractDomainBehavior.CurrentUserId)
    .Select(<>h__TransparentIdentifier3 => <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.documentTypePermission)

Exception:

Expression of type 'System.Func`2[Lw.Domain.IDocumentType,System.Int64]' cannot be used for parameter of type 'System.Linq.Expressions.Expression`1[System.Func`2[Lw.Domain.EDocumentType,System.Int64]]' of method 'System.Linq.IQueryable`1[<>f__AnonymousType290`2[<>f__AnonymousType289`2[Lw.Domain.IDocumentType,System.Collections.Generic.IEnumerable`1[Lw.Sys.Repository.ITableValueFunctionResult]],Lw.Sys.Repository.ITableValueFunctionResult]] LeftJoin[EDocumentType,ITableValueFunctionResult,Int64,<>f__AnonymousType290`2](System.Linq.IQueryable`1[Lw.Domain.EDocumentType], System.Collections.Generic.IEnumerable`1[Lw.Sys.Repository.ITableValueFunctionResult], System.Linq.Expressions.Expression`1[System.Func`2[Lw.Domain.EDocumentType,System.Int64]], System.Linq.Expressions.Expression`1[System.Func`2[Lw.Sys.Repository.ITableValueFunctionResult,System.Int64]], System.Linq.Expressions.Expression`1[System.Func`3[Lw.Domain.EDocumentType,Lw.Sys.Repository.ITableValueFunctionResult,<>f__AnonymousType290`2[<>f__AnonymousType289`2[Lw.Domain.IDocumentType,System.Collections.Generic.IEnumerable`1[Lw.Sys.Repository.ITableValueFunctionResult]],Lw.Sys.Repository.ITableValueFunctionResult]]])' (Parameter 'arg2')

Stack Trace:

   at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
   at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0, Expression arg1, Expression arg2, Expression arg3, Expression arg4)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.TryFlattenGroupJoinSelectMany(MethodCallExpression methodCallExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.NormalizeQueryableMethod(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPreprocessor.NormalizeQueryableMethod(Expression expression)
   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.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 System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

NOTE: Interestingly trying to do a very similar operation on LinqPad7 seems to work:

2. Working Case
LINQ: Here, I have a query which is performing inner joins on few DbSets and then applying GroupJoin on one of the DbSet.
Repository.ConvertToBigIntTable -> This is an output of a call to a Table-Valued function which returns an IQueryable (T is a Keyless entity having a long Id field)

 private IQueryable<IDocumentTypePermission> GetDocumentTypePermissions<T>(IQueryable<T> entitySet) where T : class, IDocumentType
        {
            var userGroupIds = RunComponent(GetActiveUserGroupsOfCurrentUser.New(new GetActiveUserGroupsOfCurrentUserParam() { CurrentUserId = CurrentUserId })).UserGroupIds;
            return (from documentType in entitySet
                    join documentTypePermission in Repository.DocumentTypePermissions on documentType.Id equals documentTypePermission.DocumentTypeId
                    join userSelectionParam in Repository.UserSelectionParams on documentTypePermission.UserSelectionId equals userSelectionParam.Id
                    join userGroupId in Repository.ConvertToBigIntTable(userGroupIds, "userGroupId")
                    on userSelectionParam.UserGroupId equals userGroupId.Id into UserGroupIds
                    from userGroupId in UserGroupIds.DefaultIfEmpty()
                    where documentTypePermission.IsActive && ((userSelectionParam.UserGroupId != null && userGroupId.Id != null)
                                                              || (userSelectionParam.UserId != null && userSelectionParam.UserId == CurrentUserId))
                    select documentTypePermission);
        }

Generated Expression:

DbSet<EDocumentType>()
    .Join(
        inner: DbSet<EDocumentTypePermission>(), 
        outerKeySelector: documentType => (long?)documentType.Id, 
        innerKeySelector: documentTypePermission => documentTypePermission.DocumentTypeId, 
        resultSelector: (documentType, documentTypePermission) => new { 
            documentType = documentType, 
            documentTypePermission = documentTypePermission
         })
    .Join(
        inner: DbSet<EUserSelectionParam>(), 
        outerKeySelector: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.documentTypePermission.UserSelectionId, 
        innerKeySelector: userSelectionParam => (long?)userSelectionParam.Id, 
        resultSelector: (<>h__TransparentIdentifier0, userSelectionParam) => new { 
            <>h__TransparentIdentifier0 = <>h__TransparentIdentifier0, 
            userSelectionParam = userSelectionParam
         })
    .GroupJoin(
        inner: BananaContext.ConvertCSVToBigIntTable(
            List: <>c__DisplayClass958_0.List, 
            Delim: <>c__DisplayClass958_0.Delim), 
        outerKeySelector: <>h__TransparentIdentifier1 => <>h__TransparentIdentifier1.userSelectionParam.UserGroupId, 
        innerKeySelector: userGroupId => (long?)userGroupId.Id, 
        resultSelector: (<>h__TransparentIdentifier1, UserGroupIds) => new { 
            <>h__TransparentIdentifier1 = <>h__TransparentIdentifier1, 
            UserGroupIds = UserGroupIds
         })
    .SelectMany(
        collectionSelector: <>h__TransparentIdentifier2 => <>h__TransparentIdentifier2.UserGroupIds
            .DefaultIfEmpty(), 
        resultSelector: (<>h__TransparentIdentifier2, userGroupId) => new { 
            <>h__TransparentIdentifier2 = <>h__TransparentIdentifier2, 
            userGroupId = userGroupId
         })
    .Where(<>h__TransparentIdentifier3 => <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.<>h__TransparentIdentifier1.<>h__TransparentIdentifier0.documentTypePermission.IsActive && <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.<>h__TransparentIdentifier1.userSelectionParam.UserGroupId != null && (long?)<>h__TransparentIdentifier3.userGroupId.Id != null || <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.<>h__TransparentIdentifier1.userSelectionParam.UserId != null && <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.<>h__TransparentIdentifier1.userSelectionParam.UserId == (long?)AbstractDomainBehavior.CurrentUserId)
    .Select(<>h__TransparentIdentifier3 => <>h__TransparentIdentifier3.<>h__TransparentIdentifier2.<>h__TransparentIdentifier1.<>h__TransparentIdentifier0.documentTypePermission)

Generated SQL:

DECLARE @__List_1 nvarchar(4000) = N'1,5,6,7,8,14,15,44,46,62,63,64,67,69,73,85,88';
DECLARE @__Delim_2 nvarchar(1) = N',';
DECLARE @__CurrentUserId_3 bigint = CAST(1 AS bigint);

SELECT [d0].[Id], [d0].[Condition], [d0].[CreatedById], [d0].[CreatedTime], [d0].[DocumentTypeId], [d0].[IsActive], [d0].[IsOverridable], [d0].[IsReevaluate], [d0].[RowVersion], [d0].[UpdatedById], [d0].[UpdatedTime], [d0].[UserSelectionId], [d0].[AssignmentType], [d0].[ConditionFor], [d0].[CreationAllowed], [d0].[Permission]
FROM [DocumentTypes] AS [d]
INNER JOIN [DocumentTypePermissions] AS [d0] ON [d].[Id] = [d0].[DocumentTypeId]
INNER JOIN [UserSelectionParams] AS [u] ON [d0].[UserSelectionId] = [u].[Id]
LEFT JOIN [dbo].[ConvertCSVToBigIntTable](@__List_1, @__Delim_2) AS [c] ON [u].[UserGroupId] = [c].[Id]
WHERE ([d0].[IsActive] = CAST(1 AS bit)) AND (([u].[UserGroupId] IS NOT NULL AND [c].[Id] IS NOT NULL) OR ([u].[UserId] IS NOT NULL AND ([u].[UserId] = @__CurrentUserId_3)))

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

[ankitmatrix08]

One Update: Found an answer of the same issue on StackOverflow which works: replace GroupJoin with SelectMany

var query = 
    from documentType in entitySet
    from userGroupId in Repository.ConvertToBigIntTable(userGroupIds, "userGroupId")
        .Where(userGroupId => documentType.Id == userGroupId.Id)
        .DefaultIfEmpty()
    join documentTypePermission in Repository.DocumentTypePermissions
        on documentType.Id equals documentTypePermission.DocumentTypeId
    join userSelectionParam in Repository.UserSelectionParams
        on documentTypePermission.UserSelectionId equals userSelectionParam.Id

But this will require all of LINQs in my project to go through these syntactical changes which is really very risky and not recommened.

Essentially the question arises as to how the same kind of query works on LinqPad7?

[ankitmatrix08]

One more peculiar case: Instead of using generic object "entitySet" if I then use direct DbContext.DbSet reference, then all the errors go away and the Sql gets generated correctly:

 private IQueryable<IDocumentTypePermission> GetDocumentTypePermissionsTest<T>(IQueryable<T> entitySet) where T : class, IDocumentType
        {
            var userGroupIds = RunComponent(GetActiveUserGroupsOfCurrentUser.New(new GetActiveUserGroupsOfCurrentUserParam() { CurrentUserId = CurrentUserId })).UserGroupIds;
            return (from documentType in Repository.GetDbContext().DocumentTypes
                    join userGroupId in Repository.ConvertToBigIntTable(userGroupIds, "userGroupId")
                    on documentType.Id equals userGroupId.Id into UserGroupIds
                    from userGroupId in UserGroupIds.DefaultIfEmpty()
                    join documentTypePermission in Repository.DocumentTypePermissions
                    on documentType.Id equals documentTypePermission.DocumentTypeId
                    join userSelectionParam in Repository.UserSelectionParams
                    on documentTypePermission.UserSelectionId equals userSelectionParam.Id


                    where documentTypePermission.IsActive && ((userSelectionParam.UserGroupId != null && userGroupId.Id != null)
                                                              || (userSelectionParam.UserId != null && userSelectionParam.UserId == CurrentUserId))
                    select documentTypePermission);
        }

But this isn't the correct design for our architecture and I would like to know if I am missing something very obvious here?

[smitpatel]

Issue is the interface here. When you are passing entitySet in first case the type of T is an interface. So when you replace it with concrete type (using direct DbSet) in last query, it works since there is no type mismatch.

[ankitmatrix08]

Two queries then:

1. How is my "2. Working Case" is working, that is also an entitySet that of T type (an interface)

 private IQueryable<IDocumentTypePermission> GetDocumentTypePermissions<T>(IQueryable<T> entitySet) where T : class, IDocumentType
        {
            var userGroupIds = RunComponent(GetActiveUserGroupsOfCurrentUser.New(new GetActiveUserGroupsOfCurrentUserParam() { CurrentUserId = CurrentUserId })).UserGroupIds;
            return (from documentType in entitySet
                    join documentTypePermission in Repository.DocumentTypePermissions on documentType.Id equals documentTypePermission.DocumentTypeId
                    join userSelectionParam in Repository.UserSelectionParams on documentTypePermission.UserSelectionId equals userSelectionParam.Id
                    join userGroupId in Repository.ConvertToBigIntTable(userGroupIds, "userGroupId")
                    on userSelectionParam.UserGroupId equals userGroupId.Id into UserGroupIds
                    from userGroupId in UserGroupIds.DefaultIfEmpty()
                    where documentTypePermission.IsActive && ((userSelectionParam.UserGroupId != null && userGroupId.Id != null)
                                                              || (userSelectionParam.UserId != null && userSelectionParam.UserId == CurrentUserId))
                    select documentTypePermission);
        }

2. Why the below works?

var query = 
    from documentType in entitySet
    from userGroupId in Repository.ConvertToBigIntTable(userGroupIds, "userGroupId")
        .Where(userGroupId => documentType.Id == userGroupId.Id)
        .DefaultIfEmpty()
    join documentTypePermission in Repository.DocumentTypePermissions
        on documentType.Id equals documentTypePermission.DocumentTypeId
    join userSelectionParam in Repository.UserSelectionParams
        on documentTypePermission.UserSelectionId equals userSelectionParam.Id

[smitpatel]

GroupJoin doesn't have any equivalent operation on database. When user writes GroupJoin followed by SelectMany it is converted to a JOIN operation in database depending on the structure. See https://docs.microsoft.com/ef/core/querying/complex-query-operators#left-join

When EF core is trying to convert GroupJoin-SelectMany to LeftJoin operation, in first one it runs into issue because the generic type on the GroupJoin/SelectMany operation is different from the keyselector passed. When you write GroupJoin in later phase (as in query 2), for whatever reason that type mismatch is not happening. Compiler needs to assign a lot of different generic type when creating expression tree, this just happens to align with what EF Core can process.

The 2nd query in last post works because it doesn't utilize the code path of GroupJoin-SelectMany which is converted to JOIN during C# expression tree phase. When you use SelectMany, it is converted to APPLY operation on server side. In some case when adding the APPLY, if it can be converted to JOIN we do so. ALL this processing happens working with SQL so there is no error of type mismatch. See https://docs.microsoft.com/ef/core/querying/complex-query-operators#selectmany to know how different SelectMany patterns translate to different SQL.

[ankitmatrix08]

Thanks @smitpatel for the detailed explanation, will get back to you if there’s more clarification required.

[ankitmatrix08]

Hi @smitpatel - Any expected release version where in this issue will be fixed?
Our project is in the verge of migration from .NET Framework 4.8 to .NET 6 with EFCore 6 and we have around 1000s of Domain layer code which uses such kind of GroupJoins .

As our product has been shipped as SaaS, even the clients can write LINQ queries and they have written plenty of such "GroupJoins", hence it sounds very odd to ask everyone to have this change made manually over their solutions and not to mention it is prone to many human-errors.

Hence we would like to know as to when can we expect this "type-bug" to be resolved and shipped with which version, also can we prioritize this so that we can complete the migration successfully and in a timely manner?

CC: @ajcvickers

[ajcvickers]

@ankitmatrix08 We triage new issues on Tuesdays and Fridays. For general information on what is and is not patched, see release planning.

[ankitmatrix08]

@ajcvickers Thanks! I will wait for tomorrow for you to come back on this with the priority and release plan. As mentioned above, this is a very tricky situation for us as we can't effort to have too many code changes for this migration, and this issue seems to be a very basic one that should work anyhow.

[ankitmatrix08]

@ajcvickers Thanks! I will wait for tomorrow for you to come back on this with the priority and release plan. As mentioned above, this is a very tricky situation for us as we can't effort to have too many code changes for this migration, and this issue seems to be a very basic one that should work anyhow.

Any update on this?

[ajcvickers]

@ankitmatrix08 Some team members were out on Friday, so we were not able to fully assess this. However, based on the existence of workarounds and that we so far only have one customer report, I think given the policy for patching, it's unlikely that we will patch for this issue.