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

會長發表於2017-05-24

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

圖片.png-2.4kB

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

圖片.png-3.1kB

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

That's all.

相關文章