Linq是否可以与Entity Framework进行外部联接 [英] Is an outer join possible with Linq to Entity Framework

问题描述

有很多使用Linq to Sql进行外部联接的示例，所有示例都依赖于DefaultIfEmpty()，而Linq to Entity Framework不支持.

这是否意味着使用.NET 3.5不能通过Linq到Entity进行外部联接(我知道DefaultIfEmpty随4.0一起提供---但是这对我来说不是一个选择)

有人可以提供一个使用Linq到EntityFramework的简洁示例.

解决方案

在LINQ to Entities中，从关系而非SQL连接的角度进行思考.因此，在实体Person上具有与CustomerInfo一对一或一个关系的SQL外部联接的字面等效为:

var q = from p in Context.People
        select new
        {
            Name = p.Name,
            IsPreferredCustomer = (bool?)p.CustomerInfo.IsPreferredCustomer
        };

L2E将合并联接，因此，如果CustomerInfo为null，则整个表达式的计算结果为null.因此，将其强制转换为可为null的布尔，因为推断出的不可为null的布尔无法容纳该结果.

对于一对多，通常需要层次结构，而不是简单的SQL风格的结果集:

var q = from o in Context.Orders
        select new 
        {
            OrderNo = o.OrderNo,
            PartNumbers = from od in o.OrderDetails
                          select od.PartNumber
        }

这就像是左联接，因为您仍然可以获得没有详细信息的订单，但这是一个像OO的图形，而不是像SQL这样的集合.

There are many examples of outer join using Linq to Sql, all of them hinging on DefaultIfEmpty() which is not supported with Linq to Entity Framework.

Does this mean that outer join is not possible with Linq to Entity using .NET 3.5 (I understand that DefaultIfEmpty is coming with 4.0 --- but that's not an option at this time for me)

Could somebody please provide a concise example using Linq to EntityFramework.

解决方案

In LINQ to Entities, think in terms of relationships rather than SQL joins. Hence, the literal equivalent of a SQL outer join on an entity Person with a one to zero or one relationship to CustomerInfo would be:

var q = from p in Context.People
        select new
        {
            Name = p.Name,
            IsPreferredCustomer = (bool?)p.CustomerInfo.IsPreferredCustomer
        };

L2E will coalesce the join, so that if CustomerInfo is null then the whole expression evaluates to null. Hence the cast to a nullable bool, because the inferred type of non-nullable bool couldn't hold that result.

For one-to-many, you generally want a hierarchy, rather than a flat, SQL-style result set:

var q = from o in Context.Orders
        select new 
        {
            OrderNo = o.OrderNo,
            PartNumbers = from od in o.OrderDetails
                          select od.PartNumber
        }

This is like a left join insofar as you still get orders with no details, but it's a graph like OO rather than a set like SQL.

这篇关于Linq是否可以与Entity Framework进行外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！