EF - Joining (3 tables)

CSharp · Console

/Program.cs

// @nuget: EntityFramework
// @nuget: EntityFramework.SqlServerCompact
// @nuget: Microsoft.SqlServer.Compact
// @nuget: Z.EntityFramework.Extensions

using System;
using System.Data.Entity;
using System.Linq;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

public class Program
{
    public static void Main(string[] args)
    {
        InsertData();
        using (var dbContext = new BookStore())
        {
            var authorsData = dbContext.Authors
                .Join(
                    dbContext.AuthorBiographies,
                        author => author.AuthorId,
                        authorBio => authorBio.AuthorBiographyId,
                    (author, authorBio) => new
                    {
                        AuthorId = author.AuthorId,
                        Name = author.Name,
                        Biography = authorBio.Biography
                    }
                )
                .Join(
                    dbContext.Books,
                    author => author.AuthorId,
                    book => book.BookId,
                    (author, book) => new
                    {
                        AuthorId = author.AuthorId,
                        Name = author.Name,
                        Biography = author.Biography,
                        BookTitle = book.Title
                    }
                )
                .ToList();

            string format = "{0, -10}{1, -20}{2, -20}{3, -50}";
            Console.WriteLine(format, "AuthorId", "Name", "Biography", "Book");
            Console.WriteLine(format, "--------", "----", "---------", "----");

            foreach (var author in authorsData)
            {
                Console.WriteLine(format, author.AuthorId, author.Name, author.Biography, author.BookTitle);
            }
        }
    }

    public static void InsertData()
    {
        using (var context = new BookStore())
        {
            var Authors = new List<Author>
        {
            new Author()
            {
                Name = "Mark",
                Biography = new AuthorBiography()
                {
                    Biography = "Mark Biography",
                    DateOfBirth = DateTime.Now.AddYears(-35).AddDays(23).AddMonths(2),
                    PlaceOfBirth = "NY",
                    Nationality = "US"
                }
            },
            new Author()
            {
                Name = "Andy",
                Biography = new AuthorBiography()
                {
                    Biography = "Andy Biography",
                    DateOfBirth = DateTime.Now.AddYears(-40).AddDays(29).AddMonths(10),
                    PlaceOfBirth = "NY",
                    Nationality = "US"
                }
            },
            new Author()
            {
                Name = "Johny",
                Biography = new AuthorBiography()
                {
                    Biography = "Johny Biography",
                    DateOfBirth = DateTime.Now.AddYears(-31).AddDays(9).AddMonths(7),
                    PlaceOfBirth = "NY",
                    Nationality = "US"
                }
            }
        };

            var Books = new List<Book>
        {
            new Book() { Title = "Fundamentals of Computer Programming with C#", AuthorId = 1},
            new Book() { Title = "Java: A Beginner's Guide", AuthorId = 1},
            new Book() { Title = "SQL: The Ultimate Beginners Guide", AuthorId = 2},
            new Book() { Title = "Learn VB.NET", AuthorId = 3},
            new Book() { Title = "C# Fundamentals for Absolute Beginners", AuthorId = 3}
        };

            context.Authors.AddRange(Authors);

            context.Books.AddRange(Books);

            context.SaveChanges();

        }
    }
	
	public class BookStore : DbContext
    {
        public BookStore() : base(FiddleHelper.GetConnectionStringSqlServer())
        {
            
        }

        public DbSet<Author> Authors { get; set; }
        public DbSet<AuthorBiography> AuthorBiographies { get; set; }
        public DbSet<Book> Books { get; set; }
    }

    public class Book
    {
        public int BookId { get; set; }
        public string Title { get; set; }
        public int AuthorId { get; set; }
        [ForeignKey("AuthorId")]
        public Author Author { get; set; }
    }

    public class Author
    {
        public int AuthorId { get; set; }
        public string Name { get; set; }
        public virtual AuthorBiography Biography { get; set; }
    }

    public class AuthorBiography
    {
        [ForeignKey("Author")]
        public int AuthorBiographyId { get; set; }
        public string Biography { get; set; }
        public DateTime DateOfBirth { get; set; }
        public string PlaceOfBirth { get; set; }
        public string Nationality { get; set; }
        public virtual Author Author { get; set; }
    }
}