Linq to SQL使用Lambda语法并在2列上进行了左外部联接(复合联接键) [英] Linq to SQL left outer join using Lambda syntax and joining on 2 columns (composite join key)

问题描述

我正在尝试使用Linq to SQL作为Lambda表达式在2列上进行内部联接.普通查询如下所示.

I am trying to make an Inner Join on 2 columns with Linq to SQL as a Lambda expression. The normal query would look like this.

SELECT * FROM participants 
LEFT OUTER JOIN prereg_participants ON prereg_participants.barcode = participants.barcode
AND participants.event_id = prereg_participants.event_id
WHERE (participants.event_id = 123)

我成功地在左列上使用了以下代码.

I am succeeding in making a Left Outer Join on one column with the following code.

var dnrs = context.participants.GroupJoin(
    context.prereg_participants,
    x => x.barcode,
    y => y.barcode,
    (x, y) => new { deelnr = x, vi = y })
    .SelectMany(
    x => x.vi.DefaultIfEmpty(),
    (x, y) => new { deelnr = x, vi = y })
    .Where(x => x.deelnr.deelnr.event_id == 123)
    .ToList();

问题是上述Lambda导致我得到太多结果，因为它缺少AND participants.event_id = prereg_participants.event_id部分.但是，无论我怎么努力，我都没有得到正确数量的参与者.

The problem is that with the above Lambda I get too many results because it is missing the AND participants.event_id = prereg_participants.event_id part. But whatever I try i'm not getting the correct amount of participants.

我查看了以下现有问题，但是在编写正确的lambda时没有一个解决了我的问题.而且大多数解决方案都是lambda格式的nog或不是多列上的Left外部联接.

I looked at the following existing questions, but none solved my problem in writing the correct lambda. And most of the solutions are nog in lambda-format or not a Left outer join on multiple columns.

如何加入LINQ在单个联接中的多个字段上

LINQ to SQL-左外部连接多个加盟条件

Group通过使用Lambda的多于两列表情

其中大多数来自

And most of these from this Google search

推荐答案

我能够在同时在Linq2Sql和Entity Framework中工作的复合外键对barcode, event_id上获得此LEFT OUTER JOIN，并按照此查询语法示例.

I was able to get this LEFT OUTER JOIN on the composite foreign key pair barcode, event_id working in both Linq2Sql, and Entity Framework, converting to lambda syntax as per this query syntax example.

这可以通过创建一个匿名投影来实现，该投影用于连接条件的左右两侧的匹配:

This works by creating an anonymous projection which is used in match of the left and right hand sides of the join condition:

var dnrs = context.participants.GroupJoin(
    context.prereg_participants,
    x => new { JoinCol1 = x.barcode, JoinCol2 = x.event_id }, // Left table join key
    y => new { JoinCol1 = y.barcode, JoinCol2 = y.event_id }, // Right table join key
    ...

注释

此方法依赖于

由于匿名类型的Equals和GetHashCode方法是根据属性的Equals和GetHashCode方法定义的，因此只有所有匿名属性都相等时，相同匿名类型的两个实例才相等.

Because the Equals and GetHashCode methods on anonymous types are defined in terms of the Equals and GetHashCode methods of the properties, two instances of the same anonymous type are equal only if all their properties are equal.

因此，为了使连接键的两个投影必须具有相同的类型才能成为equal，编译器需要将它们视为幕后相同的匿名类，即:

So for the two projections for the join keys need to be of the same type in order to be equal, the compiler needs to see them as the same anonymous class behind the scenes, i.e.:

• 两个匿名投影中的连接列数必须相同
• 字段类型必须具有相同的兼容类型
• 如果字段名称不同，那么您将需要为它们加上别名(我用过JoinColx)

我已经在 GitHub此处.

遗憾的是，目前尚不支持表达式树中的值元组，因此您需要坚持使用匿名类型在投影中.

Sadly, there's no support yet for value tuples in expression trees, so you'll need to stick to anonymous types in the projections.

这篇关于Linq to SQL使用Lambda语法并在2列上进行了左外部联接(复合联接键)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！