How to return IQueryable LINQ result from two joined tables into a List<string>?

Question

This is an add-on question to one asked here: Entity Framework Core 5.0 How to convert LINQ for many-to-many join to use Intersection table for ASP.NET Membership

How can I return the results of an the following LINQ IQueryable result, which is from two join tables, for the RoleName column to a List<string>?

var queryResult = (this.DbContext.aspnet_UsersInRoles
                .Where(x => x.UserId == dpass.UserId)
                .Join(
                    this.DbContext.aspnet_Roles,
                    ur => ur.RoleId,
                    r => r.RoleId,
                    (ur, role) => new
                    {
                        ur,
                        role
                    }
                )
                .Select(x => new { x.ur.UserId, x.role.RoleName })
                );

UPDATE 1

I need the List in the form of an array of values so that I can use the Contains() method. I need to search for specific RoleNames assigned to a UserId. If I use ToList() on the IQueryable, then the array result is in the form of:

{ RoleName = "admin"}

{ Rolename = "user"}

I am unable to use the .Contains() method because I get the following error:

cannot convert from 'string' to <anonymous type: string RoleName>.

It seems be to expecting a class that the query result can be assigned to. But, one doesn't exist because I am doing this on-the-fly.

UPDATE 2

I need the queryResult in a List that is in the form of:

{ "admin"}

{ "user"}

With this output, I can use the .Contains() method to perform multiple checks. This is used for determining Windows Forms field properties. So, if the UserId belongs to the admin role then the form enables certain check boxes and radio buttons whereas if the UserId belongs to the user role then the form enables different check boxes. This is not an exhaustive list of roles available along with the checks that are performed by the form. But, what is important is that there are multiple checks on the List that need to be performed in separate IF statements.

Currently, I am able to use the queryResult to do the following:

1. Get a list of the RoleNames
2. Perform separate LINQ queries on the queryResult by checking for the specific RoleName
3. Perform a .Count() > 0 check to see if the UserId is in a specific role.

This seems like an ugly hack because I have the intermediate step of creating 1 + N variables to retrieve, by LINQ, and store each RoleName and then check to see if the .Count() is greater than zero. I think that the List method would be cleaner and more efficient. If that is possible.

var varUser = from d in queryResult
          where d.RoleName == "user"
          select new { d.RoleName };

var varAdmin = from u in queryResult
                where u.RoleName == "admin"
                select new { u.RoleName };

//... more declarations and LINQs ...

Answer 1

Try to use GroupBy(). Be careful, this method is not supported by direct IQueryable to SQL conversion. If you will try to call GroupBy() before .ToList(), it will throw an error.

In your example you could this: select a list in memory and then work with it:

var queryResult = (this.DbContext.aspnet_UsersInRoles
                       .Where(x => x.UserId == dpass.UserId)
                       .Join(this.DbContext.aspnet_Roles,
                                 ur => ur.RoleId,
                                 r => r.RoleId,
                                 (ur, role) => new { ur, role }
                                )
                       .Select(x => new { x.ur.UserId, x.role.RoleName })
                       .ToList()   // MATERIALIZE FIRST
                       .GroupBy(x => x.UserId)  //ADD THIS
                      );
        
queryResult.Contains(roleName=> roleName == "ROLE_TO_SEARCH")
var userId = queryResult.Key;

Answer 2

Short answer:
Select only the RoleName, and use SelectMany instead of Select

Better answer

So you have a table of Roles, and a table of Users (I'm simplifying your long identifiers, not part of the problem and way too much typing).

There seems to be a many to many relation between Roles and Users: Every Role is a role for zero or more Users, every User has zero or more Roles.

This many-to-many relation is implemented using a standard junction table: UsersInRoles. This junction table has two foreign keys: one to the User and one to the Roles.

You have a UserId, and it seems that you want all names of all Roles of the user that has this Id.

How about this:

int userId = ...

// Get the names of all Roles of the User with this Id
var namesOfRolesOfThisUser = dbContext.UsersInRoles

    // only the user with this Id:
    .Where(userInRole => userInRole.UserId == userId)

    // get the names of all Roles for this userInRole
    .SelectMany(userInRole => dbContext.Roles.Where(role => role.RoleId == userInRole.RoleId)
                                             .Select(role => role.RoleName));

In words: from the table of UsersInRoles, keep only those UsersInRoles that have a value for property UserId that equals userId.

From every one of the remaining UsersInRoles, select all Roles that have a RoleId that equeals the UserInRole.RoleId. From these Roles take the RoleName.

I use SelectMany to make sure that I get one sequence of strings, instead of a sequence of sequences of strings.

If you suspect double RoleNames, consider to append Distinct() at the end.

But I want to Join!

Some people really like to do the joins themselves.

int userId = ...
var namesOfRolesOfThisUser = dbContext.UsersInRoles
    .Where(userInRole => userInRole.UserId == userId)
    .Join(dbContext.Roles,

    userInRole => userInRole.RoleId,  // from every UserInRole take the foreign key
    role => role.RoleId,              // from every Role take the primary key

    // when they match, take only the name of the Role
    (userInRole, role) => role.RoleName);