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)
@rhythmnewt try:
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.