EF Core Lambda Expression Object Reference not set to an instance of an object on multiple joins

Question

I am having weird error of NullReferenceException while querying the data using Entity framework core lambda syntax.

using this code

           var usersWithRights = await _dbContext.TblUsers.Where(x => x.IsInternal).Select(x => new
            {
                RightIds = x.TblInternalUserRoles.FirstOrDefault().Role.TblInternalRoleRights.Select(i => i.RightId).ToList()
            }).ToListAsync();

Where as the same code works when I apply Count instead of Select like

           var usersWithRights = await _dbContext.TblUsers.Where(x => x.IsInternal).Select(x => new
            {
                RightIds = x.TblInternalUserRoles.FirstOrDefault().Role.TblInternalRoleRights.Count
            }).ToListAsync();

Above code using Count give me the accurate result of count but I want to select the RightIds. I have crossed check that the record exists in my database and as a result when I query using Count it gives me the accurate result.

I just want to ask if there is any limit on Entity framework core while joining data? like are there a maximum number of joins allowed in EF Core or I can join any number of tables?

Answer 1

There are no join limits, but unfortunately there are still many shortcomings/bugs/issues with EF Core query translation of some LINQ constructs.

Normally you don't need Include / ThenInclude in projection (Select) queries because they are ignored. But getting NRE w/o them here indicates client evaluation attempt (EF Core 3.x still sypports client evaluation for some constructs, and mainly in final projection like here), which in turn means failed translation.

Here the problem seems to be

x.TblInternalUserRoles.FirstOrDefault().Role.TblInternalRoleRights.Select(i => i.RightId)

i.e. converting sequence (x.TblInternalUserRoles) to single element (.FirstOrDefault()) and then taking subsequence (.Role.TblInternalRoleRights).

The working solution is to use SelectMany operator for flattening the source sequence. FirstOrDefault() should be removed (it makes no sense to me) or if really needed, replaced with the corresponding equivalent sequence operator Take(1).

e.g.

RightIds = x.TblInternalUserRoles
    .SelectMany(ur => ur.Role.TblInternalRoleRights.Select(i => i.RightId))
    .ToList()

or

RightIds = x.TblInternalUserRoles
    .SelectMany(ur => ur.Role.TblInternalRoleRights, (ur, i) => i.RightId)
    .ToList()

or

RightIds = x.TblInternalUserRoles
    .SelectMany(ur => ur.Role.TblInternalRoleRights)
    .Select(i => i.RightId)
    .ToList()