Convert SQl query to Linq query in c#

Question

Below is my full sql query need to convert into linq query

select distinct pd.personId

from t1 (NOLOCK) pd

inner join t2 (NOLOCK) fo on fo.personId = pd.createdBy and fo.isActive=1

left join t3 (NOLOCK) fap on pd.personId=fap.personId and fap.TypeId=232

left join t4 (NOLOCK) rd on rd.PersonId=pd.personId

left join t5 (NOLOCK) pp on pp.personId=pd.personId

where pd.createdBy=@personId

Need above sql query to joins using linq query like below my agenda is to call individual stored procedure the join in c#

var t1=callingt1storedproce();

var t2=callingt2storedproce();

var t3=callingt3storedproce();

var t4=callingt4storedproce();

var t5=callingt5storedproce();

Here how to join above sql query into linq query by using above var list t1,t2,t2,t3,t4, andt5

Answer 1

Hi,@coder rock. Welcome to Microsoft Q&A. 

You could refer to the following code to achieve the function you want.

var personId = 1;

var query = from pd in t1
            join fo in t2 on pd.CreatedBy equals fo.PersonId
            where fo.IsActive == 1
            join fap in t3 on pd.PersonId equals fap.PersonId into fapJoin
            from fap in fapJoin.DefaultIfEmpty()
            where fap == null || fap.TypeId == 232
            join rd in t4 on pd.PersonId equals rd.PersonId into rdJoin
            from rd in rdJoin.DefaultIfEmpty()
            join pp in t5 on pd.PersonId equals pp.PersonId into ppJoin
            from pp in ppJoin.DefaultIfEmpty()
            where pd.CreatedBy == personId
            select pd.PersonId;

var result = query.Distinct().ToList();

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.