Linq-完全外连接
Linq-完全外连接
ID FirstName-- --------- 1 John 2 SueID LastName-- -------- 1 Doe 3 Smith
ID FirstName LastName-- --------- -------- 1 John Doe 2 Sue 3 Smith
private void OuterJoinTest(){
List<FirstName> firstNames = new List<FirstName>();
firstNames.Add(new FirstName { ID = 1, Name = "John" });
firstNames.Add(new FirstName { ID = 2, Name = "Sue" });
List<LastName> lastNames = new List<LastName>();
lastNames.Add(new LastName { ID = 1, Name = "Doe" });
lastNames.Add(new LastName { ID = 3, Name = "Smith" });
var outerJoin = from first in firstNames
join last in lastNames
on first.ID equals last.ID into temp from last in temp.DefaultIfEmpty()
select new
{
id = first != null ? first.ID : last.ID,
firstname = first != null ? first.Name : string.Empty,
surname = last != null ? last.Name : string.Empty
};
}}public class FirstName{
public int ID;
public string Name;}public class LastName{
public int ID;
public string Name;}ID FirstName LastName-- --------- -------- 1 John Doe 2 Sue
守着一只汪浏览 1071回答 3
3回答
-
守候你守候我
我认为其中大多数都存在问题，包括公认的答案，因为它们在IQueryable上与Linq的工作不太好，这要么是因为执行了太多的服务器往返和太多的数据返回，要么是因为执行了太多的客户端执行。对于IEnumerable，我不喜欢Sehe的答案或类似的内容，因为它占用了过多的内存(一个简单的10000000双列表测试在我的32 GB机器上运行Linqpad)。另外，其他大多数并不实际实现正确的完全外部联接，因为它们使用的是带有右联接的UNION，而不是与右反半连接的UNION，这不仅从结果中消除了重复的内部联接行，而且消除了原来在左或右数据中存在的任何正确的重复。下面是我的扩展，它可以处理所有这些问题，生成SQL，就像在Linq中直接实现联接一样，在服务器上执行，并且比在EnDigable上的其他扩展更快，内存更少：public static class Ext { public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>( this IEnumerable<TLeft> leftItems, IEnumerable<TRight> rightItems, Func<TLeft, TKey> leftKeySelector, Func<TRight, TKey> rightKeySelector, Func<TLeft, TRight, TResult> resultSelector) { return from left in leftItems join right in rightItems on leftKeySelector(left) equals rightKeySelector(right) into temp from right in temp.DefaultIfEmpty() select resultSelector(left, right); } public static IEnumerable<TResult> RightOuterJoin<TLeft, TRight, TKey, TResult>( this IEnumerable<TLeft> leftItems, IEnumerable<TRight> rightItems, Func<TLeft, TKey> leftKeySelector, Func<TRight, TKey> rightKeySelector, Func<TLeft, TRight, TResult> resultSelector) { return from right in rightItems join left in leftItems on rightKeySelector(right) equals leftKeySelector(left) into temp from left in temp.DefaultIfEmpty() select resultSelector(left, right); } public static IEnumerable<TResult> FullOuterJoinDistinct<TLeft, TRight, TKey, TResult>( this IEnumerable<TLeft> leftItems, IEnumerable<TRight> rightItems, Func<TLeft, TKey> leftKeySelector, Func<TRight, TKey> rightKeySelector, Func<TLeft, TRight, TResult> resultSelector) { return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector).Union(leftItems. RightOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector)); } public static IEnumerable<TResult> RightAntiSemiJoin<TLeft, TRight, TKey, TResult>( this IEnumerable<TLeft> leftItems, IEnumerable<TRight> rightItems, Func<TLeft, TKey> leftKeySelector, Func<TRight, TKey> rightKeySelector, Func<TLeft, TRight, TResult> resultSelector) where TLeft : class { var hashLK = new HashSet<TKey>(from l in leftItems select leftKeySelector(l)); return rightItems.Where(r => !hashLK.Contains(rightKeySelector(r))).Select(r => resultSelector((TLeft)null,r)); } public static IEnumerable<TResult> FullOuterJoin<TLeft, TRight, TKey, TResult>( this IEnumerable<TLeft> leftItems, IEnumerable<TRight> rightItems, Func<TLeft, TKey> leftKeySelector, Func<TRight, TKey> rightKeySelector, Func<TLeft, TRight, TResult> resultSelector) where TLeft : class { return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector) .Concat(leftItems.RightAntiSemiJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector)); } private static Expression<Func<TP, TC, TResult>> CastSMBody<TP, TC, TResult>(LambdaExpression ex, TP unusedP, TC unusedC, TResult unusedRes) => (Expression<Func<TP, TC, TResult>>)ex; public static IQueryable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>( this IQueryable<TLeft> leftItems, IQueryable<TRight> rightItems, Expression<Func<TLeft, TKey>> leftKeySelector, Expression<Func<TRight, TKey>> rightKeySelector, Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class { var sampleAnonLR = new { left = (TLeft)null, rightg = (IEnumerable<TRight>)null }; var parmP = Expression.Parameter(sampleAnonLR.GetType(), "p"); var parmC = Expression.Parameter(typeof(TRight), "c"); var argLeft = Expression.PropertyOrField(parmP, "left"); var newleftrs = CastSMBody(Expression.Lambda(Expression.Invoke(resultSelector, argLeft, parmC), parmP, parmC), sampleAnonLR, (TRight)null, (TResult)null); return leftItems.AsQueryable().GroupJoin(rightItems, leftKeySelector, rightKeySelector, (left, rightg) => new { left, rightg }).SelectMany(r => r.rightg.DefaultIfEmpty(), newleftrs); } public static IQueryable<TResult> RightOuterJoin<TLeft, TRight, TKey, TResult>( this IQueryable<TLeft> leftItems, IQueryable<TRight> rightItems, Expression<Func<TLeft, TKey>> leftKeySelector, Expression<Func<TRight, TKey>> rightKeySelector, Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class { var sampleAnonLR = new { leftg = (IEnumerable<TLeft>)null, right = (TRight)null }; var parmP = Expression.Parameter(sampleAnonLR.GetType(), "p"); var parmC = Expression.Parameter(typeof(TLeft), "c"); var argRight = Expression.PropertyOrField(parmP, "right"); var newrightrs = CastSMBody(Expression.Lambda(Expression.Invoke(resultSelector, parmC, argRight), parmP, parmC), sampleAnonLR, (TLeft)null, (TResult)null); return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right }). SelectMany(l => l.leftg.DefaultIfEmpty(), newrightrs); } public static IQueryable<TResult> FullOuterJoinDistinct<TLeft, TRight, TKey, TResult>( this IQueryable<TLeft> leftItems, IQueryable<TRight> rightItems, Expression<Func<TLeft, TKey>> leftKeySelector, Expression<Func<TRight, TKey>> rightKeySelector, Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class { return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector). Union(leftItems.RightOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector)); } private static Expression<Func<TP, TResult>> CastSBody<TP, TResult>(LambdaExpression ex, TP unusedP, TResult unusedRes) => (Expression<Func<TP, TResult>>)ex; public static IQueryable<TResult> RightAntiSemiJoin<TLeft, TRight, TKey, TResult>( this IQueryable<TLeft> leftItems, IQueryable<TRight> rightItems, Expression<Func<TLeft, TKey>> leftKeySelector, Expression<Func<TRight, TKey>> rightKeySelector, Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class { var sampleAnonLgR = new { leftg = (IEnumerable<TLeft>)null, right = (TRight)null }; var parmLgR = Expression.Parameter(sampleAnonLgR.GetType(), "lgr"); var argLeft = Expression.Constant(null, typeof(TLeft)); var argRight = Expression.PropertyOrField(parmLgR, "right"); var newrightrs = CastSBody(Expression.Lambda(Expression.Invoke(resultSelector, argLeft, argRight), parmLgR), sampleAnonLgR, (TResult)null); return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right }).Where(lgr => !lgr.leftg.Any()).Select(newrightrs); } public static IQueryable<TResult> FullOuterJoin<TLeft, TRight, TKey, TResult>( this IQueryable<TLeft> leftItems, IQueryable<TRight> rightItems, Expression<Func<TLeft, TKey>> leftKeySelector, Expression<Func<TRight, TKey>> rightKeySelector, Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class { return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector) .Concat(leftItems.RightAntiSemiJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector)); }}右反半连接之间的区别主要与linq在对象或源中的区别有关，但在最终答案中在server(Sql)端产生了差异，从而删除了不必要的内容。JOIN.手编码Expression若要处理合并Expression<Func<>>可以使用LinqKit对lambda进行改进，但是如果语言/编译器为此添加了一些帮助，那就太好了。这个FullOuterJoinDistinct和RightOuterJoin函数是为了完整性而包含的，但我没有重新实现。FullOuterGroupJoin现在还没有。我写另一个版本一个完全的外部连接IEnumerable对于键是可排序的情况下，这是大约50%的速度比组合左外部连接和右反半连接，至少在小集合。它只在排序一次之后就会遍历每个集合。
随时随地看视频慕课网APP
C#
.NET