convert sql query to linq

Question

Hi friends I need to convert sql query into linq. I have placed my query for your reference. select p.firstnamep.lastnamea.assignmentname a.makeavailablea.pointspossiblea.duedatetime from person2 p inner join coursesectionroster cs on cs.personid = p.personid inner join assignmentsubmission asn on asn.coursesectionrosterid = cs.coursesectionrosterid inner join assignment a on a.assignmentid = asn.assignmentid where cs.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' and cs.courserole=2

Santhosh Kumar Jayaraman · Answer

public List StudentGradeBook > GetStudentsGradeBook(Guid courseSectionId) { int roler = (int)CourseSectionRoster.CourseRoleTypes.Student; List StudentGradeBook > assignmentList=new List StudentGradeBook > (); var q = from p in _context.Person2 join cs in _context.CourseSectionRoster on p.PersonId equals cs.PersonId join asn in _context.AssignmentSubmission on cs.CourseSectionRosterId equals asn.CourseSectionRosterId join a in _context.Assignments on asn.AssignmentId equals a.AssignmentId where cs.CourseSectionId == courseSectionId && cs.CourseRole == roler select new {p.FirstName  p.LastName  a.AssignmentName  a.MakeAvailable  a.PointsPossible  a.DueDateTime }; foreach(var item in q) { StudentGradeBook a=new StudentGradeBook (){FirstName=item.FirstName LastName=item.LastName AssignmentName=item.AssignmentName MakeAvailable=item.MakeAvailable PointsPossible =item.PointsPossible  DueDateTime= item. DueDateTime }; assignmentList.Add(a); } return assignmentList; }

Dot NetProgrammer · Answer

hi Iftikar Hussain good post

vidhya · Answer

thanks to every one. i resolved my issue through your guidelines. I followed santhosh code to resolve that issue. thanks a lot santhosh.

Iftikar Hussain · Answer

modify like this public List GetStudentsGradeBook(Guid courseSectionId) { int roler = (int)CourseSectionRoster.CourseRoleTypes.Student; var result = from p in _context.Person2 join cs in _context.CourseSectionRoster on p.PersonId equals cs.PersonId join asn in _context.AssignmentSubmission on cs.CourseSectionRosterId equals asn.CourseSectionRosterId join a in _context.Assignments on asn.AssignmentId equals a.AssignmentId where cs.CourseSectionId == courseSectionId && cs.CourseRole == roler select new StudentGradeBook { FirstName= p.firstname L lastName = p.lastname  AssignmentName = a.assignmentname  MakeAvailable = a.makeavailable  PointsPossible = a.pointspossible  DueDateTime = a.duedatetime }; var studentGradeBooks = new List StudentGradeBook >(result.ToList()); return studentGradeBooks; } Regards Iftikar Remember to click Mark as Answer on the post if it helps you.

vidhya · Answer

I rectified the namespace error but now i am getting the error Error 11 Cannot implicitly convert type 'System.Collections.Generic.List ' to 'System.Collections.Generic.List ' D:\MavinLMS\MavinLMSDAL\Repositories\GradeBookRepository.cs 45 20 MavinLMSDAL public List GetStudentsGradeBook(Guid courseSectionId) { int roler = (int)CourseSectionRoster.CourseRoleTypes.Student; var result = from p in _context.Person2 join cs in _context.CourseSectionRoster on p.PersonId equals cs.PersonId join asn in _context.AssignmentSubmission on cs.CourseSectionRosterId equals asn.CourseSectionRosterId join a in _context.Assignments on asn.AssignmentId equals a.AssignmentId where cs.CourseSectionId == courseSectionId && cs.CourseRole == roler select new {p.FirstName  p.LastName  a.AssignmentName  a.MakeAvailable  a.PointsPossible  a.DueDateTime }; return result .ToList(); }

vidhya · Answer

its in same project

Santhosh Kumar Jayaraman · Answer

check the name space of this file and new class

Iftikar Hussain · Answer

Is the new class in same project or different? please check Regards Iftikar

vidhya · Answer

I created a class but i cannot able to access that class. shows error you are missing directory or assembly. public class StudentGradeBook { // public Guid CourseSectionRosterId { get; set; } // public CourseSectionRoster.CourseRoleTypes Foo { get; set; } public Guid PersonId { get; set; } //public string TheUserName { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public Guid CourseSectionRosterId { get; set; } public int PointsAwarded { get; set; } public DateTime SubmissionDateTime { get; set; } public int CourseRole { get; set; } public string AssignmentName {get; set;} //public int MakeAvailable {get; set;} public int PointsPossible{get; set;} public DateTime DueDateTime{get; set;} } public List GetStudentsGradeBook(Guid courseSectionId) { }

Santhosh Kumar Jayaraman · Answer

Then how you said return type as Assignment class in your method? If you are just going to return type of Assignment  then you no need to include first name and lastname right? If you want to return mix of both then as Vulpes said create custom class PersonAssignment with all the properties you wanted and use that afterwards

Vulpes · Answer

Create a custom class which contains exactly the fields you want and return a List of objects of that class.

vidhya · Answer

Assignment class contains assignmentnamemakeavailablepointspossibleduedatetime fields Person2 class contains firstnamelastname fields Public list getstudentsgradebook(guid id) but i need to two class fields. how do i fetch these 2 class field values.

Iftikar Hussain · Answer

use like this after your linq statement var result = from p in db.person2 join cs in db.coursesectionroster on p.personid equals cs.personid oin asn in db.assignmentsubmission on cs.coursesectionrosterid equals asn.coursesectionrosterid join a in db.assignment on asn.assignmentid equals a.assignmentid where cs.coursesectionid == b78a6efe-ac77-4e49-806a-fc2fad71068b && cs.courserole == 2 select new Assignment { FirstName= p.firstname L lastName = p.lastname  Assignmentname = a.assignmentname  MakeAvailable = a.makeavailable  PointsPossible = a.pointspossible  DueDatetime = a.duedatetime }; var assignments = new List Assignment >(result.ToList()); Regards Iftikar

Santhosh Kumar Jayaraman · Answer

public List GetStudentsGradeBook(Guid courseSectionId) { int roler = (int)CourseSectionRoster.CourseRoleTypes.Student; List assignmentList=new List (); var q = from p in _context.Person2 join cs in _context.CourseSectionRoster on p.PersonId equals cs.PersonId join asn in _context.AssignmentSubmission on cs.CourseSectionRosterId equals asn.CourseSectionRosterId join a in _context.Assignments on asn.AssignmentId equals a.AssignmentId where cs.CourseSectionId == courseSectionId && cs.CourseRole == roler select new {p.FirstName  p.LastName  a.AssignmentName  a.MakeAvailable  a.PointsPossible  a.DueDateTime }; foreach(var item in q) { Assignment a=new Assignment(){FirstName=item.FirstName LastName=item.LastName AssignmentName=item.AssignmentName MakeAvailable=item.MakeAvailable PointsPossible =item.PointsPossible }; assignmentList.Add(a); } return assignmentList; } This should work. The issue was we are getting new anonymous type with selected columns from different table. So at the end you have to assign these columns to the properties of Assignment Class. I am not sure about the names of the properties in Assignment Class. So i used same name. Modify the names as you have in your class to make it work.

Vulpes · Answer

That's because the return types of methods can't be anonymous types or collections of anonymous types. If you want to return something to outside code then create a 'named' class and use that instead of the anonymous type.

vidhya · Answer

Iftika I get same error when i implement your code in my application. it was shown in above reply.

vidhya · Answer

santhosh. it does not show error when i implement your code in application i got this error. Cannot implicitly convert type 'System.Collections.Generic.List ' to 'System.Collections.Generic.List ' D:\MavinLMS\MavinLMSDAL\Repositories\GradeBookRepository.cs public List GetStudentsGradeBook(Guid courseSectionId) { int roler = (int)CourseSectionRoster.CourseRoleTypes.Student; var q = from p in _context.Person2 join cs in _context.CourseSectionRoster on p.PersonId equals cs.PersonId join asn in _context.AssignmentSubmission on cs.CourseSectionRosterId equals asn.CourseSectionRosterId join a in _context.Assignments on asn.AssignmentId equals a.AssignmentId where cs.CourseSectionId == courseSectionId && cs.CourseRole == roler select new {p.FirstName  p.LastName  a.AssignmentName  a.MakeAvailable  a.PointsPossible  a.DueDateTime }; return q.ToList(); }

Iftikar Hussain · Answer

Hi sorry Please check now var result = from p in db.person2 join cs in db.coursesectionroster on p.personid equals cs.personid oin asn in db.assignmentsubmission on cs.coursesectionrosterid equals asn.coursesectionrosterid join a in db.assignment on asn.assignmentid equals a.assignmentid where cs.coursesectionid == b78a6efe-ac77-4e49-806a-fc2fad71068b && cs.courserole == 2 select new { p.firstname p.lastname a.assignmentname a.makeavailable a.pointspossible a.duedatetime }; Regards Iftikar Remember to click Mark as Answer on the post if it helps you.

Santhosh Kumar Jayaraman · Answer

hi Vidhya have you tried mine?

vidhya · Answer

Iftikar It shows error in the line join cs in _context.CourseSectionRoster on cs.personid equals p.personid cs is not scope of the right side equals for p also it shows this error.

Santhosh Kumar Jayaraman · Answer

try this var q = from p in person2 join cs in coursesectionroster on p.personid equals cs.personid join asn in assignmentsubmission on cs.coursesectionrosterid equals asn.coursesectionrosterid join a in assignment on asn.assignmentid equals a.assignmentid where cs.courserole==2 && cs.coursesectionid==Guid.Parse(b78a6efe-ac77-4e49-806a-fc2fad71068b) select new {p.firstnamep.lastnamea.assignmentnamea.makeavailablea.pointspossiblea.duedatetime};

Iftikar Hussain · Answer

Hi Here it is var result = from p in db.person2 join cs in db.coursesectionroster on cs.personid equals p.personid join asn in db.assignmentsubmission on asn.coursesectionrosterid equals cs.coursesectionrosterid join a in db.assignment on a.assignmentid equals asn.assignmentid where cs.coursesectionid == b78a6efe-ac77-4e49-806a-fc2fad71068b && cs.courserole == 2 select new { p.firstname p.lastname a.assignmentname a.makeavailable a.pointspossible a.duedatetime }; Let me know if you need more information Regards Iftikar Remember to click Mark as Answer on the post if it helps you.