时间戳

首页 > 解决方案 > 使用实体框架过滤子记录

c# - 使用实体框架过滤子记录

问题描述

我有两个班级Author并Book有关系：

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Book> Books { get; set; }
}

public class Book
{
    public int Id { get; set; }
    public int AuthorId { get; set; }
    public string BookName { get; set; }
}

DbContext：

public class LibraryDBContext : DbContext
{
    public DbSet<Author> Authors { get; set; }
    public DbSet<Book> Books { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.\SQLEXPRESS;Database=SchoolDB;Trusted_Connection=True;");
    }

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

        modelBuilder.Entity<Author>()
               .HasMany(obj => obj.Books)
               .WithOne()
               .HasForeignKey(obj => obj.AuthorId)
               .OnDelete(DeleteBehavior.Cascade)
               .IsRequired();
    }
}

编写逻辑的主要方法：

class Program
{
    static void Main(string[] args)
    {
        var book1 = new Book() { AuthorId = 1, BookName = "a" };
        var book2 = new Book() { AuthorId = 1, BookName = "b" };
        var book3 = new Book() { AuthorId = 1, BookName = "c" };

        var author = new Author() { Name ="a" };

        var mydbcontext = new LibraryDBContext();
        mydbcontext.Add(author);
        mydbcontext.Add(book1);
        mydbcontext.Add(book2);
        mydbcontext.Add(book3);
        mydbcontext.SaveChanges();

        // Here I am trying to get Author 1 with BookName b
        var result = mydbcontext.Authors
                                .Include(d => d.Books)
                                .Where(d => d.Id == 1 && 
                                            d.Books.Any(b => b.BookName.Equals("b")))
                                .AsNoTracking()
                                .ToList();

        Console.WriteLine(JsonConvert.SerializeObject(result));
    }
}

Book输出：即使我过滤了，我也得到了所有记录BookName b

[
    {
        "Id": 1,
        "Name": "a",
        "Books": [
            {
                "Id": 1,
                "AuthorId": 1,
                "BookName": "a"
            },
            {
                "Id": 2,
                "AuthorId": 1,
                "BookName": "b"
            },
            {
                "Id": 3,
                "AuthorId": 1,
                "BookName": "c"
            }
        ]
    }
]

为什么所有的书都被退回而不是只有一本带有BookName = b？

标签： c#entity-framework

解决方案

.Where(d => d.Id == 1 && d.Books.Any(b=>b.BookName.Equals("b")))

上述条件检查作者的任何Author Id == 1书籍是否被命名。它不会按作者过滤具有所需名称的书籍。b

这对于作者来说是正确的，Author ID == 1因为他的三本书中的一本书具有所需的名称并且满足条件。

你需要的是

mydbcontext.Books.Where(d => d.AuthorId== 1 && d.BookName.Equals("b"))

以上将过滤拥有AuthorId=1和拥有的书籍BookName==b

根据评论更新

要包含作者详细信息，您可以将查询修改为

var result = mydbcontext.Authors
                        .Include(d => d.Books)
                        .Where(d => d.AuthorId== 1)
                        .Select(x=> 
                           new Author
                          { 
                              Id=x.Id,
                              Name=x.Name,
                              Books = x.Where(c=>c.BookName.Equals("b"))
                           });

推荐阅读