我需要在Linq-to-sql中实现这一点
SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s ON c.Room = s.Entry_Bar_Code
OR c.HomeRoom = s.Entry_Bar_Code
WHERE s.id = 1021645
我对此所做的所有研究表明,linq-to-sql 不能支持"OR"多重联接,建议改为像这样做两个联接:
SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s ON c.Room = s.Entry_Bar_Code
LEFT OUTER JOIN dbo.Lab_Space s2 ON c.HomeRoom = s2.Entry_Bar_Code
WHERE s.id = 1021645
这些实际上不是同一个查询,因为它们会返回不同的结果。 在这一点上,除了将原始SQL放入我的C#程序中之外,还有什么方法可以完成上述工作吗?
让我们从头开始。如果将WHERE
与表中的列一起使用OUTER JOIN
则表示您的查询:
SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s ON c.Room = s.Entry_Bar_Code
OR c.HomeRoom = s.Entry_Bar_Code
WHERE s.id = 1021645
在逻辑上等效于:
SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
JOIN dbo.Lab_Space s
ON c.Room = s.Entry_Bar_Code
OR c.HomeRoom = s.Entry_Bar_Code
WHERE s.id = 1021645;
这可以通过CROSS JOIN
(伪代码)来实现:
var q = from c in Collector_Capital_Equipment
from s in Lab_Space
where s.id == 1021645
&& (s.Entry_Bar_Code == c.Room || c.HomeRoom == s.Entry_Bar_Code)
select ...
我假设你真的想生成查询:
SELECT DISTINCT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s
ON (c.Room = s.Entry_Bar_Code OR c.HomeRoom = s.Entry_Bar_Code)
AND s.id = 1021645
可以表示为:
SELECT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s
ON c.Room = s.Entry_Bar_Code AND s.id = 1021645
INTERSECT
SELECT c.*
FROM dbo.Collector_Capital_Equipment c
LEFT OUTER JOIN dbo.Lab_Space s
ON c.HomeRoom = s.Entry_Bar_Code AND s.id = 1021645
上述查询可以通过 LINQ 使用集合运算符实现。
数据库<>小提琴演示
我以前从未见过有人在连接上使用OR子句。MS SQL Server在SQL中是否支持此功能?
老实说,我可能只是将其拆分为两个查询。
SELECT entry_bar_code
FROM dbo.Lab_Space WHERE id = 1021645;
结果被输入
SELECT DISTINCT *
FROM dbo.Collector_Capital_Equipment
WHERE c.room == <barcode> OR c.homeRoom == <barcode>