将常规SQL转换为LINQ到实体

Question

经过大量搜索，我找不到以下SQL语句的简单答案：

SELECT t1.LoginName, t0.BNAME
FROM USR02 AS t0
LEFT OUTER JOIN LoginData AS t1
    INNER JOIN Mandants AS t2 ON t1.Id_Mandants = t2.Id_Mandants
ON t0.BNAME = t1.LoginName AND t0.MANDT = t2.CodeMandant

由于双ON语句，我不知道如何用LINQ编写它。

我试图简化它，但多个主键使工作变得困难。

Answer 1

首先将SQL查询转换为更自然的查询。像这样：

SELECT  t1.LoginName, t0.BNAME
FROM    USR02 AS t0
        LEFT OUTER JOIN LoginData AS t1
            ON t0.BNAME = t1.LoginName 
        INNER JOIN Mandants AS t2 
            ON t1.Id_Mandants = t2.Id_Mandants
WHERE   t0.MANDT = t2.CodeMandant

现在应该很容易将其翻译成LINQ。当您已经在实体模型中正确设置了关系时，您将能够编写以下LINQ查询：

from data in db.LoginData
where data.User.MANDT == data.Mandant.CodeMandant
select new { data.LoginName, data.User.BNAME };

顺便说一句。为什么将两个LoginData.LoginName都输出为USR02.BNAME，因为它们总是相等的？

Answer 2

您需要将查询转换为常规的1级联接：

select t1.LoginName, t0.BNAME
from USR02 as t0
left outer join LoginData as t1 on t0.BNAME = t1.LoginName 
inner join Mandants as t2 on t0.MANDT = t2.CodeMandant and t1.Id_Mandants = t2.Id_Mandants

然后在LINQ to Entities:上重写它会容易得多

from t0 in db.t0
join t1 in db.t1 on t0.BNAME equals t1.LoginName 
join t2 in db.t2 on new { t0.MANDT, t1.Id_Mandants} equals new { t2.CodeMandant , t2.Id_Mandants }
select new { t1.LoginName, t0.BNAME };

Answer 3

我喜欢用这种方式编写连接

from t0 in db.t0
from t1 in db.t1.Where(x => t0.BNAME == x.LoginName).DefaultIfEmpty() 
from t2 in db.t2.Where(x => t0.MANDT == x.CodeMandant)
                .Where(x => t1.Id_Mandants == x.Mandants)
select new { t1.LoginName, t0.BNAME };