Entity Framework Core Many-to-Many Relationship not loading from database

c# entity-framework-core many-to-many

Question

Ok, so I followed the 'Many-to-many' example for Entity Framework Core: Relationships (at the end of the page).

It seems that when the database is initially created (createNew set to true) everything is working as expected but when I use an existing database (createNew set to false) the joins are no longer there. Following is a Console App showing the problem that I'm having:

Output for createNew = true:

Author 1 has written: - Book 1 - Book 2

Author 2 has written: - Book 1 - Book 2

Author 3 has written: - Book 4

Auhtor 4 has written: no books yet

Output for createNew = false:

Author 1 has written: no books yet

Author 2 has written: no books yet

Author 3 has written: no books yet

Auhtor 4 has written: no books yet

// Visual Studio 2017
// Console App (.NET Core)
// Nuget: Microsoft.EntityFrameworkCore.SqlServer

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace ConsoleAppCoreEFMany
{
    /* 
    An Author can write several Books, and a Book can be written by several Authors..
    https://en.wikipedia.org/wiki/Many-to-many_(data_model)
    */

    class Author
    {
        public long AuthorId { get; set; }
        public string Name { get; set; }

        public List<AuthorBook> AuthorBook { get; set; }        
    }

    class Book
    {
        public long BookId { get; set; }
        public string Title { get; set; }

        public List<AuthorBook> AuthorBook { get; set; }
    }   

    class AuthorBook
    {
        public long AuthorId { get; set; }
        public Author Author { get; set; }

        public long BookId { get; set; }
        public Book Book { get; set; }
    }  

    class MyDbContext : DbContext
    {        
        public DbSet<Author> Author { get; set; }
        public DbSet<Book> Book { get; set; }        
        public DbSet<AuthorBook> AuthorBook { get; set; }        

        string connectionString = "";

        public MyDbContext(string connectionString, bool createNew)
        {
            this.connectionString = connectionString;

            if (createNew)
            {
                Database.EnsureDeleted();
                Database.EnsureCreated();
            }

            // to see the actual tables created add a Data Connection in View / Server Explorer, Data Source: (Microsoft SQL Server Database File), Browse: C:/Users/<user>/*.mdf (in this case TestEFManyMany.mdf)
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connectionString);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AuthorBook>()
                .HasKey(x => new { x.AuthorId, x.BookId }); // creates a composite key

            modelBuilder.Entity<AuthorBook>()
                .HasOne(x => x.Author)
                .WithMany(x => x.AuthorBook)
                .HasForeignKey(x => x.AuthorId);

            modelBuilder.Entity<AuthorBook>()
                .HasOne(x => x.Book)
                .WithMany(x => x.AuthorBook)
                .HasForeignKey(x => x.BookId);           
        }
    }

    class Program
    {


        static void Main(string[] args)
        {
            bool createNew = false;

            using (var db = new MyDbContext("Server=(localdb)\\mssqllocaldb;Database=TestEFManyMany;Trusted_Connection=True;MultipleActiveResultSets=True", createNew))
            {
                try
                {
                    if (createNew)
                    {
                        // creating some 'unlinked' books
                        var book1 = new Book { Title = "Book 1" };
                        var book2 = new Book { Title = "Book 2" };
                        var book3 = new Book { Title = "Book 3" };

                        db.Book.Add(book1);
                        db.Book.Add(book2);
                        db.Book.Add(book3);
                        db.SaveChanges();

                        // creating some 'unlinked' authors
                        var author1 = new Author { Name = "Author 1" };
                        var author2 = new Author { Name = "Author 2" };

                        db.Author.Add(author1);
                        db.Author.Add(author2);
                        db.SaveChanges();

                        // link authors and books

                        // Author 1 and Author 2 have written Book 1
                        db.AuthorBook.Add(new AuthorBook { AuthorId = author1.AuthorId, BookId = book1.BookId });
                        db.AuthorBook.Add(new AuthorBook { AuthorId = author2.AuthorId, BookId = book1.BookId });

                        db.SaveChanges();

                        // Author 2 also has written Book 2
                        db.AuthorBook.Add(new AuthorBook { AuthorId = author2.AuthorId, BookId = book2.BookId });

                        // creating initially linked 
                        db.AuthorBook.Add(new AuthorBook
                        {
                            Author = new Author { Name = "Author 3" },
                            Book = new Book { Title = "Book 4" }
                        });

                        db.SaveChanges();

                        // check if link between author 2 and book 1 exists
                        if (db.AuthorBook.Where(x => x.AuthorId == author2.AuthorId && x.BookId == book1.BookId).Count() == 0)
                        {
                            db.AuthorBook.Add(new AuthorBook { AuthorId = author2.AuthorId, BookId = book1.BookId });
                        }

                        // check if link between author 1 and book 2 exists
                        if (db.AuthorBook.Where(x => x.AuthorId == author1.AuthorId && x.BookId == book2.BookId).Count() == 0)
                        {
                            db.AuthorBook.Add(new AuthorBook { AuthorId = author1.AuthorId, BookId = book2.BookId });
                        }

                        db.SaveChanges();

                        var author4 = new Author { Name = "Auhtor 4" };
                        db.Author.Add(author4);
                        db.SaveChanges();                       
                    }


                    foreach (var author in db.Author)
                    {
                        Console.WriteLine(author.Name + " has written:");

                        if (author.AuthorBook != null)
                        {
                            var books = author.AuthorBook.Select(x => x.Book).ToList();

                            foreach (var book in books)
                            {
                                Console.WriteLine("- " + book.Title);
                            }                            
                        }
                        else
                        {
                            Console.WriteLine(" no books yet");
                        }

                        Console.WriteLine();
                    }                   
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }                
            }

            Console.ReadKey();
        }
    }
}

Data tables:

enter image description here

I sure hope I did something really wrong and it's not a bug..

1
0
5/3/2017 8:31:36 AM

Popular Answer

Latest EF does not support automatic lazy-loading. You can read more and track this task here.

EF team stated, that they have not yet decided whether automatic lazy-loading will be included in upcoming updates.

To answer your question you need to manually load relational data. To do so you might want to use Include and ThenInclude methods provided by EF Core.

Example:

 ctx.EntityOne
    .Include(eOne => eOne.EntityTwo)
    .ThenInclude(eTwo => eTwo.SomeOtherEntity)
    .Where(entityOne => YourQuery);

This code snipped will load relational links between these entities as it was done in previous EF versions automatically.

4
11/27/2019 4:20:56 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow