LINQ statment looks unreasonable to me: what's my error?

Question

There has to be a better way:

public IList<ApplicationUser> GetProspects()
        {
            var UsrNames = Roles.GetUsersInRole("prospect");
            IList<ApplicationUser> Users = new List<ApplicationUser>();

            foreach ( var u in UsrNames)
            {
                // In theory should return at most one element. 
                var UserListFromQuery = from usr in (new ApplicationDbContext()).Users where usr.UserName == u select usr;
                Users.Add(UserListFromQuery.ElementAtOrDefault(0));
            }
            return Users;
        }

Can you please show me the error of my ways?

Answer 1

This should do what you want:

using (var context = new ApplicationDbContext())
{
    var result = Roles.GetUsersInRole("prospect")
                      .Select(name => context.Users.FirstOrDefault(user => user.UserName == name))
                      .Where(user => user != null)
                      .ToList();
}

I've modified your code to utilize a using statement for the context, so as to ensure it is disposed of properly even if there is an exception. Then I have a linq query which does the following:

1. Get the usernames
2. For every username, select the first user in Users with a matching username
3. Remove all nulls from the resulting enumeration. This is necessary because FirstOrDefault returns null if no matching user is found
4. Turn our final enumeration into a List