public class Place { public int Id { set; get; } public string Name { set; get; } public virtual ICollection<Person> Personnel { set; get; } } public class Person { public int Id { set; get; } public string FirstName { set; get; } public string LastName { set; get; } [ForeignKey("BirthPlaceId")] public virtual Place BirthPlace { set; get; } public int BirthPlaceId { set; get; } [ForeignKey("IssuanceLocationId")] public virtual Place IssuanceLocation { set; get; } public int? IssuanceLocationId { set; get; } }
public class MyContext : DbContext { public DbSet<Place> Places { get; set; } public DbSet<Person> Personnel { get; set; } public MyContext() { this.Database.Log = sql => Console.WriteLine(sql); } }
context.Personnel.Include(x => x.IssuanceLocation)
SELECT [Extent1].[Id] AS [Id], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[BirthPlaceId] AS [BirthPlaceId], [Extent1].[IssuanceLocationId] AS [IssuanceLocationId], [Extent2].[Id] AS [Id1], [Extent2].[Name] AS [Name], [Extent1].[Place_Id] AS [Place_Id] FROM [dbo].[People] AS [Extent1] LEFT OUTER JOIN [dbo].[Places] AS [Extent2] ON [Extent1].[IssuanceLocationId] = [Extent2].[Id]
و خروجی کوئری زیر که DefaultIfEmpty را هم لحاظ کرده و join نویسی صریحی هم دارد (مطابق مقاله فوق):
var query = from personnel in context.Personnel join issuanceLocation in context.Places on personnel.IssuanceLocationId equals issuanceLocation.Id into aIssuanceLocation from IL in aIssuanceLocation.DefaultIfEmpty() join birthLocation in context.Places on personnel.BirthPlaceId equals birthLocation.Id into aBirthLocation from BL in aBirthLocation.DefaultIfEmpty() select new { personnel.Id, personnel.FirstName, personnel.LastName, IssuanceLocation = IL.Name, BirthLocation = BL.Name };
SELECT [Extent1].[Id] AS [Id], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent2].[Name] AS [Name], [Extent3].[Name] AS [Name1] FROM [dbo].[People] AS [Extent1] LEFT OUTER JOIN [dbo].[Places] AS [Extent2] ON [Extent1].[IssuanceLocationId] = [Extent2].[Id] INNER JOIN [dbo].[Places] AS [Extent3] ON [Extent1].[BirthPlaceId] = [Extent3].[Id]
حتی همین حالت دوم را هم با کوئری ذیل که از خواص راهبری استفاده کرده، میتوان تولید کرد:
var query = context.Personnel.Select(x => new { x.Id, x.FirstName, x.LastName, BirthPlaceName = x.BirthPlace.Name, IssuanceLocationName = x.IssuanceLocation == null ? "" : x.IssuanceLocation.Name });
SELECT [Extent1].[Id] AS [Id], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent2].[Name] AS [Name], CASE WHEN ([Extent3].[Id] IS NULL) THEN N'' ELSE [Extent3].[Name] END AS [C1] FROM [dbo].[People] AS [Extent1] INNER JOIN [dbo].[Places] AS [Extent2] ON [Extent1].[BirthPlaceId] = [Extent2].[Id] LEFT OUTER JOIN [dbo].[Places] AS [Extent3] ON [Extent1].[IssuanceLocationId] = [Extent3].[Id]