convert sql to linq -- left join -- getting error

Question

need to convert sql to linq. select p . firstname  p . lastname  a . assignmentname  asg . pointsawarded  a . pointspossible  a . duedatetime  asg . submissiondatetime from person2 p inner join coursesectionroster cs on cs . personid = p . personid inner join assignment a on a . coursesectionid = cs . coursesectionid left join assignmentsubmission asg on a . assignmentid = asg . assignmentid where cs . coursesectionid = 'b78a6efe-ac77-4e49-806a-fc2fad71068b' and cs . courserole = 2

Jignesh Trivedi · Answer

hi so ithing there is problem with duedate column try following code StudentGradeBook sgb = new StudentGradeBook() { FirstName = item.FirstName LastName = item.LastName AssignmentName = item.AssignmentName PointsPossible = item.PointsPossible DueDateTime = item.DueDateTime.HasValue ? Convert.ToDateTime(item.DueDateTime) : DateTime.Now; PointsAwarded = item.PointsAwarded SubmissionDateTime =item.SubmissionDateTime }; lsgb.Add(sgb); } hope this will help you.

vidhya · Answer

Even though it is getting error. It is runtime error.

Iftikar Hussain · Answer

Hi Download this tool. You can easily convert any sql query to Linq. http://www.sqltolinq.com/ Regards Iftikar

Jignesh Trivedi · Answer

hi why are you cast datatime to nullable datetime and agian check for hasvalue? if you commit highlighted line the is it work fine? is it run time error or compiletime error?

vidhya · Answer

foreach (var item in result) { StudentGradeBook sgb = new StudentGradeBook() { FirstName = item.FirstName LastName = item.LastName AssignmentName = item.AssignmentName PointsPossible = item.PointsPossible DueDateTime = Convert.ToDateTime(item.DueDateTime) PointsAwarded = item.PointsAwarded SubmissionDateTime =( (DateTime ?)item.SubmissionDateTime).HasValue ?Convert .ToDateTime (item .SubmissionDateTime ):Convert .ToDateTime (1/1/0001) }; lsgb.Add(sgb); } I am getting error in foreach after adding the condition. Error message is The cast to value type 'DateTime' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

Jignesh Trivedi · Answer

hi you can also use conditional operator in this case like DueDateTime = item.DueDateTime.HasValue ? Convert.ToDateTime(item.DueDateTime) : DateTime.Now; hope this will help you.

vidhya · Answer

After adding (DateTime?) in select I am getting the error Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment simple name or member access.

Santhosh Kumar Jayaraman · Answer

also make this change in linq select clause select new {p.FirstName  p.LastName  a.AssignmentName  a.MakeAvailable  a.PointsPossible  a.DueDateTimeagn.PointsAwarded (DateTime ?) agn.SubmissionDateTime };

Santhosh Kumar Jayaraman · Answer

Just make DueDatetime property as nullable datetime in your studentGradebook class

vidhya · Answer

I rectified the above problem. I am getting error in foreach Error message is The cast to value type 'DateTime' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type. I used public DateTime? SubmissionDateTime { get; set; } still i am getting error. public List GetStudentsGradeBook(Guid courseSectionId) { int roler = (int)CourseSectionRoster.CourseRoleTypes.Student; List lsgb = new List (); var result = from p in _context.Person2 join cs in _context.CourseSectionRoster on p.PersonId equals cs.PersonId join a in _context.Assignments on cs.CourseSectionId equals a.CourseSectionId join asg in _context.AssignmentSubmission on a.AssignmentId equals asg.AssignmentId into asgn from agn in asgn.DefaultIfEmpty () where cs.CourseSectionId == courseSectionId && cs.CourseRole == roler select new {p.FirstName  p.LastName  a.AssignmentName  a.MakeAvailable  a.PointsPossible  a.DueDateTimeagn.PointsAwardedagn.SubmissionDateTime }; foreach (var item in result) { StudentGradeBook sgb = new StudentGradeBook() { FirstName =item.FirstName  LastName =item.LastName  AssignmentName = item.AssignmentName PointsPossible = item.PointsPossible DueDateTime = Convert.ToDateTime(item.DueDateTime) }; lsgb.Add(sgb); } return lsgb.ToList(); }

vidhya · Answer

thats different query  in this i used left join i didnt know how to implement it in linq. I used defaultifempty. but it does not work. it work like inner join. Var q= From p in _context.Person2 Join cs in _context.CourseSectionRosteron p.PersonId Equals cs.PeriodId Join a in _context.Assignment on a.CourseSectionId equals cs.CoursesectionId Join asg in _Context.AssignmentSubmission .DefaultIfEmpty() on a.AssignmentId equals asg.AssignmentId where cs.CourseSectionId == courseSectionId && cs.CourseRole == roler select new {p.FirstName  p.LastName  a.AssignmentName  a.MakeAvailable  a.PointsPossible  a.DueDateTimeasg.PointsAwardedasg.SubmissionDateTime };

Santhosh Kumar Jayaraman · Answer

I believe you asked similar question yesterday also. Here is the linq. List assignmentList=new List (); var q = from a in _context.Assignment join cs in _context.CourseSectionRoster on a.coursesectionid equals c.coursesectionid join p in _context.person2 on cs.personid equals p.personid join a in _context.Assignments on asn.AssignmentId equals a.AssignmentId where a.coursesectionid == 'b78a6efe-ac77-4e49-806a-fc2fad71068b' && c.courserole==2 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); } I advise you to understand the query and start writing your own going forward.