LINQ to SQL C# Add a 1 to many List to Select Join, super slow

Question

I'm modifying a linq statement that has 3 tables. I need to add a list of records that could be 1 to many but I'm stuck on how to rewrite this statement.

I added an embedded Select to the Select new UserViewModel

Is this the right approach?

I made the changes in my UserViewModel as following

public class UserViewModel
{
public int usrId {get;set;}
public int deptId {get;set;}
public string firstName {get;set;}
public string lastName {get;set;}
public List<RoleViewModel> roles {get;set} // want to add one to many
}

User Entity Model
public class User
{
public User()
{
Roles = new HashSet<Role>(); // EF scaffold this
}

public int userId{get;set;}
public string firstName {get;set;}
public string lastName{get;set}
...
}

public class Role
{
public int roleId {get;set;}
public int userId {get;set;}
public string roleName {get;set;}
...
}
In my linq statment, I have the following

var users = this.dbContext.Users
.Join(this.dbContext.Roles,
usr => usr.usrId, role => role.usrId,
(usr, role) => new {usr, role}
)
.SelectMany(
usr_role_left => usr_role_left.Users.DefaultIfEmpty(),
(usr_role_left, role) => new {usr_role_left, role}
)
.Select(joined = new UserViewModel
{
usrId = joined.usr_role.usr_role_left.usrId,
firstName = joined.usr_role.usr_role_left.firstName,
lastName = joined.usr_role.usr_role_left.lastName,
roles = joined.usr_role.usr_role_left.users.Roles, // this is super slow
.Select(s => new RoleViewModel
{
roleId = s.roleId,
...
}).ToList()
}).ToList();

I am thinking that is it is the conversion from Role (Entity Scaffolded model) to RoleViewModel, so I tried this

.Select(joined = new UserViewModel
{
usrId = joined.usr_role.usr_role_left.usrId,
firstName = joined.usr_role.usr_role_left.firstName,
lastName = joined.usr_role.usr_role_left.lastName,
roles = joined.usr_role.usr_role_left.users.Roles.ToList(), // get the CS0029 error
}).ToList();
I get the typical Cannot convert type System.Collection.Generic.List<Repository.Role> to System.Collection.Generic.List<ViewModels.RoleViewModel>

The repository entity model and viewmodel are in different c# libraries, so I added the repository project to the ViewModels project and got a Cicular error.

In my UserViewModel, I got the definition of roles as

public List roles {get;set}

but changing it to public List roles {get;set} causes an error.

Any suggestion would be appreciated. Thanks.

Answer 1

Hi Marvelocity-9381,
I have some suggestions you can refer to:

1. Use AsNoTracking() to see if you can improve query performance.
2. You can also try to execute a query that returns all the results, and then group all the results. Make sure to execute .ToList() on the first query so that the second query does not execute multiple calls.
   Best Regards,
   Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.