ASP.NET MVC finding pending fees

Question

I'm learning ASP.NET MVC 4 with Entity Framework (Database First) and ran into a problem that I'm not able to solve.

I have following tables:

• Student: To store students information
• Course: To store courses running in an institute
• StudentCourse: To store which student selected what course
• Fees: To store submitted fees by a student corresponding to StudentCourse

Now, I wan't to show course name, course fees, total fees submitted & the pending fees of students I'm using Entity Framework in the project and trying to execute a query like this:

var feeslist = entities.Fees
        .Where(m => m.StudentCourse.status=="pending")
        .GroupBy(m => m.StudentCourse.id)
        .Select(m => new { StudentCourseId = m.Key, SumOfFees = m.Sum(v => v.fees) });

I want to get Course object instead of StudentCourseId so that i can display the course, its course fees and total fees submitted by student.

In case, I'm listing all of the four class contents:

Student

public partial class Student
{
    public Student()
    {
        this.Leaves = new HashSet<Leave>();
        this.StudentCourses = new HashSet<StudentCourse>();
    }

    public int id { get; set; }
    public string first_name { get; set; }
    public string middle_name { get; set; }
    public string last_name { get; set; }
    public string password { get; set; }
    public string email { get; set; }
    public string gender { get; set; }
    public string facebook { get; set; }
    public string contact_number { get; set; }
    public string address { get; set; }
    public string admin { get; set; }
    public string college { get; set; }
    public string status { get; set; }
    public System.DateTime createdat { get; set; }
    public System.DateTime updatedat { get; set; }
    public string descripton { get; set; }

    public virtual ICollection<Leave> Leaves { get; set; }
    public virtual ICollection<StudentCourse> StudentCourses { get; set; }
}

Course

public partial class Course
{
    public Course()
    {
        this.CourseContents = new HashSet<CourseContent>();
        this.StudentCourses = new HashSet<StudentCourse>();
    }

    public int id { get; set; }
    public string course_name { get; set; }
    public int course_fees { get; set; }
    public int duration { get; set; }
    public string admin { get; set; }
    public string status { get; set; }
    public System.DateTime createdat { get; set; }
    public System.DateTime updatedat { get; set; }

    public virtual ICollection<CourseContent> CourseContents { get; set; }
    public virtual ICollection<StudentCourse> StudentCourses { get; set; }
}

StudentCourse

public partial class StudentCourse
{
    public StudentCourse()
    {
        this.Fees1 = new HashSet<Fee>();
        this.Issues = new HashSet<Issue>();
    }

    public int id { get; set; }
    public int student_id { get; set; }
    public int course_id { get; set; }
    public int fees { get; set; }
    public System.DateTime join_date { get; set; }
    public string admin { get; set; }
    public System.DateTime createdat { get; set; }
    public System.DateTime updatedat { get; set; }
    public string status { get; set; }

    public virtual Course Course { get; set; }
    public virtual ICollection<Fee> Fees1 { get; set; }
    public virtual ICollection<Issue> Issues { get; set; }
    public virtual Student Student { get; set; }
}

Fee

public partial class Fee
{
    public int id { get; set; }
    public int student_course_id { get; set; }
    public int fees { get; set; }
    public string admin { get; set; }
    public System.DateTime createdat { get; set; }
    public System.DateTime updatedat { get; set; }

    public virtual StudentCourse StudentCourse { get; set; }
}

Answer 1

var feeslist = entities.Fees
        .Where(m => m.StudentCourse.status=="pending")
        .GroupBy(m => m.StudentCourse.Course)
        .Select(m => new { Course = m.Key, SumOfFees = m.Sum(v => v.fees) });