CODEWITHSUNDEEP
c#sqllinqentity-frameworkleft-join
Victor
Victor

Reputation: 762

Convert SQL Left Join to Linq Expression Left Join

I have a query from SQL and I'm doing a left join. It's working ok.

SELECT Table1.Id, Table1.Comments, Table1.IdListDef  
FROM Table2 INNER JOIN Table1 ON Table2.Id = Table1.IdListDef  
LEFT JOIN Table3  
ON Table1.Id = Table3.IdComments  
WHERE Table1.IdListDef = 36 and Table2.IdRev = 1075 and Table3.IdAutor IS NULL

I need to transfrom this query to Linq Expression from C#. How can I do it? I don't know how to transform this query that contains left join.

Upvotes: 1

Views: 2109

Answers (2)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

TRY THIS QUERY

    VAR OBJLIST=(FROM A IN CONTEX.TABLE2
        FROM B IN CONTEX.TABLE1.WHERE(X=>X.IdListDef==a.Id && B.IdListDef==36 && B.IdRev==1075 )
        FROM C IN CONTEX.TABLE3.WHRE(X=>X.IdComments==a.Id).DefaultEmpty()
        where  C.IdAutor==NULL
        select new{a.Id, a.Comments, a.IdListDef }).ToList()

Upvotes: -1

msmolcic
msmolcic

Reputation: 6557

It should look something like this:

var result = (
    from item1 in table1
    join item2 in table2 on item1.IdListDef equals item2.Id
    join item3 in table3 on item1.Id equals item3.IdComments into finalGroup
    from finalItem in finalGroup.DefaultIfEmpty()
    where item1.IdListDef == 36 && item2.IdRev == 1075 && finalItem.IdAutor == null
    select new
    {
        item1.Id, item1.Comments, item1.IdListDef
    }).ToList();

Addition to your comment, if you're willing to see if any item has id of your outer parameter you could use linq Any extension method:

bool idExists = result.Any(item => item.id == idAutor);

Upvotes: 2

Related Questions