JW/EMiS.DataLogic/ScoreManage/LevelScoreDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.ScoreManage;
using EMIS.Entities;
using System.Linq.Expressions;

namespace EMIS.DataLogic.ScoreManage
{
    public class LevelScoreDAL
    {
        public LevelScoreRepository levelScoreRepository { get; set; }
        public LevelSettingRepository levelSettingRepository { get; set; }
        public StudentRepository studentRepository { get; set; }
        public ClassmajorRepository classmajorRepository { get; set; }
        public CollegeRepository collegeRepository { get; set; }
        public ExaminationSubjectRepository examinationSubjectRepository { get; set; }

        public IQueryable<LevelScoreView> GetLevelScoreViewQueryable(Expression<Func<ER_LevelScore, bool>> exp)
        {
            var query = from a in levelScoreRepository.GetList(exp)
                        join b in studentRepository.Entities on a.UserID equals b.UserID
                        join c in classmajorRepository.Entities on b.ClassmajorID equals c.ClassmajorID
                        join d in examinationSubjectRepository.Entities on a.ExaminationSubjectID equals d.ExaminationSubjectID
                        //join e in levelSettingRepository.Entities on new { a.ExaminationSubjectID } equals new { e.ExaminationSubjectID }
                        //into tmp
                        //from ae in tmp.Where(x => a.TotalScore <= x.MaxScore && a.TotalScore >= x.MinScore).DefaultIfEmpty()
                        //into tmp from ae in tmp.DefaultIfEmpty() 

                        select new LevelScoreView
                        {
                            LevelScoreID = a.LevelScoreID,
                            UserName = b.Sys_User.Name,
                            UserID = a.UserID,
                            LoginID = b.Sys_User.LoginID,
                            StudentCardNo = b.StudentCardNo,
                            SchoolyearID = a.SchoolyearID,
                            SchoolyearCode = a.CF_Schoolyear.Code,
                            Years = c.CF_Grademajor.GradeID,
                            ClassmajorID = c.ClassmajorID,
                            ClassmajorName = c.Name,
                            CollegeID = c.CF_Grademajor.CF_Facultymajor.CollegeID,
                            CollegeName = c.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            StandardID = c.CF_Grademajor.CF_Facultymajor.StandardID,
                            ExaminationSubjectID = a.ExaminationSubjectID,
                            ExaminationSubjectName = d.Name,
                            ScoreNo = a.ScoreNo,
                            TotalScore = a.TotalScore,
                            ExaminationDate = a.ExaminationDate,
                            //LevelName = ae.LevelName,
                            //ValidDate = a.ValidDate,
                            Remark = a.Remark,
                            //MaxScore = ae.MaxScore.Value
                        };

            return query;
        }

    }
}