convert sql query to linq

vidhya

13y3kLINQ

Hi friends,

I need to convert sql query into linq. I have placed my query for your reference.

select p.firstname,p.lastname,a.assignmentname,

a.makeavailable,a.pointspossible,a.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

22 Replies

Know the answer? Post it — somebody with the same question will find it here.

Santhosh Kumar Jayaraman13y ago
Accepted 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;
}

0
Josephs13y ago
hi

Iftikar Hussain good post
0
vidhya13y ago
thanks to every one. i resolved my issue through your guidelines.

I followed santhosh code to resolve that issue. thanks a lot santhosh.
0
Iftikar Hussain13y ago
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,
LlastName=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.
0
vidhya13y ago
I rectified the namespace error but now i am getting the error

Error11Cannot implicitly convert type 'System.Collections.Generic.List' to 'System.Collections.Generic.List'D:\MavinLMS\MavinLMSDAL\Repositories\GradeBookRepository.cs4520MavinLMSDAL

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();

}
0
vidhya13y ago
its in same project
0
Santhosh Kumar Jayaraman13y ago
check the name space of this file and new class
0
Iftikar Hussain13y ago
Is the new class in same project or different? please check

Regards,
Iftikar
0
vidhya13y ago
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)
{
}
0
Santhosh Kumar Jayaraman13y ago
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
0
Vulpes13y ago
Create a custom class which contains exactly the fields you want and return a List of objects of that class.
0
vidhya13y ago
Assignment class contains assignmentname,makeavailable,pointspossible,duedatetime fields

Person2 class contains firstname,lastname fields

Public listgetstudentsgradebook(guid id)

but i need to two class fields. how do i fetch these 2 class field values.
0
Iftikar Hussain13y ago
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,
LlastName=p.lastname,
  Assignmentname=a.assignmentname,
  MakeAvailable=a.makeavailable,
  PointsPossible=a.pointspossible,
  DueDatetime=a.duedatetime
};

var assignments= new List<Assignment>(result.ToList());

Regards,
Iftikar
0
Santhosh Kumar Jayaraman13y ago
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.
0
Vulpes13y ago
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.
0
vidhya13y ago
Iftika,

I get same error when i implement your code in my application.

it was shown in above reply.
0
vidhya13y ago
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();
}
0
Iftikar Hussain13y ago
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.
0
Santhosh Kumar Jayaraman13y ago
hi Vidhya,

have you tried mine?
0
vidhya13y ago
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.
0
Santhosh Kumar Jayaraman13y ago
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.firstname,p.lastname,a.assignmentname,a.makeavailable,a.pointspossible,a.duedatetime};
0
Iftikar Hussain13y ago
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.
0