LINQ 中的 JOIN 和 LEFT JOIN 等效项
我正在使用以下 SQL 查询:
SELECT
a.AppointmentId,
a.Status,
a.Type,
a.Title,
b.Days,
d.Description,
e.FormId
FROM Appointment a (nolock)
LEFT JOIN AppointmentFormula b (nolock)
ON a.AppointmentId = b.AppointmentId and b.RowStatus = 1
JOIN Type d (nolock)
ON a.Type = d.TypeId
LEFT JOIN AppointmentForm e (nolock)
ON e.AppointmentId = a.AppointmentId
WHERE a.RowStatus = 1
AND a.Type = 1
ORDER BY a.Type
我不确定如何在 LINQ 中实现 JOIN。 我的所有表都有外键关系。
I am working with the following SQL query:
SELECT
a.AppointmentId,
a.Status,
a.Type,
a.Title,
b.Days,
d.Description,
e.FormId
FROM Appointment a (nolock)
LEFT JOIN AppointmentFormula b (nolock)
ON a.AppointmentId = b.AppointmentId and b.RowStatus = 1
JOIN Type d (nolock)
ON a.Type = d.TypeId
LEFT JOIN AppointmentForm e (nolock)
ON e.AppointmentId = a.AppointmentId
WHERE a.RowStatus = 1
AND a.Type = 1
ORDER BY a.Type
I am unsure how to achieve the JOINs in LINQ. All my tables have foreign key relationships.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
此 linq 方法调用(对 Join)将生成上述 Join。
这些 linq 方法调用(对 GroupJoin、SelectMany、DefaultIfEmpty)将生成上述 Left Join。
这里的关键概念是 Linq 的方法生成分层形状的结果,而不是展平的行列形状。
GroupBy
生成层次结构中的结果,其分组键与元素的集合(可能不为空)相匹配。 SQL 的GroupBy
子句生成带有聚合值的分组键 - 没有可以使用的子集合。GroupJoin
会生成一个分层形状 - 父记录与子记录的集合(可能为空)相匹配。 Sql 的 LEFT JOIN 生成与每个子记录匹配的父记录,如果没有其他匹配,则生成空子记录。 要从 Linq 的形状获取 Sql 的形状,必须使用SelectMany
解压子记录集合 - 并使用DefaultIfEmpty
处理空的子记录集合。这是我尝试对问题中的 sql 进行解析:
This linq method call (to Join) will generate the above Join.
These linq method calls (to GroupJoin, SelectMany, DefaultIfEmpty) will produce the above Left Join
The key concept here is that Linq's methods produce hierarchically shaped results, not flattened row-column shapes.
GroupBy
produces results shaped in a hierarchy with a grouping key matched to a collection of elements (which may not be empty). SQL'sGroupBy
clause produces a grouping key with aggregated values - there is no sub-collection to work with.GroupJoin
produces a hierarchical shape - a parent record matched to a collection of child records (which may be empty). Sql'sLEFT JOIN
produces a parent record matched to each child record, or a null child record if there are no other matches. To get to Sql's shape from Linq's shape, one must unpack the collection of child records withSelectMany
- and deal with empty collections of child records usingDefaultIfEmpty
.And here's my attempt at linquifying that sql in the question:
当我即兴发言时,您可能需要稍微调整一下,但有几件重要的事情需要记住。 如果您在 dbml 中正确设置了关系,那么您应该能够隐式执行内部联接,并且只需通过初始表访问数据。 另外,LINQ 中的左连接并不像我们希望的那么简单,您必须通过 DefaultIfEmpty 语法才能实现它。 我在这里创建了一个匿名类型,但您可能希望将其放入 DTO 类或类似的东西中。 我也不知道在空值的情况下你想做什么,但你可以使用 ?? 语法来定义一个值,如果该值为 null,则为该变量提供值。 如果您还有其他问题,请告诉我...
You may have to tweak this slightly as I was going off the cuff, but there are a couple of major things to keep in mind. If you have your relationships set up properly in your dbml, you should be able to do inner joins implicitly and just access the data through your initial table. Also, left joins in LINQ are not as straight forward as we may hope and you have to go through the DefaultIfEmpty syntax in order to make it happen. I created an anonymous type here, but you may want to put into a DTO class or something to that effect. I also didn't know what you wanted to do in the case of nulls, but you can use the ?? syntax to define a value to give the variable if the value is null. Let me know if you have additional questions...
如果您想保留(NOLOCK)提示,我有 在博客中发布了一个使用 C# 扩展方法的便捷解决方案。 请注意,这与向查询中的每个表添加 nolock 提示相同。
If you want to preserve the (NOLOCK) hints, I have blogged a handy solution using extension methods in C#. Note that this is the same as adding nolock hints to every table in the query.