Right outer join in linq-to-sql

Question

I'm new in LINQ , i have query which i wrote with SQL server and i wanna Translate/convert to LINQ.Can anyone direct me in the right direction? thx

This is my SQL:

SELECT Contact.Name, Contact.E-Mail, 
       Contact Business Relation.No_
FROM   Contact Business Relation RIGHT OUTER JOIN
          Contact ON Contact Business Relation.Contact No_ = Contact.Company No_
WHERE  (Contact.E-Mail = @mail)

This is my LINQ (I'm not sure i did it in right way):

                var query = from cbr in db.Contact_Business_Relation
                           join c in db.Contact
                           on cbr.Contact_No_ equals c.Company_No_ into f
                           from c in f.DefaultIfEmpty()
                           where c.E_Mail == Mail
                           select new
                           {
                               Mail = c.E_Mail,
                               No = cbr.No_
                           };

Possible duplicate of [LINQ Left Join And Right Join](https://stackoverflow.com/questions/4497086/linq-left-join-and-right-join) — CodeLikeBeaker, Oct 12 '17 at 15:26
You don't. You create a *proper* model with navigation properties so that you *don't* have to use joins to load related objects. — Panagiotis Kanavos, Oct 12 '17 at 15:28

Aducci · Answer 1 · 2017-10-12T15:20:04.377

1

Unfortunately there are no right joins in linq. However, you can rearrange your query to use a left join instead.

var query = from c in db.Contact
            join cbr in db.Contact_Business_Relation
            on c.Company_No_ equals cbr.Contact_No_ into f
            from cbr in f.DefaultIfEmpty()
            where c.E_Mail == Mail
            select new
            {
              Mail = c.E_Mail,
              No = cbr.No_
            };

Join Clause

edited Oct 12 '17 at 15:20

answered Oct 12 '17 at 15:17

Aducci

26,101
8
63
67

To bad :( , but i will try dat , tnx aducci – Oct 12 '17 at 15:19
2

Better fix the original problem - using entities as if they were tables instead of configuring relations between the objects. All this code should be replaced with a single `db.Contact.Include(c=>c.Relations).Where(c=>c.E_Mail == Mail)` – Panagiotis Kanavos Oct 12 '17 at 15:31
@PanagiotisKanavos - agreed, but sometimes you need to use a manual join – Aducci Oct 12 '17 at 15:38

Right outer join in linq-to-sql

1 Answers1