如何将两个联接表中的IQueryable LINQ结果返回到List< string>中? [英] How to return IQueryable LINQ result from two joined tables into a List<string>?

问题描述

这是在此处提出的一个附加问题: Entity Framework Core 5.0如何将LINQ转换为多对多联接使用Intersection表获取ASP.NET成员身份

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

如何将以下两个LINQ IQueryable 结果的结果返回，该结果来自两个联接表，用于 RoleName 列到 List< string>; ?

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 })
                );

更新1

我需要值数组形式的 List ，以便可以使用 Contains()方法.我需要搜索分配给 UserId 的特定 RoleNames .如果我在 IQueryable 上使用 ToList()，则数组结果的形式为:

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 = "admin"}

{角色名称=用户"}

{ Rolename = "user"}

我无法使用 .Contains()方法，因为出现以下错误:

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

无法从'string'转换为< 匿名类型:字符串RoleName > .

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.

更新2

我需要列表形式的 queryResult :

{"admin"}

{ "admin"}

{用户"}

使用此输出，我可以使用 .Contains()方法执行多次检查.这用于确定 Windows窗体字段属性.因此，如果 UserId 属于 admin 角色，则该表单启用某些复选框和单选按钮，而如果 UserId 属于用户角色，则该表单会启用不同的复选框.这不是可用角色的详尽列表以及由表单执行的检查.但是，重要的是，需要在单独的IF语句中对 List 进行多次检查.

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.

当前，我可以使用 queryResult 执行以下操作:

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

1. 获取角色名称
的列表
2. 通过检查特定的 RoleName
在 queryResult 上执行单独的LINQ查询
3. 执行 .Count()>0 ，以查看 UserId 是否在特定角色中.

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.

这似乎很丑陋，因为我有一个中间步骤，即创建 1 + N 变量以通过LINQ进行检索，并存储每个 RoleName ，然后检查以查看如果 .Count()大于零.我认为 List 方法将更干净，更高效.如果可能的话.

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 ...

推荐答案

简短答案:
仅选择RoleName，然后使用SelectMany代替Select

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

更好的答案

因此，您有一个 Roles 表和一个 Users 表(我在简化您的长标识符，这不是问题的一部分，而且键入太多)

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).

Roles 和 Users 之间似乎存在多对多的关系:每个 Role 都是零个或多个的角色.用户，每个 User 具有零个或多个角色.

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.

使用标准联结表 UsersInRoles 实现此多对多关系.此联结表具有两个外键:一个用于用户，一个用于角色.

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.

您有一个UserId，看来您想要具有此ID的用户的所有角色的所有名称.

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

如何?

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));

换句话说:在UsersInRoles表中，仅保留那些属性UserId的值等于userId的UsersInRoles.

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

从其余所有UsersInRoles中，选择所有具有RoleId使其与UserInRole.RoleId成为一体的角色.从这些角色中选择RoleName.

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.

我使用 SelectMany 来确保获得一个字符串序列，而不是一个字符串序列.

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

如果您怀疑有两个RoleName，请考虑在末尾附加 Distinct().

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);

这篇关于如何将两个联接表中的IQueryable LINQ结果返回到List< string>中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！