0

As I am new to Linq and Entity Framework, I don't know how I add comma sepearted and left join in linq/Entity code.

My Sql query is rather big and I want it to be convert in Linq/entity:

Select DISTINCT SR.StudentRequestId,SR.RegistrationId,SR.Location,SR.PaymentMethod,SR.CreatedOn,C.ClassName,CC.CampusName,
CASE WHEN ISNULL(TSR.StatusId,0)=0 THEN 1 ELSE TSR.StatusId END AS StatusId,
 substring(
(
    Select ', '+REPLACE(REPLACE(ST1.FromTime,'AM',''),'PM','')+'-'+ST1.ToTime AS [text()]
    From dbo.StudentRequestTimings ST1
    Where ST1.StudentRequestId = SRT.StudentRequestId
    ORDER BY ST1.CreatedOn
    For XML PATH ('')
), 2, 1000) [Time]
FROM StudentRequest SR
Inner JOIN Registration R ON R.RegistrationId=SR.RegistrationId
INNER JOIN Campus CC ON CC.CampusId=R.CampusId
INNER JOIN Class C ON C.ClassId=SR.ClassId 
LEFT JOIN TutorClasses TC ON SR.ClassId=TC.ClassId
LEFT JOIN StudentRequestTimings SRT ON SR.StudentRequestId=SRT.StudentRequestId
LEFT JOIN TutorStudentRequest TSR ON TSR.StudentRequestId=SRT.StudentRequestId AND TutorId=@RegistrationId
where TC.RegistrationId=@RegistrationId
ORDER BY SR.CreatedOn DESC

What I have tried is below code

var model = from sr in db.StudentRequests
            join r in db.Registrations on sr.RegistrationId equals r.RegistrationId
            join cc in db.Campus on r.CampusId equals cc.CampusId
            join c in db.Classes on sr.ClassId equals c.ClassId
            from tc in db.TutorClasses.Where(t=>t.ClassId==sr.ClassId).DefaultIfEmpty()
            from srt in db.StudentRequestTimings.Where(s=>s.StudentRequestId==sr.StudentRequestId).DefaultIfEmpty()
            from tsr in db.TutorStudentRequests.Where(t=>t.StudentRequestId==srt.StudentRequestId && t.TutorId==registrationid)
            where sr.RegistrationId == registrationid
            select new { sr.StudentRequestId, sr.RegistrationId, sr.Location, sr.PaymentMethod,sr.CreatedOn, c.ClassName, cc.CampusName };

Can any one help me out by putting left join and with comma separated for 1 column in linq/Entity?

Any Help?
I will marked your answer if it work for me

2
Contributors
1
Reply
16
Views
1 Month
Discussion Span
Last Post by rproffitt
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.