from f in Foo
join b in Bar on f.Foo_Id equals b.Foo_Id into g
from result in g.DefaultIfEmpty()
select new { Foo = f, Bar = result }
我该如何使用扩展方法来表达同样的任务?例如:
Foo.GroupJoin(Bar, f => f.Foo_Id, b => b.Foo_Id, (f,b) => ???)
.Select(???)
使用 lambda 表示法进行表 Foo
和表 Bar
的 (左外) 连接,连接条件为 Foo.Foo_Id = Bar.Foo_Id
:
var qry = Foo.GroupJoin(
Bar,
foo => foo.Foo_Id,
bar => bar.Foo_Id,
(x,y) => new { Foo = x, Bars = y })
.SelectMany(
x => x.Bars.DefaultIfEmpty(),
(x,y) => new { Foo=x.Foo, Bar=y});
由于这似乎是使用方法(扩展)语法进行左外连接的默认SO问题,我认为我应该添加一种替代当前选定答案的方法(至少在我的经验中)更常见。
// Option 1: Expecting either 0 or 1 matches from the "Right"
// table (Bars in this case):
var qry = Foos.GroupJoin(
Bars,
foo => foo.Foo_Id,
bar => bar.Foo_Id,
(f,bs) => new { Foo = f, Bar = bs.SingleOrDefault() });
// Option 2: Expecting either 0 or more matches from the "Right" table
// (courtesy of currently selected answer):
var qry = Foos.GroupJoin(
Bars,
foo => foo.Foo_Id,
bar => bar.Foo_Id,
(f,bs) => new { Foo = f, Bars = bs })
.SelectMany(
fooBars => fooBars.Bars.DefaultIfEmpty(),
(x,y) => new { Foo = x.Foo, Bar = y });
List<int> tableA = new List<int> { 1, 2, 3 };
List<int?> tableB = new List<int?> { 3, 4, 5 };
// Result using both Option 1 and 2. Option 1 would be a better choice
// if we didn't expect multiple matches in tableB.
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3 }
List<int> tableA = new List<int> { 1, 2, 3 };
List<int?> tableB = new List<int?> { 3, 3, 4 };
// Result using Option 1 would be that an exception gets thrown on
// SingleOrDefault(), but if we use FirstOrDefault() instead to illustrate:
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3 } // Misleading, we had multiple matches.
// Which 3 should get selected (not arbitrarily the first)?.
// Result using Option 2:
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3 }
{ A = 3, B = 3 }
选项2符合典型的左外连接定义,但正如我之前提到的那样,根据数据集的不同,它通常是不必要的复杂。
使用 Group Join 方法并非实现两个数据集连接的必要方法。
内部连接:
var qry = Foos.SelectMany
(
foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id),
(foo, bar) => new
{
Foo = foo,
Bar = bar
}
);
对于左连接,只需添加 DefaultIfEmpty()。
var qry = Foos.SelectMany
(
foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id).DefaultIfEmpty(),
(foo, bar) => new
{
Foo = foo,
Bar = bar
}
);
EF和LINQ to SQL可以正确地转换为SQL。 对于LINQ to Objects,最好使用GroupJoin进行连接,因为它在内部使用Lookup。 但是,如果您正在查询数据库,跳过GroupJoin是AFAIK的性能更好。
对我个人来说,与GroupJoin().SelectMany()相比,这种方式更易读。
您可以创建扩展方法,例如:
public static IEnumerable<TResult> LeftOuterJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> other, Func<TSource, TKey> func, Func<TInner, TKey> innerkey, Func<TSource, TInner, TResult> res)
{
return from f in source
join b in other on func.Invoke(f) equals innerkey.Invoke(b) into g
from result in g.DefaultIfEmpty()
select res.Invoke(f, result);
}
在Ocelot20的答案基础上改进,如果你要与一个表进行左外连接,并且你只需要其中0或1行,但它可能有多个,那么你需要对连接后的表进行排序:
var qry = Foos.GroupJoin(
Bars.OrderByDescending(b => b.Id),
foo => foo.Foo_Id,
bar => bar.Foo_Id,
(f, bs) => new { Foo = f, Bar = bs.FirstOrDefault() });
虽然被接受的答案可行且适用于Linq to Objects,但我觉得SQL查询不仅仅是一个简单的左外连接。
以下代码依赖于LinqKit项目,该项目允许您传递表达式并将其调用到查询中。
static IQueryable<TResult> LeftOuterJoin<TSource,TInner, TKey, TResult>(
this IQueryable<TSource> source,
IQueryable<TInner> inner,
Expression<Func<TSource,TKey>> sourceKey,
Expression<Func<TInner,TKey>> innerKey,
Expression<Func<TSource, TInner, TResult>> result
) {
return from a in source.AsExpandable()
join b in inner on sourceKey.Invoke(a) equals innerKey.Invoke(b) into c
from d in c.DefaultIfEmpty()
select result.Invoke(a,d);
}
可以按以下方式使用:
Table1.LeftOuterJoin(Table2, x => x.Key1, x => x.Key2, (x,y) => new { x,y});
我收藏了这个问题,每年需要参考一次左右。每次重新访问时,我发现自己已经忘记了它的工作原理。以下是更详细的说明。
GroupJoin
就像 GroupBy
和 Join
的混合体。 GroupJoin
基本上通过连接键对外部集合进行分组,然后将分组与内部集合连接在连接键上。假设我们有客户和订单。如果你按照相应的 ID 进行 GroupJoin
,则结果是一个可枚举的对象 {Customer,IGrouping<int, Order>}
。之所以 GroupJoin
很有用,是因为即使外部集合不包含匹配的对象,所有内部对象都会被表示出来。对于没有订单的客户,IGrouping<int,Order>
就是空的。一旦我们有了 { Customer, IGrouping<int, Order> }
,我们可以直接使用它,过滤掉没有订单的结果,或者使用 SelectMany
将其展平,从而获得像传统 LINQ Join
一样的结果。
以下是一个完整的示例,如果有人想逐步使用调试器查看其工作原理:
using System;
using System.Linq;
public class Program
{
public static void Main()
{
//Create some customers
var customers = new Customer[]
{
new Customer(1, "Alice"),
new Customer(2, "Bob"),
new Customer(3, "Carol")
};
//Create some orders for Alice and Bob, but none for Carol
var orders = new Order[]
{
new Order(1, 1),
new Order(2, 1),
new Order(3, 1),
new Order(4, 2),
new Order(5, 2)
};
//Group join customers to orders.
//Result is IEnumerable<Customer, IGrouping<int, Order>>.
//Every customer will be present.
//If a customer has no orders, the IGrouping<> will be empty.
var groupJoined = customers.GroupJoin(orders,
c => c.ID,
o => o.CustomerID,
(customer, order) => (customer, order));
//Display results. Prints:
// Customer: Alice (CustomerID=1), Orders: 3
// Customer: Bob (CustomerID=2), Orders: 2
// Customer: Carol (CustomerID=3), Orders: 0
foreach(var result in groupJoined)
{
Console.WriteLine($"Customer: {result.customer.Name} (CustomerID={result.customer.ID}), Orders: {result.order.Count()}");
}
//Flatten the results to look more like a LINQ join
//Produces an enumerable of { Customer, Order }
//All customers represented, order is null if customer has no orders
var flattened = groupJoined.SelectMany(z => z.order.DefaultIfEmpty().Select(y => new { z.customer, y }));
//Get only results where the outer table is null.
//roughly equivalent to:
//SELECT *
//FROM A
//LEFT JOIN B
//ON A.ID = B.ID
//WHERE B.ID IS NULL;
var noMatch = groupJoined.Where(z => z.order.DefaultIfEmpty().Count() == 0);
}
}
class Customer
{
public int ID { get; set; }
public string Name { get; set; }
public Customer(int iD, string name)
{
ID = iD;
Name = name;
}
}
class Order
{
static Random Random { get; set; } = new Random();
public int ID { get; set; }
public int CustomerID { get; set; }
public decimal Amount { get; set; }
public Order(int iD, int customerID)
{
ID = iD;
CustomerID = customerID;
Amount = (decimal)Random.Next(1000, 10000) / 100;
}
}
internal static IEnumerable<Tuple<TLeft, TRight>> LeftJoin<TLeft, TRight, TKey>(
this IEnumerable<TLeft> left,
IEnumerable<TRight> right,
Func<TLeft, TKey> selectKeyLeft,
Func<TRight, TKey> selectKeyRight,
TRight defaultRight = default(TRight),
IEqualityComparer<TKey> cmp = null)
{
return left.GroupJoin(
right,
selectKeyLeft,
selectKeyRight,
(x, y) => new Tuple<TLeft, IEnumerable<TRight>>(x, y),
cmp ?? EqualityComparer<TKey>.Default)
.SelectMany(
x => x.Item2.DefaultIfEmpty(defaultRight),
(x, y) => new Tuple<TLeft, TRight>(x.Item1, y));
}
Marc Gravell的答案被转化为支持IQueryable<T>
接口的扩展方法,可以参考this answer,并且增加了对C# 8.0 NRT的支持,具体如下:
#nullable enable
using LinqKit;
using LinqKit.Core;
using System.Linq.Expressions;
...
/// <summary>
/// Left join queryable. Linq to SQL compatible. IMPORTANT: any Includes must be put on the source collections before calling this method.
/// </summary>
public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
this IQueryable<TOuter> outer,
IQueryable<TInner> inner,
Expression<Func<TOuter, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TOuter, TInner?, TResult>> resultSelector)
{
return outer
.AsExpandable()
.GroupJoin(
inner,
outerKeySelector,
innerKeySelector,
(outerItem, innerItems) => new { outerItem, innerItems })
.SelectMany(
joinResult => joinResult.innerItems.DefaultIfEmpty(),
(joinResult, innerItem) =>
resultSelector.Invoke(joinResult.outerItem, innerItem));
}
这对我来说更简化了。
var appuser = appUsers.GroupJoin(trackLogin, u => u.Id, ur => ur.UserId, (u, ur) => new { u = u, ur = ur })
.Select( m => new { m.u.Id, m.u.Email, m.u.IsSuperUser, m.u.RoleId,
LastLogin = m.ur.Select(t => t.LastLogin).FirstOrDefault()}).ToList();
GroupJoin
执行左外连接,而SelectMany
部分仅根据您要选择的内容而需要。 - George MauerGroupJoin
不会展开IGrouping<Bar>
(其中包含0或1个项目)。因此,左外连接是不完整的,无论如何都需要SelectMany
来完成它。 - mins