c#之EF Core之不加载所有子实体

我有 3 个实体:

public partial class Category 
{ 
    public int CategoryID { get; set; } 
    public string? CategoryName { get; set; } 
 
    public ICollection<BookCategory> BookCategory { get; set; } 
} 
 
public partial class BookCategory 
{ 
    public int CategoryID { get; set; } 
    public int BookID { get; set; } 
 
    public Category Category { get; set; } 
    public Book Book { get; set; } 
} 
 
public partial class Book 
{ 
    public int BookID { get; set; } 
    public string? Title { get; set; } 
} 

我希望返回一个 Category 的数组，以及一个 BookCategory 的子数组，它与一本书一对一。

使用这样的调用；

public async Task<List<Category>> test() 
{ 
    var query = dbContext.Category 
                .Include(p => p.BookCategory)  
                .ThenInclude(pc => pc.Book) 
                .OrderBy(p => p.CategoryID) as IQueryable<Category>; 
 
    var data = await query.ToListAsync(); 
 
    return data; 
} 

我有这样的虚拟数据:

insert into kiosk.Category (CategoryID, CategoryName) values (1, 'Horror') 
insert into kiosk.Category (CategoryID, CategoryName) values (2, 'Fantasy') 
 
insert into kiosk.Book (BookID, Title) values (1, 'Space shooty') 
insert into kiosk.Book (BookID, Title) values (2, 'Elf shooty') 
 
insert into kiosk.BookCategory (BookID, CategoryID) values (1, 2) 
insert into kiosk.BookCategory (BookID, CategoryID) values (2, 2) 

但是，我收到的针对此请求的响应只为我提供了类别 2 的一条记录，而不是预期的两条。

[ 
    { 
        "categoryID": 1, 
        "categoryName": "Horror", 
        "bookCategory": [] 
    }, 
    { 
        "categoryID": 2, 
        "categoryName": "Fantasy", 
        "bookCategory": [ 
            { 
                "categoryID": 2, 
                "bookID": 1, 
                "book": { 
                    "bookID": 1, 
                    "title": "Space shooty" 
                } 
            } 
        ] 
    } 
] 

数据库上下文:

    public class BooksDbContext : DbContext 
    { 
 
        public DbSet<Book> book { get; set; }  
        public DbSet<Category> Category { get; set; }  
        public DbSet<BookCategory> BookCategory { get; set; }  
 
        public BooksDbContext() 
        { } 
 
        public BooksDbContext(DbContextOptions options) : base(options) 
        { } 
 
        public BooksDbContext(string connectionString) 
        { 
            this.connectionString = connectionString; 
        } 
 
        protected override void OnModelCreating(ModelBuilder modelBuilder) 
        { 
            base.OnModelCreating(modelBuilder); 
 
            modelBuilder.HasDefaultSchema("dbo"); 
 
            modelBuilder.ApplyConfiguration(new BookCategoryConfiguration()); 
            modelBuilder.ApplyConfiguration(new CategoryTestConfiguration()); 
            modelBuilder.ApplyConfiguration(new BookConfiguration()); 
 
        } 
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
        { 
            base.OnConfiguring(optionsBuilder); 
            if (!string.IsNullOrWhiteSpace(connectionString)) 
            { 
                optionsBuilder.UseSqlServer(connectionString); 
            } 
        } 
    } 

三种配置如下

    public class BookCategoryConfiguration : IEntityTypeConfiguration<BookCategory> 
    { 
        public void Configure(EntityTypeBuilder<BookCategory> builder) 
        { 
            builder.ToTable("BookCategory"); 
            builder.HasKey(x => x.BookID); 
            builder.HasKey(x => x.CategoryID);          
 
            builder.HasOne(a => a.Category).WithMany(b => b.BookCategory).HasForeignKey(c => c.CategoryID); // FK_POSCategoryProduct_POSCategory 
        } 
    } 
 
 
    public class CategoryTestConfiguration : IEntityTypeConfiguration<Category> 
    { 
        public void Configure(EntityTypeBuilder<Category> builder) 
        { 
            builder.ToTable("Category"); 
            builder.HasKey(x => x.CategoryID); 
 
            builder.Property(x => x.CategoryID).HasColumnName(@"CategoryID").HasColumnType("int").IsRequired(); 
            builder.Property(x => x.CategoryName).HasColumnName(@"CategoryName").HasColumnType("nvarchar").HasMaxLength(50);           
 
        } 
    } 
 
 
    public class BookConfiguration : IEntityTypeConfiguration<Book> 
    { 
        public void Configure(EntityTypeBuilder<Book> builder) 
        { 
            builder.ToTable("Book"); 
            builder.HasKey(x => x.BookID); 
 
            builder.Property(x => x.BookID).HasColumnName(@"BookID").HasColumnType("int").IsRequired(); 
            builder.Property(x => x.Title).HasColumnName(@"Title").HasColumnType("nvarchar").HasMaxLength(50); 
        } 
    } 

请您参考如下方法:

该模型通过显式连接实体表示标准的多对多关系。

问题是连接实体键的流畅映射:

builder.HasKey(x => x.BookID); 
builder.HasKey(x => x.CategoryID); 

HasKey 方法(即使不是所有的 Fluent API，也是大多数)不是可加的。后来的电话获胜(取代以前的电话)。

上述代码的效果是 EF Core 将 CategoryID 视为 BookCategory 表的唯一键，因此仅加载 1每个 CategoryID 记录。

当然，我们的想法是定义连接实体的标准复合主键。这是通过使用匿名类型(在 EF Core Keys 文档主题的复合键部分示例中显示)实现的(类似于所有允许多个属性的流畅 API)。

将它应用到您的场景中会将上面的两行替换为

builder.HasKey(x => new { x.BookID, x.CategoryID });