问题描述
我正在尝试使用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.
其中大多数来自此搜索>
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
...
注释
此方法依赖于赋予相同匿名类的自动相等性,即:
因此,为了使连接键的两个投影必须具有相同的类型才能成为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列上进行了左外部联接(复合联接键)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!