Joins allow developers to combine data from multiple tables into a sigle query.
Let's have a look at codes:
Creating a project
- Create a project named JoinTest
- Add Packages by NuGet
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; } }
- Execute commands:
- Enable-Migrations
- Add-Migration init
- Update-Database
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.