Efcore: Query: Complex query with multiple joins and group by throws "nullable object must have a value"

[maumar]

Reported by a customer @armitagemderivitec here: https://github.com/aspnet/EntityFrameworkCore/issues/13517

The problem:

This is one of our complex joins returning this error:

public static IQueryable<vBookUserAccess> vBookUserAccesses(this PortfolioDbContext context)
{
    return from ga in context.GroupAccesses
       join g in context.Groups on new { ga.GroupId, GroupTypeId = 2 } equals
           new { GroupId = g.Id, g.GroupTypeId }
       join at in context.AccessTypes on ga.AccessTypeId equals at.Id
       join gs in context.GroupStatuses on g.GroupStatusId equals gs.Id
       join u in context.AspNetUsers on ga.UserId equals u.Id
       join uas in context.UserAccountStatuses on u.AccountStatus equals uas.Id
       join jr in context.Relationships() on ga.GroupId equals jr.ChildGroupId into joined
       from relationship in joined.DefaultIfEmpty()
       select relationship == null
          ? new vBookUserAccess
                {
                    Id = ga.GroupId,
                    Name = g.Name,
                    Description = g.Description,
                    StatusId = g.GroupStatusId,
                    StatusName = gs.Name,
                    StatusDescription = gs.Description,
                    NumberOfParentPortfolios = null,
                    ParentPortfolioRelationshipTypeId = null,
                    ParentPortfolioRelationshipTypeName = null,
                    ParentPortfolioRelationshipTypeDescription = null,
                    AccessUserId = ga.UserId,
                    AccessUserName = u.UserName,
                    AccessUserFirstName = u.FirstName,
                    AccessUserLastName = u.LastName,
                    AccessUserEmail = u.Email,
                    AccessUserAccountStatusId = u.AccountStatus,
                    AccessUserAccountStatusName = uas.Name,
                    AccessUserAccountStatusDescription = uas.Description,
                    AccessTypeId = ga.AccessTypeId,
                    AccessTypeName = at.Name,
                    AccessTypeDescription = at.Description,
                    AccessCreatedDate = ga.CreatedDate,
                    AccessCreatedBy = ga.CreatedBy,
                    AccessModifiedDate = ga.ModifiedDate,
                    AccessModifiedBy = ga.ModifiedBy,
                    AccessVersion = ga.RowVersion
                }
          : new vBookUserAccess
                {
                    Id = ga.GroupId,
                    Name = g.Name,
                    Description = g.Description,
                    StatusId = g.GroupStatusId,
                    StatusName = gs.Name,
                    StatusDescription = gs.Description,
                    NumberOfParentPortfolios = relationship.NumberOfParentPortfolios,
                    ParentPortfolioRelationshipTypeId =
                        relationship.ParentPortfolioRelationshipTypeId,
                    ParentPortfolioRelationshipTypeName =
                        relationship.ParentPortfolioRelationshipTypeName,
                    ParentPortfolioRelationshipTypeDescription =
                        relationship.ParentPortfolioRelationshipTypeDescription,

                    AccessUserId = ga.UserId,
                    AccessUserName = u.UserName,
                    AccessUserFirstName = u.FirstName,
                    AccessUserLastName = u.LastName,
                    AccessUserEmail = u.Email,
                    AccessUserAccountStatusId = u.AccountStatus,
                    AccessUserAccountStatusName = uas.Name,
                    AccessUserAccountStatusDescription = uas.Description,
                    AccessTypeId = ga.AccessTypeId,
                    AccessTypeName = at.Name,
                    AccessTypeDescription = at.Description,
                    AccessCreatedDate = ga.CreatedDate,
                    AccessCreatedBy = ga.CreatedBy,
                    AccessModifiedDate = ga.ModifiedDate,
                    AccessModifiedBy = ga.ModifiedBy,
                    AccessVersion = ga.RowVersion
                };
}

private static IQueryable<Relationship> Relationships(this PortfolioDbContext context)
{
    return from gr in context.GroupRelationships
       join gs in context.Groups on new { gr.ParentGroupId, GroupTypeId = 1 } equals
           new { ParentGroupId = gs.Id, gs.GroupTypeId }
       join relationshipType in context.RelationshipTypes on gr.RelationshipTypeId equals
           relationshipType.Id
       group gr by new
           {
               gr.ChildGroupId,
               gr.RelationshipTypeId,
               relationshipType.Name,
               relationshipType.Description
           }
       into g
       select new Relationship
          {
              ChildGroupId = g.Key.ChildGroupId,
              ParentPortfolioRelationshipTypeId = g.Key.RelationshipTypeId,
              ParentPortfolioRelationshipTypeName = g.Key.Name,
              ParentPortfolioRelationshipTypeDescription = g.Key.Description,
              NumberOfParentPortfolios = g.Count()
          };
}

private class Relationship
{
    public int ChildGroupId { get; set; }

    public int NumberOfParentPortfolios { get; set; }

    public string ParentPortfolioRelationshipTypeDescription { get; set; }

    public int ParentPortfolioRelationshipTypeId { get; set; }

    public string ParentPortfolioRelationshipTypeName { get; set; }
}

This worked in EF6 even without the null operator. Presumably the nested IQueryable is the cause. Any ideas?

[armitagemderivitec]

The properties in question are nullable and the stack trace is as follows:

System.InvalidOperationException : Nullable object must have a value.
   at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
   at lambda_method(Closure , QueryContext , ValueBuffer )
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.IShaper<TOut>.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.CompositeShaper.TypedCompositeShaper`5.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.CompositeShaper.TypedCompositeShaper`5.Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.IShaper<TResult>.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.IShaper<TOut>.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Linq.Lookup`2.CreateForJoin(IEnumerable`1 source, Func`2 keySelector, IEqualityComparer`1 comparer)
   at System.Linq.Enumerable.<JoinIterator>d__38`4.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

We are running on Postgres, could that be a factor?

[armitagemderivitec]

@maumar what can I do to help?

[maumar]

@armitagemderivitec GroupBy seems like the most probable culprit. EF Core should be able to translate it, but can't handle scenarios where the result of group by is composed on. @smitpatel thoughts?

[maumar]

@armitagemderivitec Ideally we would like a repro sample with listings of all the entities used in the query and DbContext (specifically contents of OnModelCreating method). Ideally with some sample data. This way we can debug into the scenario and hopefully narrow it down and (hopefully) provide the workaround.

Postgres shouldn't make a difference here. Problem seems to be in the client-eval part of the query.

[armitagemderivitec]

@maumar I'll put something together. The easiest way will be to provide a subsection of our test suite but I'll have to make it private. I'll add you to a new repo, if there is anyone else I should add let me know.

[armitagemderivitec]

@maumar I've created a repo with failing tests for the issues I have. I added you and made you admin so you can add others. It contains everything required including seed data for the database. I configured Sqlite In-memory and Postgres versions

[maumar]

@armitagemderivitec I can't see the repo. You can send me the info to [my github name] AT microsoft D0T com

[armitagemderivitec]

@maumar I've emailed you a link to the invitations page. The invite will have been sent to the email account linked to your github account. I tried to invite the Microsoft one but it says it wasn't linked to Github.

[armitagemderivitec]

@maumar apologies if you had pulled and tried to build already, I forgot to update a project reference after moving the folder and have corrected the build configuration. Just so I can manage expectations internally do you have a rough idea of when you may have time to look at this?

[maumar]

@armitagemderivitec I should be able to get to it within next few days. However I will not be working on the fix per se (bug was triaged to Backlog) but will try to come up with a workaround.

[maumar]

@armitagemderivitec you should be able to unblock yourself by making properties of Relationship class nullable:

  private class Relationship
        {
            public int? ChildGroupId { get; set; }

            public int? NumberOfParentPortfolios { get; set; }

            public string ParentPortfolioRelationshipTypeDescription { get; set; }

            public int? ParentPortfolioRelationshipTypeId { get; set; }

            public string ParentPortfolioRelationshipTypeName { get; set; }
        }

[maumar]

looking at the query plan:

(QueryContext queryContext) => IEnumerable<vBookUserAccess> _InterceptExceptions(
|__ source: IEnumerable<vBookUserAccess> _ShapedQuery(
|   |__ queryContext: queryContext, 
|   |__ shaperCommandContext: SelectExpression: 
|   |       SELECT [t].[ChildGroupId], [t].[ParentPortfolioRelationshipTypeId], [t].[ParentPortfolioRelationshipTypeName], [t].[ParentPortfolioRelationshipTypeDescription], [t].[NumberOfParentPortfolios], [ga].[GroupId] AS [Id], [g].[Name], [g].[Description], [g].[GroupStatusId] AS [StatusId], [gs].[Name] AS [StatusName], [gs].[Description] AS [StatusDescription], [ga].[UserId] AS [AccessUserId], [u].[UserName] AS [AccessUserName], [u].[FirstName] AS [AccessUserFirstName], [u].[LastName] AS [AccessUserLastName], [u].[Email] AS [AccessUserEmail], [u].[AccountStatus] AS [AccessUserAccountStatusId], [uas].[Name] AS [AccessUserAccountStatusName], [uas].[Description] AS [AccessUserAccountStatusDescription], [ga].[AccessTypeId], [at].[Name] AS [AccessTypeName], [at].[Description] AS [AccessTypeDescription], [ga].[CreatedDate] AS [AccessCreatedDate], [ga].[CreatedBy] AS [AccessCreatedBy], [ga].[ModifiedDate] AS [AccessModifiedDate], [ga].[ModifiedBy] AS [AccessModifiedBy], [ga].[RowVersion] AS [AccessVersion]
|   |       FROM [dbo].[GroupAccess] AS [ga]
|   |       INNER JOIN [dbo].[Groups] AS [g] ON ([ga].[GroupId] = [g].[Id]) AND (2 = [g].[GroupTypeId])
|   |       INNER JOIN [dbo].[AccessTypes] AS [at] ON [ga].[AccessTypeId] = [at].[Id]
|   |       INNER JOIN [dbo].[GroupStatus] AS [gs] ON [g].[GroupStatusId] = [gs].[Id]
|   |       INNER JOIN [dbo].[AspNetUsers] AS [u] ON [ga].[UserId] = [u].[Id]
|   |       INNER JOIN [dbo].[UserAccountStatus] AS [uas] ON [u].[AccountStatus] = [uas].[Id]
|   |       LEFT JOIN (
|   |           SELECT [gr].[ChildGroupId], [gr].[RelationshipTypeId] AS [ParentPortfolioRelationshipTypeId], [relationshipType].[Name] AS [ParentPortfolioRelationshipTypeName], [relationshipType].[Description] AS [ParentPortfolioRelationshipTypeDescription], COUNT(*) AS [NumberOfParentPortfolios]
|   |           FROM [dbo].[GroupRelationships] AS [gr]
|   |           INNER JOIN [dbo].[Groups] AS [gs0] ON ([gr].[ParentGroupId] = [gs0].[Id]) AND (1 = [gs0].[GroupTypeId])
|   |           INNER JOIN [dbo].[RelationshipTypes] AS [relationshipType] ON [gr].[RelationshipTypeId] = [relationshipType].[Id]
|   |           GROUP BY [gr].[ChildGroupId], [gr].[RelationshipTypeId], [relationshipType].[Name], [relationshipType].[Description]
|   |       ) AS [t] ON [ga].[GroupId] = [t].[ChildGroupId], 
|   |__ shaper: (QueryContext queryContext | TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>, ValueBuffer>, ValueBuffer>, ValueBuffer>, Relationship> t6) => t6.Inner == null ? new vBookUserAccess{ 
|           Id = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 5, GroupAccess.GroupId), 
|           Name = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 6, Group.Name), 
|           Description = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 7, Group.Description), 
|           StatusId = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 8, Group.GroupStatusId), 
|           StatusName = string TryReadValue(t6.Outer.Outer.Outer.Inner, 9, GroupStatus.Name), 
|           StatusDescription = string TryReadValue(t6.Outer.Outer.Outer.Inner, 10, GroupStatus.Description), 
|           NumberOfParentPortfolios = null, 
|           ParentPortfolioRelationshipTypeId = null, 
|           ParentPortfolioRelationshipTypeName = null, 
|           ParentPortfolioRelationshipTypeDescription = null, 
|           AccessUserId = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 11, GroupAccess.UserId), 
|           AccessUserName = string TryReadValue(t6.Outer.Outer.Inner, 12, AspNetUser.UserName), 
|           AccessUserFirstName = string TryReadValue(t6.Outer.Outer.Inner, 13, AspNetUser.FirstName), 
|           AccessUserLastName = string TryReadValue(t6.Outer.Outer.Inner, 14, AspNetUser.LastName), 
|           AccessUserEmail = string TryReadValue(t6.Outer.Outer.Inner, 15, AspNetUser.Email), 
|           AccessUserAccountStatusId = int TryReadValue(t6.Outer.Outer.Inner, 16, AspNetUser.AccountStatus), 
|           AccessUserAccountStatusName = string TryReadValue(t6.Outer.Inner, 17, UserAccountStatus.Name), 
|           AccessUserAccountStatusDescription = string TryReadValue(t6.Outer.Inner, 18, UserAccountStatus.Description), 
|           AccessTypeId = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 19, GroupAccess.AccessTypeId), 
|           AccessTypeName = string TryReadValue(t6.Outer.Outer.Outer.Outer.Inner, 20, AccessType.Name), 
|           AccessTypeDescription = string TryReadValue(t6.Outer.Outer.Outer.Outer.Inner, 21, AccessType.Description), 
|           AccessCreatedDate = Nullable<DateTime> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 22, GroupAccess.CreatedDate), 
|           AccessCreatedBy = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 23, GroupAccess.CreatedBy), 
|           AccessModifiedDate = Nullable<DateTime> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 24, GroupAccess.ModifiedDate), 
|           AccessModifiedBy = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 25, GroupAccess.ModifiedBy), 
|           AccessVersion = Nullable<int> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 26, GroupAccess.RowVersion) 
|       }
|        : new vBookUserAccess{ 
|           Id = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 5, GroupAccess.GroupId), 
|           Name = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 6, Group.Name), 
|           Description = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 7, Group.Description), 
|           StatusId = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 8, Group.GroupStatusId), 
|           StatusName = string TryReadValue(t6.Outer.Outer.Outer.Inner, 9, GroupStatus.Name), 
|           StatusDescription = string TryReadValue(t6.Outer.Outer.Outer.Inner, 10, GroupStatus.Description), 
|           NumberOfParentPortfolios = t6.Inner?.NumberOfParentPortfolios, 
|           ParentPortfolioRelationshipTypeId = t6.Inner?.ParentPortfolioRelationshipTypeId, 
|           ParentPortfolioRelationshipTypeName = t6.Inner?.ParentPortfolioRelationshipTypeName, 
|           ParentPortfolioRelationshipTypeDescription = t6.Inner?.ParentPortfolioRelationshipTypeDescription, 
|           AccessUserId = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 11, GroupAccess.UserId), 
|           AccessUserName = string TryReadValue(t6.Outer.Outer.Inner, 12, AspNetUser.UserName), 
|           AccessUserFirstName = string TryReadValue(t6.Outer.Outer.Inner, 13, AspNetUser.FirstName), 
|           AccessUserLastName = string TryReadValue(t6.Outer.Outer.Inner, 14, AspNetUser.LastName), 
|           AccessUserEmail = string TryReadValue(t6.Outer.Outer.Inner, 15, AspNetUser.Email), 
|           AccessUserAccountStatusId = int TryReadValue(t6.Outer.Outer.Inner, 16, AspNetUser.AccountStatus), 
|           AccessUserAccountStatusName = string TryReadValue(t6.Outer.Inner, 17, UserAccountStatus.Name), 
|           AccessUserAccountStatusDescription = string TryReadValue(t6.Outer.Inner, 18, UserAccountStatus.Description), 
|           AccessTypeId = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 19, GroupAccess.AccessTypeId), 
|           AccessTypeName = string TryReadValue(t6.Outer.Outer.Outer.Outer.Inner, 20, AccessType.Name), 
|           AccessTypeDescription = string TryReadValue(t6.Outer.Outer.Outer.Outer.Inner, 21, AccessType.Description), 
|           AccessCreatedDate = Nullable<DateTime> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 22, GroupAccess.CreatedDate), 
|           AccessCreatedBy = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 23, GroupAccess.CreatedBy), 
|           AccessModifiedDate = Nullable<DateTime> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 24, GroupAccess.ModifiedDate), 
|           AccessModifiedBy = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 25, GroupAccess.ModifiedBy), 
|           AccessVersion = Nullable<int> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 26, GroupAccess.RowVersion) 
|       }
|__ ), 
|__ contextType: Repro14773.PortfolioDbContext, 
|__ logger: DiagnosticsLogger<Query>, 
|__ queryContext: Unhandled parameter: queryContext)

the shaper parameter is:

TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>, ValueBuffer>, ValueBuffer>, ValueBuffer>, Relationship>

ValueBuffers store scalar properties, but we also try to materialize Relationship object here. Problem is when the properties used to materialize it are null.

[maumar]

@armitagemderivitec once you do this, you can actually simplify the query by removing the null protection logic in the final projection:

from ga in context.GroupAccesses
                   join g in context.Groups on new { ga.GroupId, GroupTypeId = 2 } equals
                       new { GroupId = g.Id, g.GroupTypeId }
                   join at in context.AccessTypes on ga.AccessTypeId equals at.Id
                   join gs in context.GroupStatuses on g.GroupStatusId equals gs.Id
                   join u in context.AspNetUsers on ga.UserId equals u.Id
                   join uas in context.UserAccountStatuses on u.AccountStatus equals uas.Id
                   join jr in context.Relationships() on ga.GroupId equals jr.ChildGroupId into joined
                   from relationship in joined.DefaultIfEmpty()
                   select new vBookUserAccess
                              {
                                  Id = ga.GroupId,
                                  Name = g.Name,
                                  Description = g.Description,
                                  StatusId = g.GroupStatusId,
                                  StatusName = gs.Name,
                                  StatusDescription = gs.Description,
                                  NumberOfParentPortfolios = relationship.NumberOfParentPortfolios,
                                  ParentPortfolioRelationshipTypeId =
                                            relationship.ParentPortfolioRelationshipTypeId,
                                  ParentPortfolioRelationshipTypeName =
                                            relationship.ParentPortfolioRelationshipTypeName,
                                  ParentPortfolioRelationshipTypeDescription =
                                            relationship.ParentPortfolioRelationshipTypeDescription,

                                  AccessUserId = ga.UserId,
                                  AccessUserName = u.UserName,
                                  AccessUserFirstName = u.FirstName,
                                  AccessUserLastName = u.LastName,
                                  AccessUserEmail = u.Email,
                                  AccessUserAccountStatusId = u.AccountStatus,
                                  AccessUserAccountStatusName = uas.Name,
                                  AccessUserAccountStatusDescription = uas.Description,
                                  AccessTypeId = ga.AccessTypeId,
                                  AccessTypeName = at.Name,
                                  AccessTypeDescription = at.Description,
                                  AccessCreatedDate = ga.CreatedDate,
                                  AccessCreatedBy = ga.CreatedBy,
                                  AccessModifiedDate = ga.ModifiedDate,
                                  AccessModifiedBy = ga.ModifiedBy,
                                  AccessVersion = ga.RowVersion
                              };

EFCore is smart enough to detect that relationship can be null and inject null protection automatically. Here is the query plan we generate for the simplified case (notice ?. after t6.Inner when constructing ParentPortfolioRelationship* properties)

(QueryContext queryContext) => IEnumerable<vBookUserAccess> _InterceptExceptions(
|__ source: IEnumerable<vBookUserAccess> _ShapedQuery(
|   |__ queryContext: queryContext, 
|   |__ shaperCommandContext: SelectExpression: 
|   |       SELECT [t].[ChildGroupId], [t].[ParentPortfolioRelationshipTypeId], [t].[ParentPortfolioRelationshipTypeName], [t].[ParentPortfolioRelationshipTypeDescription], [t].[NumberOfParentPortfolios], [ga].[GroupId] AS [Id], [g].[Name] AS [Name0], [g].[Description] AS [Description0], [g].[GroupStatusId] AS [StatusId], [gs].[Name] AS [StatusName], [gs].[Description] AS [StatusDescription], [ga].[UserId] AS [AccessUserId], [u].[UserName] AS [AccessUserName], [u].[FirstName] AS [AccessUserFirstName], [u].[LastName] AS [AccessUserLastName], [u].[Email] AS [AccessUserEmail], [u].[AccountStatus] AS [AccessUserAccountStatusId], [uas].[Name] AS [AccessUserAccountStatusName], [uas].[Description] AS [AccessUserAccountStatusDescription], [ga].[AccessTypeId], [at].[Name] AS [AccessTypeName], [at].[Description] AS [AccessTypeDescription], [ga].[CreatedDate] AS [AccessCreatedDate], [ga].[CreatedBy] AS [AccessCreatedBy], [ga].[ModifiedDate] AS [AccessModifiedDate], [ga].[ModifiedBy] AS [AccessModifiedBy], [ga].[RowVersion] AS [AccessVersion]
|   |       FROM [dbo].[GroupAccess] AS [ga]
|   |       INNER JOIN [dbo].[Groups] AS [g] ON ([ga].[GroupId] = [g].[Id]) AND (2 = [g].[GroupTypeId])
|   |       INNER JOIN [dbo].[AccessTypes] AS [at] ON [ga].[AccessTypeId] = [at].[Id]
|   |       INNER JOIN [dbo].[GroupStatus] AS [gs] ON [g].[GroupStatusId] = [gs].[Id]
|   |       INNER JOIN [dbo].[AspNetUsers] AS [u] ON [ga].[UserId] = [u].[Id]
|   |       INNER JOIN [dbo].[UserAccountStatus] AS [uas] ON [u].[AccountStatus] = [uas].[Id]
|   |       LEFT JOIN (
|   |           SELECT [gr].[ChildGroupId], [gr].[RelationshipTypeId] AS [ParentPortfolioRelationshipTypeId], [relationshipType].[Name] AS [ParentPortfolioRelationshipTypeName], [relationshipType].[Description] AS [ParentPortfolioRelationshipTypeDescription], COUNT(*) AS [NumberOfParentPortfolios]
|   |           FROM [dbo].[GroupRelationships] AS [gr]
|   |           INNER JOIN [dbo].[Groups] AS [gs0] ON ([gr].[ParentGroupId] = [gs0].[Id]) AND (1 = [gs0].[GroupTypeId])
|   |           INNER JOIN [dbo].[RelationshipTypes] AS [relationshipType] ON [gr].[RelationshipTypeId] = [relationshipType].[Id]
|   |           GROUP BY [gr].[ChildGroupId], [gr].[RelationshipTypeId], [relationshipType].[Name], [relationshipType].[Description]
|   |       ) AS [t] ON [ga].[GroupId] = [t].[ChildGroupId], 
|   |__ shaper: (QueryContext queryContext | TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, ValueBuffer>, ValueBuffer>, ValueBuffer>, ValueBuffer>, Relationship> t6) => new vBookUserAccess{ 
|           Id = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 5, GroupAccess.GroupId), 
|           Name = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 6, Group.Name), 
|           Description = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 7, Group.Description), 
|           StatusId = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Inner, 8, Group.GroupStatusId), 
|           StatusName = string TryReadValue(t6.Outer.Outer.Outer.Inner, 9, GroupStatus.Name), 
|           StatusDescription = string TryReadValue(t6.Outer.Outer.Outer.Inner, 10, GroupStatus.Description), 
|           NumberOfParentPortfolios = t6.Inner?.NumberOfParentPortfolios, 
|           ParentPortfolioRelationshipTypeId = t6.Inner?.ParentPortfolioRelationshipTypeId, 
|           ParentPortfolioRelationshipTypeName = t6.Inner?.ParentPortfolioRelationshipTypeName, 
|           ParentPortfolioRelationshipTypeDescription = t6.Inner?.ParentPortfolioRelationshipTypeDescription, 
|           AccessUserId = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 11, GroupAccess.UserId), 
|           AccessUserName = string TryReadValue(t6.Outer.Outer.Inner, 12, AspNetUser.UserName), 
|           AccessUserFirstName = string TryReadValue(t6.Outer.Outer.Inner, 13, AspNetUser.FirstName), 
|           AccessUserLastName = string TryReadValue(t6.Outer.Outer.Inner, 14, AspNetUser.LastName), 
|           AccessUserEmail = string TryReadValue(t6.Outer.Outer.Inner, 15, AspNetUser.Email), 
|           AccessUserAccountStatusId = int TryReadValue(t6.Outer.Outer.Inner, 16, AspNetUser.AccountStatus), 
|           AccessUserAccountStatusName = string TryReadValue(t6.Outer.Inner, 17, UserAccountStatus.Name), 
|           AccessUserAccountStatusDescription = string TryReadValue(t6.Outer.Inner, 18, UserAccountStatus.Description), 
|           AccessTypeId = int TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 19, GroupAccess.AccessTypeId), 
|           AccessTypeName = string TryReadValue(t6.Outer.Outer.Outer.Outer.Inner, 20, AccessType.Name), 
|           AccessTypeDescription = string TryReadValue(t6.Outer.Outer.Outer.Outer.Inner, 21, AccessType.Description), 
|           AccessCreatedDate = Nullable<DateTime> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 22, GroupAccess.CreatedDate), 
|           AccessCreatedBy = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 23, GroupAccess.CreatedBy), 
|           AccessModifiedDate = Nullable<DateTime> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 24, GroupAccess.ModifiedDate), 
|           AccessModifiedBy = string TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 25, GroupAccess.ModifiedBy), 
|           AccessVersion = Nullable<int> TryReadValue(t6.Outer.Outer.Outer.Outer.Outer.Outer, 26, GroupAccess.RowVersion) 
|       }
|__ ), 
|__ contextType: Repro14773.PortfolioDbContext, 
|__ logger: DiagnosticsLogger<Query>, 
|__ queryContext: Unhandled parameter: queryContext)

[armitagemderivitec]

@maumar you're a legend, thanks! In fact, this is perhaps a feature rather than a bug, to make sure intermediary objects have the right types, rather than in just the final projection. There is still one issue with the GetPortfolioHierarchyByUserIdReturnsPermissionedPortfoliosOnly test but this relates to this issue: #13204

Happy for you to close this one.

[maumar]

@armitagemderivitec glad I could help.
I'd consider it a bug/limitation of the current design. There is nothing wrong with the query, so in theory it should work fine. The issue should go away in 3.0 however, so leaving it open but in backlog seems reasonable.

[armitagemderivitec]

@maumar is there any chance you could offer some guidance on #13204, again just to manage internal expectations

[rhythmnewt]

Can someone confirm that this issue will be resolved in 3.0 release? It doesn't have be a complex query for it to fail and in our case it looks like this:

var result = await context.Customers
                .Select(d => new
                {
                    d.Id,
                    d.Address.AddressType,
                }).ToListAsync(cancellationToken);

In the case where Customer has null Address object, the same "nullable object must have a value" error is thrown. The workaround does work if we make the AddressType enumeration nullable in the entity definition. It is silly though, because in reality the AddressType cannot be null but we have to treat it as such in order not to break the query.

[maumar]

@rhythmnewt try:

var result = await context.Customers
                .Select(d => new
                {
                    d.Id,
                    (AddressType?)d.Address.AddressType,
                }).ToListAsync(cancellationToken);

[rhythmnewt]

@rhythmnewt try:

var result = await context.Customers
                .Select(d => new
                {
                    d.Id,
                    (AddressType?)d.Address.AddressType,
                }).ToListAsync(cancellationToken);

Well, compiler will bark at this because it's an anonymous method, so would have to be
addressType = (AddressType?)d.Address.AddressType, for it to build. Oddly enough I though we tested this last night and it did not work, it does work now - so probably a mistake on our end.

Still would be nice to keep the same syntax as EF6 in this case :)

[smitpatel]

@rhythmnewt - The query you are writing would never work since you have type mismatch. Your anonymous type has AddressType member which is non-null since Address navigation can be null, the result read from database will give you back null. Assigning null to a non-null property throws above exception. If you don't have Address null ever, you won't get exception and things would always work. There is nothing EF Core or compiler can do about it. It is application level information and should be treated as such.

[rhythmnewt]

@smitpatel You're right, we got our wires crossed here. Porting from EF6 to EFCore and stepped on a few rakes already, so now jumping at the shadows. Sorry about that!

[smitpatel]

@maumar has repro for this. Can you try again if it works in latest codebase?

[maumar]

I verified this indeed works on the current bits. (somewhat simplified) query produces the following sql:

SELECT [t].[ChildGroupId], [t].[RelationshipTypeId], [t].[Name], [t].[Description], [t].[c], [g].[GroupId], [g0].[Name], [g0].[Description], [g0].[GroupStatusId], [g1].[Name], [g1].[Description], [g].[UserId], [g].[AccessTypeId], [a].[Name], [a].[Description], [g].[CreatedDate], [g].[CreatedBy], [g].[ModifiedDate], [g].[ModifiedBy], [g].[RowVersion]
FROM [GroupAccesses] AS [g]
INNER JOIN [Groups] AS [g0] ON ([g].[GroupId] = [g0].[Id]) AND (2 = [g0].[GroupTypeId])
INNER JOIN [AccessTypes] AS [a] ON [g].[AccessTypeId] = [a].[Id]
INNER JOIN [GroupStatuses] AS [g1] ON [g0].[GroupStatusId] = [g1].[Id]
LEFT JOIN (
    SELECT [g2].[ChildGroupId], [g2].[RelationshipTypeId], [r].[Name], [r].[Description], COUNT(*) AS [c]
    FROM [GroupRelationships] AS [g2]
    INNER JOIN [Groups] AS [g3] ON ([g2].[ParentGroupId] = [g3].[Id]) AND (1 = [g3].[GroupTypeId])
    INNER JOIN [RelationshipTypes] AS [r] ON [g2].[RelationshipTypeId] = [r].[Id]
    GROUP BY [g2].[ChildGroupId], [g2].[RelationshipTypeId], [r].[Name], [r].[Description]
) AS [t] ON [g].[GroupId] = [t].[ChildGroupId]