Delete elements of nested collections in Entity Framework

Question

I am using Entity Framework to store objects of the following entity classes:

public class Library
{
    public int Id { get; set; }

    private ICollection<Book> _books;
    public virtual ICollection<Book> Books => _books ?? (_books = new List<Book>());
}

public class Book
{
    public int Id { get; set; }

    public int LibraryId { get; set; }
    public virtual Library Library { get; set; }

    private ICollection<Page> _pages;
    public virtual ICollection<Page> Pages => _pages ?? (_pages = new List<Page>());
}

public class Page
{
    public int Id { get; set; }

    public int BookId { get; set; }
    public virtual Book Book { get; set; }
}

I want to be able to remove individual pages and books from the corresponding collections, so I do the following configuration with the fluent API:

modelBuilder.Entity<Library>()
    .HasMany(library => library.Books)
    .WithOptional()
    .HasForeignKey(book => book.LibraryId);
modelBuilder.Entity<Book>()
    .HasKey(book => new { book.Id, book.LibraryId })
    .Property(book => book.Id)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

modelBuilder.Entity<Book>()
    .HasMany(book => book.Pages)
    .WithOptional()
    .HasForeignKey(page => page.BookId);
modelBuilder.Entity<Page>()
    .HasKey(page => new { page.Id, page.BookId })
    .Property(page => page.Id)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

I create composite keys for Book and Page and set up the one-to-many relationships.

When I try to create a migration I get the following error:

Book_Pages_Source_Book_Pages_Target: : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical.

I suspect the error is on the foreign key of Page, which should probably include LibraryId, since it is part of the PK of Book... How should I fix the configuration?

Perhaps your FK on page is wrong as it references only a part of `Book`'s PK? https://stackoverflow.com/a/11755058/7034621 — orhtej2, Oct 06 '17 at 19:46
There are many flaws in this model. First, the `Identity` columns are already unique, so there is absolutely no need to create composite PKs. Second, both the `.WithOptional()` mappings are wrong - the FKs are `int`, hence **required**, and also the corresponding navigation properties are not mapped. — Ivan Stoev, Oct 06 '17 at 20:05

score 0 · Accepted Answer · answered Oct 07 '17 at 07:27

As orhtej2 points out in the comment above the FK relationship on Page missed LibraryId, since this value also part of Book's PK. I have moved away from the fluent API and used data annotations instead. The following entity classes are doing what I needed:

public class Library
{
    [Key]
    public int Id { get; set; }

    private ICollection<Book> _books;
    public virtual ICollection<Book> Books => _books ?? (_books = new List<Book>());
}       

public class Book
{   
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Key, Column(Order = 1), ForeignKey("Library")]
    public int LibraryId { get; set; }
    public virtual Library Library { get; set; }

    private ICollection<Page> _pages;
    public virtual ICollection<Page> Pages => _pages ?? (_pages = new List<Page>());
}

public class Page
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Key, Column(Order = 1), ForeignKey("Book")]
    public int BookId { get; set; }
    [Key, Column(Order = 2), ForeignKey("Book")]
    public int LibraryId { get; set; }
    public virtual Book Book { get; set; }
}

The composite keys in Book and Page are needed to make sure that when I, for example, remove an item from the Pages collection of one book, not only is the relationship between Page and Book deleted, but also the record that stores the page is actually deleted from the database (as explained in this answer).

Delete elements of nested collections in Entity Framework

1 Answers1