How to write many to many query in Entity Framework with db first approach?

Question

Database model:

Create table Author
(
    AuthorID int primary key identity,
    Name nvarchar(50)
)

Create table Book
(
    BookID int primary key identity,
    Name nvarchar(50),
    PageCount int
)

Create table AuthorBook
(
    AuthorID int not null foreign key references Author(AuthorID),
    BookID int not null foreign key references Book(BookID)
)

OnModel Creating:

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public virtual DbSet<Author> Author { get; set; }
        public virtual DbSet<AuthorBook> AuthorBook { get; set; }
        public virtual DbSet<Book> Book { get; set; }
        public virtual DbSet<BookGenre> BookGenre { get; set; }
        public virtual DbSet<Genre> Genre { get; set; }
        public virtual DbSet<MyBook> MyBook { get; set; }
        public virtual DbSet<Person> Person { get; set; }
    }

There are many to many relationship in book and author tables with the autor_book junction table. How do I get books with the authors?

using (MyLibraryEntities db = new MyLibraryEntities())
{
    var query = db.Book.SelectMany(book => db.Author, (book, author) =>
            new {
                bookID = book.BookID,
                name = book.Name,
                pageCount = book.PageCount,
                authorId = author.AuthorID,
                authorName = author.Name
            }).ToList<dynamic>();
}

I expect to get the list like

books = [{"name": book name, "pageCount": 233, authors = {"Carl", "Mike"}, ....].

But the actual output is;

[{"name": book name", .. author: "Carl"}, {"name": book, ... author: "Mike"]

Selim Yildiz · Accepted Answer · 2020-11-03T19:15:08.153

0

First you should create a relation between Book and Author in Model. Check here

Once you create relation, now Book entity has Collection of Author. Then query simple as:

using (MyLibraryEntities db = new MyLibraryEntities())
{
    var query = db.Book.Select(book =>
            new {
                bookID = book.BookID,
                name = book.Name,
                pageCount = book.PageCount,
                authors = book.Authors.Select(i=>i.Name).ToList()
            }).ToList<dynamic>();
}

edited Nov 03 '20 at 19:15

answered Sep 07 '19 at 21:24

Selim Yildiz

5,254
6
18
28

Ado.net entity model does not create only two entity within their collections, it also includes the bridge table. What I'm doing wrong? I've added the SQL query for the details. – GONCA ŞİLİK Sep 07 '19 at 21:38
Yes, added to question. – GONCA ŞİLİK Sep 07 '19 at 21:50
I still get "Book does not contain the definition for Author". Which seems normal since Book class only initialize this.AuthorBook = new HashSet(); in its constructor. – GONCA ŞİLİK Sep 07 '19 at 21:59
@GONCAŞİLİK could you please add your Book, Author and AuthorBook entities to question. – Selim Yildiz Sep 07 '19 at 22:02
System does not allow me to add the whole code. But I think problem is related with wrong relationship. Auto model generation does not create author relation for book, it just add author book. Do I need to add association between book and author with the many multiplicity and delete the author book? What is the cleanest and safest way to generate many to many relationship in database first approach? – GONCA ŞİLİK Sep 07 '19 at 22:15
Have a look at: https://stackoverflow.com/questions/35527175/entity-framework-database-first-many-to-many – Selim Yildiz Sep 07 '19 at 22:19
Thank you. I just deleted the unique id of bridge tables and get multiplicity but with a warning. However, I don't get the authors rather I get the count of the authors. authors = Count = 1 – GONCA ŞİLİK Sep 07 '19 at 22:46

How to write many to many query in Entity Framework with db first approach?

1 Answers1