EF Core - Unable to access navigation properties in Select()

c# entity-framework-core

Question

I am trying to access the navigation Roles property of the IdentityUser model.

Inside GetQueryable function I am setting the include property

protected virtual IQueryable<TEntity> GetQueryable<TEntity>(
    Expression<Func<TEntity, bool>> filter = null,
    string includeProperties = null
    )
    where TEntity : class, IEntity
    {
        IQueryable<TEntity> query = context.Set<TEntity>();
        if (filter != null)
        {
            query = query.Where(filter);
        }

        if (includeProperties != null)
        {
            query = query.Include(includeProperties);
        }
        return query;
    }

If I execute following query, the roles property is populated successfully:

return GetQueryable<ApplicationUser>(e => e.Id == id, "Roles").SingleOrDefault();

But when I use the projection (Select) with following Dto:

public class ApplicationUserDto: BaseDto
{
    public string Email { get; set; }
    public string Name { get; set; }
    public List<IdentityUserRole<string>> Roles{ get; set; }

    public static Expression<Func<ApplicationUser, ApplicationUserDto>> SelectProperties = (user) => new ApplicationUserDto {
        Id = user.Id,
        Email = user.Email,
        Name = user.Name,
        Roles = (List<IdentityUserRole<string>>)user.Roles
    };
}

Then the following query crashes:

return GetQueryable<ApplicationUser>(e => e.Id == id, "Roles").Select(ApplicationUserDto.SelectProperties).SingleOrDefault();

with the following error:

System.InvalidOperationException: The type of navigation property 'Roles' on the entity type 'IdentityUser<string, IdentityUserClaim<string>, IdentityUserRole
<string>, IdentityUserLogin<string>>' is 'ICollection<IdentityUserRole<string>>' for which it was not possible to create a concrete instance. Either initialize the
property before use, add a public parameterless constructor to the type, or use a type which can be assigned a HashSet<> or List<>.

It also logs a warning:

warn: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[6]
      The Include operation for navigation: 'user.Roles' was ignored because the target navigation is not reachable in the final query results. 
1
2
2/24/2017 1:02:09 PM

Accepted Answer

Actually, in EF Core 1.1 Include is ignored if we use Select(), refer to following:

Ignored includes section: https://docs.microsoft.com/en-us/ef/core/querying/related-data

That is the reason it was showing the warning:

warn: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[6]
      The Include operation for navigation: 'user.Roles' was ignored because the target navigation is not reachable in the final query results.

and ToList() also didn't work. So another projection was required on the navigation properties for it to work.

    public static Expression<Func<ApplicationRole, ApplicationRoleDto>> SelectProperties = (role) => new ApplicationRoleDto
    {
        Id = role.Id,
        Name = role.Name.Substring(0, role.Name.Length - role.TenantId.Length),
        Description = role.Description,
        // another projection on Claims navigation property
        Claims = role.Claims.Select(claim => claim.ClaimValue).ToList(),
    };

Note: Their is also a performance issue in this case, since eager loading doesn't occur if we use Select() on a list (since Include() is ignored) it will generate a separate sql query to fetch navigation property of each element in the result set.

On the other hand, if we only use Include(), it does a join as expected and hence performs better.

0
2/24/2017 1:10:56 PM

Popular Answer

You need to materialize (execute the subquery, which will gather Roles explicitly) in order to assign it to your DTO:

public class ApplicationUserDto: BaseDto
{
    public string Email { get; set; }
    public string Name { get; set; }
    public List<IdentityUserRole<string>> Roles{ get; set; }

    public static Expression<Func<ApplicationUser, ApplicationUserDto>> SelectProperties = (user) => new ApplicationUserDto {
        Id = user.Id,
        Email = user.Email,
        Name = user.Name,
        Roles = user.Roles.ToList()
    };
}

And remember to add: using System.Linq; to your file in order to be able to call .ToList() on ICollection.

As you stated in comments, the type you want is a string, so you can do anything after the user.Roles, i.e perform further projection like this:

user.Roles.Select(role=> role.RoleId).ToList()

Just remember to materialize the results afterwards.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow