How can I get a list of students from this database schema?

I have 开发者_JAVA百科the following database:

Student
StudentID
Name
LastName

Grade
GradeId
Name

GradeInstance
GradeInstanceId
GradeId
Name
Year

StudentInstance
StudentInstanceId
GradeInstanceId
StudentInstanceId

How can I retrieve a list of students from a given grade instance?

I'm using Entity Framework as my ORM so what would the LINQ query be like?

Edit: I need to return Student objects, not StudentInstance object because those don't contain the attributes I need to fill the GUI with.

So something like this doesn't work for me, unless I'm missing something here.

dbContext.StudentInstances.Where(s => s.GradeInstanceId == 1);

---

from s in dbContext.Student
join si in dbContext.StudentInst on s.StudentID equals si.StudentInstanceID
join g in dbContext.Grade on si.GradeInstanceID equals g.GradeID
where g.Name = ...
select s;

---

This should help you get started....

    public static List<Student> GetStudents(int gradeId)
    {
        using (var context = new Entities())
        {
            List<Student> myList = (from s in dbContext.Student 
join si in dbContext.StudentInst on s.StudentID equals si.StudentInstanceID
join g in dbContext.Grade on si.GradeInstanceID equals g.GradeID
where g.GradeId = gradeId
select s).ToList();

         return myList;
        }
    }

Using slightly modified query :D

---

you could add a navigation property to your GradeInstance Entity to the collection of Student entities related to it (you actually have the possibility to do that from the Add Association wizard), then you could acess it simply with: gradeInstance.Students

Hope this helps