c# - 如何执行连接两个实体的 LINQ 查询并在其中一个实体上选择什么位置？

Question

我有这些类(class):

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

Entity Framework 创建这些表来保存数据:

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 属性。

Answer 1

使用这个:

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