Entity Framework（EF）之Linq查询的left join实现

EF中多表联结查询只实现了inner join并没有实现left join，但是在实际的业务中需要用到left join的情况是很常见的。那么我们可以通过设置左表之外的表查询无记录时返回默认值即可。具体参见以下代码。

EF代码：

var order = (from a in context.Set<SonOrder>()
						 join bus in context.Set<BusinessUser>() on a.BusinessID equals bus.ID 
						 join c in context.Set<LogisticsInfo>() on a.LogisticsID equals c.LogisticsID into t from c in t.DefaultIfEmpty()
						 where a.SonOrderShow == Id
						 select new OrderDetailDTO()
						{
							Address = a.FatherOrder.ReceiveAddress,	//收获地址
							Name = a.FatherOrder.Name,	//收货人姓名
							Phone = a.FatherOrder.Phone,	//收货人电话
							OrderNum = a.SonOrderShow,	//订单号
							OrderStatus = (EnumOrderStatus)a.Status,	//订单状态
							BusinessName = bus.BussessName,		//商家名

							InsertTime = a.FatherOrder.CreateTime,	//订单提交时间
							PayTime = a.PayTime,	//支付时间
							SentTime = a.SendTime,	//发货时间
							ReceiveTime = a.ReceiveTime,	//收货时间
							CancelTime = a.CancelTime,	//取消订单时间
							LogisticsTypeID = c.LogisticsTypeID.ToString(),	//物流类型编号
							Province = c.Province,	//省份
							City = c.City,	//城市
							Area = c.Area,	//区
							LogisticsAddress = c.LogisticsAddress,	//物流地址
							Logistics = c.Logistics,	//
							LogisticsCode = c.LogisticsCode		//物流单号

						}).FirstOrDefault();

注：希望从表LogisticsInfo中返回的值如果存在返回具体指，不存在返回空即left join的结果，那么就设置该数据序列如果为空就返回单一实例集合中的类型参数的默认值。

生成的SQL语句如下

exec sp_executesql N'SELECT 
    [Limit1].[BusinessID] AS [BusinessID], 
    [Limit1].[ReceiveAddress] AS [ReceiveAddress], 
    [Limit1].[Name] AS [Name], 
    [Limit1].[Phone] AS [Phone], 
    [Limit1].[SonOrderShow] AS [SonOrderShow], 
    [Limit1].[Status] AS [Status], 
    [Limit1].[BussessName] AS [BussessName], 
    [Limit1].[CreateTime] AS [CreateTime], 
    [Limit1].[PayTime] AS [PayTime], 
    [Limit1].[SendTime] AS [SendTime], 
    [Limit1].[ReceiveTime] AS [ReceiveTime], 
    [Limit1].[CancelTime] AS [CancelTime], 
    [Limit1].[C1] AS [C1], 
    [Limit1].[Province] AS [Province], 
    [Limit1].[City] AS [City], 
    [Limit1].[Area] AS [Area], 
    [Limit1].[LogisticsAddress] AS [LogisticsAddress], 
    [Limit1].[Logistics] AS [Logistics], 
    [Limit1].[LogisticsCode] AS [LogisticsCode]
    FROM ( SELECT TOP (1) 
        [Extent1].[BusinessID] AS [BusinessID], 
        [Extent1].[SonOrderShow] AS [SonOrderShow], 
        [Extent1].[Status] AS [Status], 
        [Extent1].[PayTime] AS [PayTime], 
        [Extent1].[SendTime] AS [SendTime], 
        [Extent1].[ReceiveTime] AS [ReceiveTime], 
        [Extent1].[CancelTime] AS [CancelTime], 
        [Extent2].[BussessName] AS [BussessName], 
        [Extent4].[CreateTime] AS [CreateTime], 
        [Extent4].[ReceiveAddress] AS [ReceiveAddress], 
        [Extent4].[Phone] AS [Phone], 
        [Extent4].[Name] AS [Name], 
        CASE WHEN ([Extent3].[LogisticsTypeID] IS NULL) THEN N'''' ELSE  CAST( [Extent3].[LogisticsTypeID] AS nvarchar(max)) END AS [C1], 
        [Extent3].[Province] AS [Province], 
        [Extent3].[City] AS [City], 
        [Extent3].[Area] AS [Area], 
        [Extent3].[LogisticsAddress] AS [LogisticsAddress], 
        [Extent3].[Logistics] AS [Logistics], 
        [Extent3].[LogisticsCode] AS [LogisticsCode]
        FROM    [dbo].[SonOrder] AS [Extent1]
        INNER JOIN [dbo].[BusinessUser] AS [Extent2] ON [Extent1].[BusinessID] = [Extent2].[ID]
        LEFT OUTER JOIN [dbo].[LogisticsInfo] AS [Extent3] ON [Extent1].[LogisticsID] = [Extent3].[LogisticsID]
        INNER JOIN [dbo].[FatherOrder] AS [Extent4] ON [Extent1].[FatherOrderID] = [Extent4].[FatherOrderID]
        WHERE [Extent1].[SonOrderShow] = @p__linq__0
    )  AS [Limit1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'20170210165403680111702120'

可以看到，当进行如此设置以后，反映到SQL中就是left join。