Linq to Entities Getting list of entities by list of variables

Question

A system I'm helping to refactor uses a dashboard to help manage employees. During development with a small sample, the following code worked reasonably well:

public List<User> GetAllUsersByRoles(String role)
{
    var userList = new List<User>();
    string[] userNamesFromDB = Roles.GetUsersInRole(role);

    foreach(string username in userNamesFromDB)
    {
        MembershipUser userFromDB = Membership.GetUser(username);
        User user = ConvertUserFromDatabase(userFromDB);
        user.Roles = Roles.GetRolesForUser(username);
        userList.Add(user);
    }

    return userList;
}

As you can see, this iterative loop is making multiple trips to the database for every single user, and isn't scaling.

I'm hoping to use Linq with the Entity Framework to grab a number of users at the same time. This is easing the pain of all the joins, but it's still making a trip to the database for every user.

How can I transform this code to grab all users at the same time? I've used SelectMany in the past with success to solve this problem, but it doesn't seem to be translating here.

public List<User> GetAllUsersByRoleForDashboard(String role)
{
    List<User> userList = new List<User>();

    string[] userNamesFromDB = Roles.GetUsersInRole(role);

    var users = userNamesFromDB
        .SelectMany(user => ctx.aspnet_Users
        .Where(x => x.UserName == user)
        .Select(user => new User
        {
            FirstName = ctx.Profile
                .FirstOrDefault(x => x.ProfileID == ctx.User
                .FirstOrDefault(u => u.UserID == user.UserId).ProfileID).FirstName,
            LastName = ctx.Profile
                .FirstOrDefault(x => x.ProfileID == ctx.User
                .FirstOrDefault(u => u.UserID == user.UserId).ProfileID).LastName,
            UserName = user,
            IsApproved = ctx.aspnet_Membership
                .FirstOrDefault(m => m.UserId == user.UserId).IsApproved,

        })).ToList();

    return users;
}

Wahid Bitar · Accepted Answer · 2015-04-03T18:30:17.007

2

public List<User> GetAllUsersByRoleForDashboard(String role)
{
    var userNamesFromDB = new HashSet<string>(Roles.GetUsersInRole(role));

    var users = context.aspnet_Users.Where(u => userNamesFromDB.Contains(u.UserName))
    .Select(u=> new User
    {
        // Do your mapping
    }).ToList();

    return users;
}

BTW using HashSet in such queries give you a much better performance

edited Apr 03 '15 at 18:30

answered Apr 03 '15 at 18:20

Wahid Bitar

13,776
13
78
106

1

Why the change to the HashSet? I just tried the previous code and it worked just fine. – Wesley Apr 03 '15 at 18:28
heheh I just was writing the reason :) Actually if you've a lot of items you'll notice the performance. And by "a lot" I mean something like 700 – Wahid Bitar Apr 03 '15 at 18:31
1

Can you explain the performance gain when HashSets are used? The in-memory collection is just converted to sql parameters so why would a hashset perform better than an array? The data is not looped through multiple times. – Maarten Apr 03 '15 at 19:42
here is more description http://stackoverflow.com/questions/4445219/linq-ring-any-vs-contains-for-huge-collections – Wahid Bitar Apr 03 '15 at 19:47

score 1 · Answer 2 · answered Apr 03 '15 at 18:21

1

you can do it like that... if I understood your question right

from u in ctx.aspnet_Users where usernames.Contains(u.UserName) select u;

answered Apr 03 '15 at 18:21

Sherif Ahmed

1,896
1
19
37

Linq to Entities Getting list of entities by list of variables

2 Answers2