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。