Entity Framework Query Many to Many

Question

I’m starting to use the Entity Framework and I’m looking to make a query between two tables, Livroes and Autors, and their relationship is like Many to Many. I don’t know if it’s relevant but I’m using the method Code First, then created the classes below.

AUTHORS:

class Autor {
    public long Id { get; set; }
    public string Nome { get; set; }
    public ICollection<Livro> Livros { get; set; }
}

BOOKS:

class Livro {
    public long Id { get; set; }
    public string Nome { get; set; }

    [Column("EditoraId")]
    public long EditoraId { get; set; }

    public ICollection<Autor> Autores { get; set; }
}

I will not put the publishing class because I know how to access it and would get too big the question, if necessary edit and put later. Down with my class Context

class EFContext : DbContext {
    public DbSet<Editora> Editoras { get; set; }
    public DbSet<Livro> Livros { get; set; }
    public DbSet<Autor> Autores { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Entity<Autor>()
       .HasMany(autor => autor.Livros)
       .WithMany(livro => livro.Autores)
       .Map(x => {
           x.ToTable("livros_e_autores");
           x.MapLeftKey("autor_id");
           x.MapRightKey("livro_id");
       });
    }

    public EFContext() {
        DropCreateDatabaseIfModelChanges<EFContext> initializer = new DropCreateDatabaseIfModelChanges<EFContext>();
        Database.SetInitializer(initializer);
    }
}

When I run the program due to this relationship Many to Many a new table is created, called book_e_authors, as shown in the image below.

The problem is that I don’t know how to access this new table that was created, I tried to make a foreach within a foreach (code below) passing the ID of the Book but the result is wrong, returning all authors for each book.

public void ListaTodosLivroAutores() {
        using (EFContext ctx = new EFContext()) {
            var retornoLivro = from liv in ctx.Livros
                        select liv;
            foreach(var itemLiv in retornoLivro) {
                Console.WriteLine("\nCódigo Livro: " + itemLiv.Id
                                      + "\nNome Livro: " + itemLiv.Nome);

                var retornoAutor = from aut in ctx.Autores
                            where ctx.Livros.Any(l => l.Id == itemLiv.Id)
                            select aut;
                foreach(var itemAut in retornoAutor) {
                    Console.WriteLine("     Autor: " + itemAut.Nome);
                }
            }
        }
    }

My question is, is there any way to access this table created by the relationship? If so, how? The query you wanted would be something like this Select down below.

SELECT liv.Id "Código Livro",
       liv.Nome "Nome do Livro",
       aut.Nome "Autores"
  FROM dbo.livros_e_autores lea
  INNER JOIN dbo.Livroes liv ON liv.Id = lea.livro_id
  INNER JOIN dbo.Autors aut ON aut.Id = lea.autor_id

Answer 1

You do not need to access this table, just make the query:

var resultado = ctx.Livros.Include(l=> l.Autores).ToList();

or

var resultado = ctx.Livros.Include("Autores").ToList();

For printing, for example:

foreach (Livro l in resultado)
{
    Console.WriteLine("Código Livro: " + l.Id);
    Console.WriteLine("Nome: " + l.Nome);

    foreach (Autor a in l.Autores)
        Console.WriteLine("Autor: " + a.Nome);    

    Console.WriteLine("---------------");

}