c# - EF Core IdentityUser 与业务模型存在多对多关系，如何使用 .Include().ThenInclude()？

Question

实际上，我知道如何在常规 EF Core DB 上下文中创建和使用多对多关系，但在我目前的项目中，我决定将我的 Identity DB 上下文表迁移到我的 ApplicationDbContext，我将其余的业务表保留在其中：

public class ApplicationDbContext : IdentityDbContext<AppUser>
    {
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

        public DbSet<CompanyModel> Companies { get; set; }
        public DbSet<CompanyUser> CompaniesUsers { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            //many-to-many
            modelBuilder.Entity<CompanyUser>()
                .HasKey(c => new { c.CompanyId, c.UserId });

            /* do I realy need this at all? -> https://stackoverflow.com/a/54612387/12603542 
            modelBuilder.Entity<CompanyUser>()
                .HasOne(cu => cu.AppUser)
                .WithMany(au => au.CompaniesUsers)
                .HasForeignKey(cu => cu.UserId);

            modelBuilder.Entity<CompanyUser>()
                .HasOne(cu => cu.CompanyModel)
                .WithMany()
                .HasForeignKey(cu => cu.CompanyId);
                */
        }
    }

我有一个关系，很多用户可能有很多公司，所以我创建了 Company <-> AppUser 多对多桥：

public class CompanyUser
    {
        public int CompanyId { get; set; }
        public string UserId { get; set; }
        public AppUser AppUser { get; set; }
        public CompanyModel CompanyModel { get; set; }
    }

我的 AppUser 包含导航属性：

public class AppUser : IdentityUser
    {
        //(...)
        public ICollection<CompanyUser> CompaniesUsers { get; set; } //many-to-many
    }

我的 CompanyModel 也一样：

public class CompanyModel
    {
        [Key]
        public int CompanyId { get; set; }
        //(...)
        public ICollection<CompanyUser> CompaniesUsers { get; set; } //many-to-many
    }

一切似乎都很好，直到我不尝试使用相关公司 drom DB 检索用户，我的控制器在其中调用 DAL 方法：

AppUser userWithLists = _repositoryUser.GetUser(user.Id);

在我的存储库中，我尝试这样做：

public AppUser GetUser(string id)
        {
            return _context.Users
                .Include(u => u.CompaniesUsers)
                .ThenInclude(u => u.CompanyModel)
                .Where(u => u.Id == id)
                .FirstOrDefault();
        }

但不幸的是，在我的数据库上下文中执行 LINQ 查询时，我收到了一个异常：

SqlException：列名“AppUserId”无效。列名“AppUserId”无效。

注释掉 OnModelCreating 代码后，我得到了另一个异常：

SqlException：列名“CompanyModelCompanyId”无效。

桥接表T-SQL创建代码：

CREATE TABLE [dbo].[CompaniesUsers] (
    [CompanyId] INT            NOT NULL,
    [UserId]    NVARCHAR (450) NOT NULL,
    CONSTRAINT [PK_CompaniesUsers] PRIMARY KEY CLUSTERED ([CompanyId] ASC, [UserId] ASC),
    CONSTRAINT [FK_CompaniesUsers_Companies_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Companies] ([CompanyId]) ON DELETE CASCADE,
    CONSTRAINT [FK_CompaniesUsers_AppUser_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_CompaniesUsers_CompanyId]
    ON [dbo].[CompaniesUsers]([CompanyId] ASC);

标签： c#many-to-manyasp.net-identityef-core-2.2