Reputation: 3957
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:
RoleNames
queryResult
by checking for the specific RoleName
.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 ...
Upvotes: 0
Views: 1970
Reputation: 2287
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;
Upvotes: 1
Reputation: 30512
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.
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);
Upvotes: 2