LINQ中的JOIN和LEFT JOIN等效项 [英] JOIN and LEFT JOIN equivalent in LINQ

问题描述

我正在使用以下SQL查询:

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

我不确定如何在LINQ中实现JOIN.我所有的表都具有外键关系.

I am unsure how to achieve the JOINs in LINQ. All my tables have foreign key relationships.

推荐答案

在我袖手旁观时，您可能不得不稍作调整，但是要记住一些主要事项.如果您在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...

var query = (from a in context.Appointment
join b in context.AppointmentFormula on a.AppointmentId equals b.AppointmentId into temp
from c in temp.DefaultIfEmpty()
join d in context.AppointmentForm on a.AppointmentID equals e.AppointmentID into temp2
from e in temp2.DefaultIfEmpty()
where a.RowStatus == 1 && c.RowStatus == 1 && a.Type == 1
select new {a.AppointmentId, a.Status, a.Type, a.Title, c.Days ?? 0, a.Type.Description, e.FormID ?? 0}).OrderBy(a.Type);

这篇关于LINQ中的JOIN和LEFT JOIN等效项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！