// @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; }

    }

}