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

图片.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.

posted @ 2017-05-24 08:59 会长 阅读(...) 评论(...) 编辑 收藏