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

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?

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 23 (12 by maintainers)

Most upvoted comments

@rhythmnewt try:

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

@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 😃

@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.

@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.