我如何执行连接两个实体的LINQ查询，并在其中一个实体上选择Where

本文关键字：实体 在其中 一个 Where 选择 查询 LINQ 连接 何执行 执行 两个 | 更新日期: 2023-09-27 17:59:01

我有这些类：

public partial class AspNetRole
{
    public AspNetRole()
    {
        AspNetUsers = new HashSet<AspNetUser>();
    }
    public string Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<AspNetUser> AspNetUsers { get; set; }
}
public partial class AspNetUser
{
    public AspNetUser()
    {
        AspNetUserClaims = new HashSet<AspNetUserClaim>();
        AspNetUserLogins = new HashSet<AspNetUserLogin>();
        AspNetRoles = new HashSet<AspNetRole>();
    }
    public string Id { get; set; }
    public string UserName { get; set; }
    public virtual ICollection<AspNetUserClaim> AspNetUserClaims { get; set; }
    public virtual ICollection<AspNetUserLogin> AspNetUserLogins { get; set; }
    public virtual ICollection<AspNetRole> AspNetRoles { get; set; }
}

实体框架创建这些表来保存数据：

CREATE TABLE [dbo].[AspNetRoles] (
    [Id]   NVARCHAR (128) NOT NULL,
    [Name] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                     NVARCHAR (128) NOT NULL,
    [UserName]               NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] NVARCHAR (128) NOT NULL,
    [RoleId] NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
    CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);

我想做的是获得AspNetUser.Id和AspNetUser.UserName的列表，其中用户在角色中的名称为"Admin"。我试过这个LINQ，但我对语法感到困惑：

        var result = await db.AspNetRoles
                             .Where(r => r.Name == "Admin")
                             .Select(e => new
                             {
                                 Id = e.AspNetUsers.??
                                 Name = e.AspNetUsers.??
                             .ToListAsync();

使用Intellisense，我似乎无法选择AspNetUsers上的属性。

我如何执行连接两个实体的LINQ查询，并在其中一个实体上选择Where

使用这个：

  var result = db.AspNetUsers.Where(t=>t.AspNetRoles.Any(r=>r.Name == "Admin") )
                                .Select(x=>x).ToList();

您需要这样的

var adminUsers = from user in db.AspNetUsers
                 join userRole in db.AspNetUserRoles on user.Id equals userRole.UserId
                 join role in db.AspNetRoles on userRole.RoleId equals role.Id
                 where role.Name == "Admin"
                 select user
相关文章：