Member Avatar for mitesh.24jain

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
  • 465 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

A few clarifications based on the SQL and ’s LINQ attempt plus ’s pointers: the SQL uses XML PATH to build a comma-separated time list and also LEFT JOINs TutorClasses but then filters by TC.RegistrationId in the WHERE — that effectively becomes an INNER JOIN. Entity Framework cannot translate a C# string.Join (or other client-only string aggregation) into SQL, so the practical patterns are (A) project minimal columns + the child timing rows, switch to LINQ-to-Objects and call string.Join (safe and simple), or (B) push aggregation to the database (raw SQL / view / STRING_AGG) for large datasets.

Example (project minimal data, then aggregate in memory):

var q = db.StudentRequests
  .Where(sr => db.TutorClasses.Any(tc => tc.ClassId == sr.ClassId && tc.RegistrationId == registrationId))
  .Select(sr => new {
    sr.StudentRequestId,
    sr.RegistrationId,
    sr.Location,
    sr.PaymentMethod,
    sr.CreatedOn,
    ClassName = sr.Class.ClassName,
    CampusName = sr.Registration.Campus.CampusName,
    Timings = sr.StudentRequestTimings
               .OrderBy(t => t.CreatedOn)
               .Select(t => t.FromTime.Replace("AM","").Replace("PM","") + "-" + t.ToTime)
  })
  .AsEnumerable()   // switch to LINQ-to-Objects here
  .Select(x => new {
    x.StudentRequestId,
    x.RegistrationId,
    x.Location,
    x.PaymentMethod,
    x.CreatedOn,
    x.ClassName,
    x.CampusName,
    Time = string.Join(", ", x.Timings)
  })
  .OrderByDescending(x => x.CreatedOn);

If true LEFT JOIN semantics are required (keep rows that have no TutorClass), put the filter inside a group-join and use DefaultIfEmpty() instead of placing the filter in Where(...) — otherwise rows with no TC will be excluded. To compute the StatusId defaulting to 1, select the matching TutorStudentRequest (filtered by tutor id) and coalesce to 1:

StatusId = (sr.TutorStudentRequests
              .Where(tsr => tsr.TutorId == registrationId)
              .Select(tsr => (int?)tsr.StatusId)
              .FirstOrDefault() ?? 1)

Troubleshooting / performance notes:

  • Use .AsNoTracking() for read-only lists.
  • Project only needed columns before .AsEnumerable() to avoid heavy client-side load.
  • Inspect the generated SQL (EF logging) to confirm joins/filters are correct.
  • For large volumes prefer server-side aggregation (SQL view or STRING_AGG / stored proc) to avoid pulling many child rows.
  • Confirm whether the original WHERE TC.RegistrationId = ... was intentional; if not, move that predicate into the join to preserve left-join behavior.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.