Question

我有两个表格如下:

 ScholarSubject
ScholarSubjectID<pk>
ScholarID
SubjectID
Mark

和

 AdmissionReq
SubjectID
DegreeCode
MinumumMark

我试图从一个学位表(用PK学位ID)上归还所有东西,在这个表中,学者的标记低于入学最低标记。

public List<object> getDegreeByAPS和Requirements()
    {
        using (DataLayer.CareerDatabaseEntities context = new DataLayer.CareerDatabaseEntities())
        {
            return (from Degrees in context.Degrees
                    join admissions in context.AdmissionReqs on
                    Degrees.DegreeCode equals admissions.DegreeCode
                    join subject in context.Subjects on
                    admissions.SubjectID equals subject.SubjectID
                    join scholarsubject in context.ScholarSubjects on
                    subject.SubjectID equals scholarsubject.SubjectID
                    join scholar in context.Scholars on
                    scholarsubject.ScholarID equals scholar.ScholarID
                    where Degrees.APSScore <= scholar.APSScore && admissions.MinimumMark <= scholarsubject.NSC && scholarsubject.SubjectID.Equals(admissions.SubjectID)
                    select Degrees).Distinct().ToList<object>();

        }
    }

Everything works, except if I change one of the marks (in ScholarSubject) to a lesser value than the minumum mark (in AdmissionsReq) then it still returns a degree. I want to return a degree if both marks are greater than the minimum requirements 和 not only one of the marks.

我做错什么了有人能帮帮我吗?

Answer 1

我还是不明白你到底想做什么,除非你的数据库里只有一个学者,在我看来,你应该归还一份所有学者取得的所有学位的清单。如果你不想这样做,就需要在 < code> 的 条款中过滤学者ID。



但无论如何 为了获得更多的信息 我会尝试做两件事


我将更改您的查询, 并以  scollar  而不是  度  开始, 因为这样可以避免一些重复 :

    from scholar in context.Scholars 
    join scholarsubject in context.ScholarSubjects on scholar.ScholarID equals scholarsubject.ScholarID
    join subject in context.Subjects on scholarsubject.SubjectID equals subject.SubjectID
    join admission in context.AdmissionReqs on subject.SubjectID equals admission.SubjectID
    join degree in context.Degrees on admission.DegreeCode equals degree.DegreeCode
    where degree.APSScore <= scholar.APSScore 
        && admission.MinimumMark <= scholarsubject.NSC 
      //&& scholarsubject.SubjectID.Equals(admission.SubjectID) // you should not need this line as you have the joins in place to assert this
    select degree)
   .Distinct()
   .ToList<object>();

如果此结果与您上一个查询结果相同, 那么我会更改返回类型, 以查看您正在获取什么 - 用此替换最后一行, 并检查收藏 :

 select new {ScholarID = scholar.ScholarID, Degree = degree})
   .Distinct()
   .ToList();

友情链接