How to get users with roles in one single query?

Question

I have setup my Identity models just like described here: https://learn.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-2.2#add-user-and-role-navigation-properties

So my each User class has a collection of Roles, through UserRole "wrapper". All the entity relations are set.

And when i query for my users, i get all their roles for each user (using Lazy loading here, but "Include" makes no difference):

var users = _userManager.Users
    .AsNoTracking()
    .ToList();

But when checking the logs which EF Core creates i see that there is another query per each user to get the role:

[Parameters=[@_outer_Id='d550f61b-ed3d-4d90-8e7b-31552de50d3b' (Size = 450)], CommandType='"Text"', CommandTimeout='30']
SELECT [r].[RoleId] AS [Id], [r.Role].[Name], [r.Role].[DisplayName]
FROM [AspNetUserRoles] AS [r]
INNER JOIN [AspNetRoles] AS [r.Role] ON [r].[RoleId] = [r.Role].[Id]
WHERE ([r].[Discriminator] = N'UserRole') AND (@_outer_Id = [r].[UserId])

And this repeats per each user Id in my database.

What can be done to get the results using only one query?

Just in case, my models:

public class User : IdentityUser
{
    public virtual ICollection<UserRole> UserRoles { get; set; }
}

public class UserRole : IdentityUserRole<string>
{
    public virtual User User { get; set; }
    public virtual Role Role { get; set; }
}

public class Role : IdentityRole
{
    public virtual ICollection<UserRole> UserRoles { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<User>(b =>
    {
        b.HasMany(e => e.UserRoles)
            .WithOne(e => e.User)
            .HasForeignKey(ur => ur.UserId)
            .IsRequired();
    });

    modelBuilder.Entity<Role>(b =>
    {
        b.HasMany(e => e.UserRoles)
            .WithOne(e => e.Role)
            .HasForeignKey(ur => ur.RoleId)
            .IsRequired();
    });
}

Answer 1

It looks like you have lazy-loading enabled. EF never automatically joins related tables, you have to instruct it in some way to do so. In the case of lazy-loading, that happens by accessing the getter of the navigation property. The getter is overridden by EF to pull the related entities from the object cache, and if they cannot be found there, issue a query to retrieve them (hence the "lazy"). You're apparently just iterating over the users and accessing the UserRoles member for each, which causes a separate query to be issued for each user.

What you want to do instead is eager load the relationship(s). You do this via Include (and ThenInclude for sub-relationships). In other words:

var users = await _userManager.Users
    .Include(x => x.UserRoles)
        .ThenInclude(x => x.Role)
    .AsNoTracking()
    .ToListAsync();