Chaining linq queries

Question

Attempting to chain linq queries due to creating a simple filter for the datatables in my view.

public ActionResult Index(int? courseId, string grade, int? statusId)
    {
        var query = from s in db.Students
                    join e in db.Enrollments on s.ID equals e.ID
                    join c in db.Courses on e.CourseID equals c.ID
                    orderby e.Grade.HasValue descending, e.Grade, s.StatusID
                    select new { s, e, c };           

        if (courseId != null)
        {
            query = query.Where(q => q.e.CourseID == courseId);
        }

        if (!string.IsNullOrEmpty(grade))
        {
            query = query.Where(q => q.e.Grade.Equals(grade));
        }

        if (statusId != null)
        {
            query = query.Where(q => q.s.StatusID == statusId);
        }

        query.Select( q => new ViewModels.ReportView
                {
                    ID = q.s.ID,
                    Name = q.s.FirstName + " " + q.s.LastName,
                    Course = q.c.Title,
                    Grade = q.e.Grade,
                    GraduationDate = q.s.GraduationDate,
                    Status = q.s.Status.Title
                });

Linq forces you to end queries with a select or group which is forcing me to put an unnecessary select at the start. For some reason this is causing the it to ignore the last select and just use the initial.

I've tried grouping instead by e.ID but that doesn't work as expected...

I'm sure this is simple but I'm lost, any ideas?

Answer 1

I usually create a helper function to simplifier complex linq

   class Test
    {
        public ActionResult Index(int? courseId, string grade, int? statusId)
        {
            var query = from s in db.Students
                        join e in db.Enrollments on s.ID equals e.ID
                        join c in db.Courses on e.CourseID equals c.ID
                        orderby e.Grade.HasValue descending, e.Grade, s.StatusID
                        select QueryResults(courseId, grade, statusId, s, e, c);


        }
        public ViewModels.ReportView QueryResults(int? courseId, string grade, int? statusId, Student student, Enrollments enrollment, Cource course)
        {

            if ((courseId != null) && (courseId != course))
            {
                return null;
            }

            if ((!string.IsNullOrEmpty(grade)) && (grade != enrollment.Grade))
            {
                return null;
            }

            if ((statusId != null) && (statusId != student.StatusID))
            {
                return null;
            }

            return new ViewModels.ReportView
            {
                ID = student.ID,
                Name = student.FirstName + " " + student.LastName,
                Course = course.Title,
                Grade = enrollment.Grade,
                GraduationDate = student.GraduationDate,
                Status = student.Status.Title
            });
        }
    }