Lerning Entity Framework 6 ------ Joins and Left outer Joins

---

Joins allow developers to combine data from multiple tables into a sigle query.

Let's have a look at codes:

Creating a project

1. Create a project named JoinTest

2. Add Packages by NuGet

3. Create entities:

    public class Person
    {
        public int PersonId { get; set; }
   
        [MaxLength(50)]
        public string Name { get; set; }
   
        public virtual PersonType PersonType { get; set; }
    }
   
    public class PersonType
    {
        public int PersonTypeId { get; set; }
   
        public string PersonTypeName { get; set; }
    }
   
    public class MyContext:DbContext
    {
        public MyContext():base("name=Test")
        {
   
        }
   
        public DbSet<PersonType> PersonTypes { get; set; }
   
        public DbSet<Person> People { get; set; }
    }
   

4. Execute commands:

   • Enable-Migrations
   • Add-Migration init
   • Update-Database

5. Add some test data by coding:

    static void Main(string[] args)
    {
        AddTestData();
    }
   
    private static void AddTestData()
    {
        using (MyContext context = new MyContext())
        {
            PersonType student = new PersonType();
            student.PersonTypeName = "学生";
   
            PersonType worker = new PersonType();
            worker.PersonTypeName = "工人";
   
            Person p1 = new Person();
            p1.Name = "王进喜";
            p1.PersonType = worker;
   
            Person p2 = new Person();
            p2.Name = "*";
            p2.PersonType = student;
   
            Person p3 = new Person();
            p3.Name = "完颜亮";
   
            context.People.Add(p1);
            context.People.Add(p2);
            context.People.Add(p3);
            context.SaveChanges();
        }
    }
   

   }

using joins

static void Main(string[] args)
{
    //AddTestData();
    using (MyContext db = new MyContext())
    {
        var result = from p in db.People
                     join t in db.PersonTypes
                     on p.PersonType.PersonTypeId equals t.PersonTypeId
                     select new { Name = p.Name, Type = t.PersonTypeName };

        foreach (var item in result)
        {
            Console.WriteLine(item);
        }
    }

    Console.ReadLine();
}

using Left outer joins

static void Main(string[] args)
{
    //AddTestData();
    using (MyContext db = new MyContext())
    {
        var result = from p in db.People
                     join t in db.PersonTypes
                     on p.PersonType.PersonTypeId equals t.PersonTypeId into finalGroup
                     from groupData in finalGroup.DefaultIfEmpty()
                     select new { Name = p.Name, Type = groupData.PersonTypeName??"Unknown" };

        foreach (var item in result)
        {
            Console.WriteLine(item);
        }
    }

    Console.ReadLine();

I think this tructure is hard to understand, but it's useful.

That's all.