Convert regular SQL to LINQ to Entities

After a lot of search, I cannot find a simple answer to this following SQL Statement :

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.CodeMandan开发者_开发知识库t

Because of the double ON statement I don't know how to write it in LINQ.

I've tried to simplify it but multiple primary key make the job hard.

---

Start by translating the SQL query to one that is more natural. Like this:

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

Now it should be easy to translate this to LINQ. When you have you have set up the relationships in your entity model correctly, you would be able to write the following LINQ query:

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

btw. Why are you outputting both LoginData.LoginName as USR02.BNAME since they are always equal?

---

You need to convert your query to regular, 1-level join:

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

Then it will be much easier to rewrite it on 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 };

---

I like writing joins in this way

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 };