JW/EMISOnline/EMISOnline.DataLogic/Educational/StudentsDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMISOnline.Entities;
using EMISOnline.DataLogic.Repositories;
using EMISOnline.ViewModel;
using System.Linq.Expressions;
using EMISOnline.ViewModel.Educational;

namespace EMISOnline.DataLogic.Educational
{
    public class StudentsDAL
    {
        public StudentRepository StudentRepository { get; set; }
        public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        public StudentContactRepository StudentContactRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }

        public IQueryable<StudentView> GetStudentList()
        {
            var query = from u in UserRepository.Entities
                        join s in StudentRepository.Entities on u.UserID equals s.UserID 
                        join rs in RecruitstudentsRepository.Entities on s.UserID equals rs.UserID 
                        join c in ClassmajorRepository.Entities on s.ClassmajorID equals c.ClassmajorID 
                        join g in GrademajorRepository.Entities on c.GrademajorID equals g.GrademajorID 
                        join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID 
                        join col in CollegeRepository.Entities on f.CollegeID equals col.CollegeID 
                        join sc in StudentContactRepository.Entities on s.UserID equals sc.UserID into stuCont
                        from stc in stuCont.DefaultIfEmpty()
                        join dic in DictionaryItemRepository.Entities.Where(d => d.DictionaryCode == (typeof(CF_STUDENTSTATUS).Name)) on s.StudentStatus equals dic.Value into sudentStatus
                        from stua in sudentStatus.DefaultIfEmpty()
                        join std in DictionaryItemRepository.Entities.Where(d => d.DictionaryCode == (typeof(CF_Standard).Name)) on f.StandardID equals std.Value 
                        join esy in SchoolyearRepository.Entities on rs.EnteringSchoolYearID equals esy.SchoolyearID 
                        select new StudentView
                        {
                            UserID = u.UserID,
                            LoginID = u.LoginID,
                            Name = u.Name,
                            CollegeID = col.CollegeID,
                            CollegeName = col.Name,
                            StandardName = std.Name,
                            SchoolyearID = esy.SchoolyearID,
                            SchoolyearName = esy.Code,

                            StudentStatus = s.StudentStatus,
                            StudentStatusName = stua.Name,
                            StudentType = s.StudentType,
                            CreateTime = s.CreateTime
                        };
            return query;
        }
    }
}