CODEWITHSUNDEEP
c#entity-framework
Krishna Varma
Krishna Varma

Reputation: 4250

Filter child records using Entity Framework

I have two classes Author & Book with a relationship:

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

Main method where the logic is written:

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

Output: I am getting all records from Book even though I filtered 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"
            }
        ]
    }
]

Why are all the books returned instead of just the one with BookName = b?

Upvotes: 1

Views: 70

Answers (1)

Anu Viswan
Anu Viswan

Reputation: 18155

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

The above condition checks if the Author Id == 1 and if ANY of the books by the author is named b. It doesn't filter the books by the author, which has the required name.

This is true for Author where Author ID == 1 as one of his 3 books has the required name and condition is satisfied.

What you would require would be

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

The above would filter the books which has AuthorId=1 and has BookName==b

Update based on comment

To include the Author details, you could modify the query as

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

Upvotes: 3

Related Questions