How to convert SQL query to LINQ lambda expression with Inner Join and subquery

Question

I want to convert following SQL query into LINQ lambda expression.

SELECT Hod.* 
FROM (SELECT * FROM tbLease_Approval
      WHERE (UserRole='HOD' and IsApproved=1)) Pm 
INNER JOIN (SELECT * FROM tbLease_Approval
            WHERE UserRole='Finance' and IsApproved=0) Hod 
ON Pm.LeaseNo=Hod.LeaseNo

What have you tried so far? Share your code and thus get a good answer. — Selim Yildiz, Sep 23 '19 at 05:30
@SelimYıldız: Thanks for reply. I made query with expecting output. — Pankaj Sonawane, Sep 23 '19 at 05:55
You might find my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) useful in the future. — NetMage, Sep 23 '19 at 21:57

score 0 · Answer 1 · answered Oct 18 '19 at 12:26

I have not tested this, but it should be something like:

var qry= tbLease_Approval
             .Join(tbLease_Approval, hod => hod.LeaseNo, fin => fin.LeaseNo, (hod, fin) => new { Hod = hod, Finance = fin })
             .Where(joined => joined.Hod.UserRole.Equals("Hod")
                           && joined.Hod.IsApproved == 1
                           && joined.Finance.UserRole.Equals("Finance")
                           && joined.Finance.IsApproved == 0);

How to convert SQL query to LINQ lambda expression with Inner Join and subquery

1 Answers1