CODEWITHSUNDEEP
c#sql-server-2008entity-frameworklinq-to-entities
ledgeJumper
ledgeJumper

Reputation: 3630

can't figure out how to do multiple joins in linq to EF

tl;dr version: I need this sql statement to work with linq to EF

This is the query in SQL:

select 
stu.SyStudentId, 
rtrim(stu.StuNum) as StudentNumber, 
stu.SSN,
stu.FirstName, 
stu.LastName, 
Email, 
OtherEmail, 
stu.StartDate,
case 
when (systa.category = 'E') then 'Enrolled'  
    when (systa.category = 'A') then 'Active'  
    else 'This should never happen'
end as StatusCategory,
rtrim(schsta.Code) as SchoolStatusCode,
pic.StudentPicture,
case when (stu.DateLstMod > isnull(pic.DateLstMod,'1900-1-1')) 
then stu.DateLstMod 
    else pic.DateLstMod 
end as DateLstMod
from 
SyStudent stu
inner join syschoolstatus schsta on schsta.syschoolstatusid = stu.syschoolstatusid
inner join SyStatus systa on systa.SyStatusId = schsta.SyStatusId
left outer join cmstudentpicture pic on pic.systudentid = stu.systudentid
where stu.sycampusid = 6
and systa.category in ('E','A')

ugly query, inherited from old systems..

I am trying to get some of the services we have to utilize entity framework. I am trying to get this query to work using the method based syntax. I first get a list of all the objects I am going to be joining to:

var students = ctx.syStudents.ToList(); //root
var statusCode = ctx.SySchoolStatus.ToList(); //inner
var status = ctx.syStatus.ToList(); //inner
var picture = ctx.CmStudentPictures.ToList(); //left outer

'ctx' is my dbcontext class.

The query I have so far is this:

var query = students
   .GroupJoin(statusCode,
   student => student.SySchoolStatusID,
   statuscode => statuscode.SySchoolStatusID,
   (student, studentStatusCode) => new
   {
      StudentName = student.FirstName
   });

obviously not fully implemented, but I am trying to get an idea if this is the best way to perform this, since I will have a couple of inner joins, and then a left outer join as well, or is there a better, more readable way to get this done?

Upvotes: 0

Views: 74

Answers (1)

DShook
DShook

Reputation: 15664

I would definitely recommend the query syntax over the lambda syntax for joins.

Try something along these lines:

var result = 
from stu in SyStudent
join schsta in syschoolstatus on stu.syschoolstatusid equals schsta.syschoolstatusid //inner
join systa in SyStatus on schsta.SyStatusId equals systa.SyStatusId //innner
from pic in cmstudentpicture.Where(x => x.systudentid = stu.systudentid).DefaultIfEmpty() //outer 
where stu.sycampusid = 6
select new{
    stu,
    schsta,
    systa,
    pic
};

Upvotes: 1

Related Questions