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

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:

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

@A.J.Bauer so your issue is that you cannot see links between objects in code? — Karolis Kajenas
– Karolis Kajenas, Commented May 3, 2017 at 8:47

Karolis Kajenas · Accepted Answer · 2022-03-14 19:41:58Z

8

UPDATE

Its possible now by adding UseLazyLoadingProxies():

.AddDbContext<BloggingContext>(
    b => b.UseLazyLoadingProxies()
          .UseSqlServer(myConnectionString));

Automatic Lazy Loading is now possible: learn.microsoft.com/en-us/ef/core/querying/related-data/lazy by @Johannes Buchholz

ORIGINAL 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.

edited Mar 14, 2022 at 19:41

answered May 3, 2017 at 8:52

Karolis Kajenas

1,5431 gold badge15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

A-J-Bauer Over a year ago

Thank you, according to the Docs (thank you Ivan) Lazy loading is not yet possible with EF Core.

Karolis Kajenas Over a year ago

@A.J.Bauer No problem. You can mark this as answer if it solved your issue.

A-J-Bauer Over a year ago

I did, and posted an answer to my own question for completeness sake.

Johannes Buchholz Over a year ago

Automatic Lazy Loading is now possible: learn.microsoft.com/en-us/ef/core/querying/related-data/lazy

A-J-Bauer · Accepted Answer · 2017-05-03 09:56:55Z

Ok Ivan Stoev pointed me in the right direction: Loading Related Data

According to the Tip on Eager loading:

Entity Framework Core will automatically fix-up navigation properties to any other entities that were previously loaded into the context instance. So even if you don't explicitly include the data for a navigation property, the property may still be populated if some or all of the related entities were previously loaded.

So it is enough to put var eagerLoading = db.AuthorBook.Include(x => x.Author).Include(x => x.Book).ToList(); in front of the loop within the context (notice you have to use the join table in order to include both, Author and Book, also ToList() is necessary)

var eagerLoading = db.AuthorBook.Include(x => x.Author).Include(x => x.Book).ToList();

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

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related