ASP.NET Membership provider unable to return user roles in LINQ query

Question

I'm trying to get a list of all the user with their roles using LINQ. The application is based on Membership Provider ver.1, I suppose as i can see from the below config lines:

<membership defaultProvider="DefaultMembershipProvider">
  <providers>
    <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="2" applicationName="/" />
  </providers>
</membership>
<roleManager enabled="true" defaultProvider="DefaultRoleProvider">
  <providers>
    <add connectionStringName="DefaultConnection" applicationName="/" name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
  </providers>
</roleManager>

My LINQ query is like this:

var users = (from u in db.Users
            join m in db.Memberships on u.UserId equals m.UserId
            select new 
            {
               m.Email,
               u.UserName,
               u.UserId,
               m.IsLockedOut,
               Roles = Roles.GetRolesForUser(u.UserName) //doesn't work   
             }).ToList();

The problem is that Roles = Roles.GetRolesForUser(u.UserName) doesn't work as class Roles hasn't any method GetRolesForUser()

I can't even make my query using UsersInRoles as this table is not mapped like you can see below.

Answer 1

I have seen that before very much, as I know when you want to get data using LINQ and use another LINQ, it dose not work.

the best way for this issue, is that you have to get all Roles before this Query(When your data saved in memory, you can get those data as easy) or make that method to Awaitable like this:

With Memory:

var allRoles = _roleMaganer.GetAllRoles();

var users = (from u in db.Users
        join m in db.Memberships on u.UserId equals m.UserId
        select new 
        {
           m.Email,
           u.UserName,
           u.UserId,
           m.IsLockedOut,
           Roles = allRoles.FirstOrDefault(x=>x.UserName == u.UserName)    
         }).ToList();

With Awaitable:

var users = (from u in db.Users
        join m in db.Memberships on u.UserId equals m.UserId
        select new 
        {
           m.Email,
           u.UserName,
           u.UserId,
           m.IsLockedOut,
           Roles = await Roles.GetRolesForUser(u.UserName)   
         }).ToList();

When the GetRolesForUser was Async or MultyTask when your base Query want to execute, and you don't used of await key, exception occured, because the EF can't make other a connection in one Query