Convert SQl query to Linq query in c#

Question

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

delcare @personId int=12345

select distinct  p.fName, p.lName, p.personId, p.assignedId,0 doId, isnull(nte.notesId,0)notesId,

	isnull(fnte.fitterNotesId,0)fitterNotesId,

	isnull(nte.requestId,0)requestId,

	isnull(nte.notes,'')notes,

	isnull(prs.firstName,'') CreatedByFirtstName,isnull(prs.lastName,'') CreatedByLastName, fnte.FitterId,

	convert(varchar(10),isnull(fnte.dateCreated,'01/01/1900'),101) createdDate,ISNULL(fnte.createdBy,0)createdBy ,

	isnull(fnte.modifiedBy,0)modifiedBy

from 

(select * from t1 fnte(NOLOCK) where   fnte.status='Notify' and fnte.fitterId=@personId)as fnte

inner join t2 nte(NOLOCK) on nte.fitternotesid = fnte.fitterNotesId

inner join t3 p(NOLOCK) on p.personId=nte.personId

left outer join t4 prs(NOLOCK) on prs.personId = fnte.createdBy 

inner join t5 fo(NOLOCK) on  fo.personId = fnte.fitterId 

and fo.organizationId=p.organizationId  and fo.isActive=1 
```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.

int personId = 12345;

var query = from fnte in t1
			where fnte.status == "Notify" && fnte.fitterId == personId
			join nte in t2 on fnte.fitterNotesId equals nte.fitternotesid
			join p in t3 on nte.personId equals p.personId
			join prs in t4 on fnte.createdBy equals prs.personId into prs_join
			from prs in prs_join.DefaultIfEmpty()
            join fo in t5 on new { prop1 = fnte.fitterId,prop2 = p.organizationId } equals new { prop1 = fo.personId, prop2 = fo.organizationId }
            where  fo.isActive == 1
			select new
			{
				p.fName,
				p.lName,
				p.personId,
				p.assignedId,
				doId = 0,
				notesId = nte.notesId ?? 0,
				fitterNotesId = fnte.fitterNotesId ?? 0,
				requestId = nte.requestId ?? 0,
				notes = nte.notes ?? "",
				CreatedByFirstName = prs.firstName ?? "",
				CreatedByLastName = prs.lastName ?? "",
				fnte.fitterId,
				createdDate = fnte.dateCreated.HasValue ? fnte.dateCreated.Value.ToString("MM/dd/yyyy") : "01/01/1900",
				createdBy = fnte.createdBy ?? 0,
				modifiedBy = fnte.modifiedBy ?? 0
			};
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.

Answer 2

linq does not support calling stored procedure. you can use ef or dapper to map a stored proc results into object collections, then use linq to join the object collections.

Answer 3

convert into linq query

The easier way is to create a stored procedure and call it form Entity Framework (EF).