CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
kianoush
kianoush

Reputation: 85

Change the code to improve the performance (eager loading)

i have this code for find UserInfoand find role of user in UserRole and find role info from Role.

I am using Identity Table.

this is my code: 

public virtual async Task<ActionResult<List<User>>> GetuserList(CancellationToken cancellationToken)
{
    var userDto = new UserDto();
    var users = await userManager.Users.Include(x => x.UserRoles).ThenInclude(t=>t.Role).ToListAsync();

    return users;
}

But this code is not good for performance Because in first step it get all user info userManager.Users, second step find all user role info and find all user info again .Include(x => x.UserRoles) and find all role info .ThenInclude(t=>t.Role).

I just need roleID from UserRole and roleName from Role.

What should I do to load Required info not all of them?

Update Code:

i write This code :

var users = userManager.Users.Select(x => new UserDto
        {
            UserName = x.UserName,
            Email = x.Email,
            Family = x.Family,
            Name = x.Name,
            Password = x.PasswordHash,
            PhoneNumber = x.PhoneNumber,
            RoleId = x.UserRoles.FirstOrDefault(t => t.UserId == x.Id).UserId
        });

but still i need to find RoleName on RoleTabel but it can not use the Include in Select . how can i use find RoleName ????

Upvotes: 0

Views: 121

Answers (3)

Steve Py
Steve Py

Reputation: 34673

To select from a nested child table references, the approach I use is to select into an anonymous type to build an efficient query, then further reduce from that into the DTO.

For example:

var users = userManager.Users.Select(x => new 
{
    UserName = x.UserName,
    Email = x.Email,
    Family = x.Family,
    Name = x.Name,
    Password = x.PasswordHash,
    PhoneNumber = x.PhoneNumber,
    Role = x.UserRoles.Select(r => new { r.RoleId, r.Role.RoleName}).FirstOrDefault()
}).ToList()
.Select(x => new UserDto
{
    UserName = x.UserName,
    Email = x.Email,
    Family = x.Family,
    Name = x.Name,
    Password = x.PasswordHash,
    PhoneNumber = x.PhoneNumber,
    RoleId = x.Role?.RoleId,
    RoleName = x.Role?.RoleName
}).ToList();

You can get by with a single .Select though this may lead to less efficient SQL as each inner .FirstOrDefault() etc. would likely re-JOIN the UserRole / Role tables. This way the first .Select() builds the SQL for a nested structure containing the child data we need, then the second operates across the resulting POCO anonymous types to construct the flat DTO to return.

One last thing, when using .First()/.FirstOrDefault() you should use an .OrderBy() clause to ensure a predictable result if you only want one entry out of a possible many.

Also, given that we are using .Select() to map our output, you do not need to use .Include() to select from related entities. You also don't need the .Where() clause inside your User.UserRoles. (User.UserRoles will only contain UserRoles for that UserId) Where you will see .Where() clauses used is when joining loosely associated tables from the DbContext. Directly related entity relationships via mapped FKs is preferable.

Edit: Given every user may not have a role, in the second .Select() you'll need to use the "?." operator to retrieve the Role ID and Name. This will mean the DTO Role ID must be null-able. If you want to default to a RoleId of 0 or such instead, you can use RoleId = x.Role?.RoleId ?? 0

Upvotes: 1

ibubi
ibubi

Reputation: 2529

Until you call ToListAsync() which executes your query on data source, you can shape your query with Include, ThenInclude, Select, Where, etc.. features.

What you should do is just combine your first quesry with the projection on your update.

public virtual async Task<ActionResult<List<User>>> GetuserList(CancellationToken cancellationToken)
{
    var userDto = new UserDto();
    var users = await userManager.Users.Include(x => x.UserRoles).ThenInclude(t=>t.Role)
       .Select(x => new UserDto
        {
            UserName = x.UserName,
            Email = x.Email,
            Family = x.Family,
            Name = x.Name,
            Password = x.PasswordHash,
            PhoneNumber = x.PhoneNumber,
            RoleId = x.UserRoles.FirstOrDefault(t => t.UserId == x.Id).UserId
        })
      .ToListAsync();

    return users;
}

Upvotes: 1

Patrick Goode
Patrick Goode

Reputation: 1452

I have something sort of similar that hopefully helps. It performs just fine

      if (users != null && users.Length > 0 )
        {
            foreach (var item in users)
            {
                var userRoles = await UserManager.GetRolesAsync(item);
                if (userRoles.Contains("Foreman"))
                {
                    CW.AddRange(db.CrewMemberForeman.Where(x => x.AssignedForemanId == item).Select(x => x.CrewMember).OrderBy(x => x.FirstName).ToList());
                }
            }
        }

Upvotes: 0

Related Questions