CODEWITHSUNDEEP
linqentity-frameworkjoinasp.net-mvc-5
Razer
Razer

Reputation: 47

Performing a join in LINQ using SQL Syntax

I'm currently learning the Entity Framework and am trying to build an enrollment system. Right now I'm trying to display the courses that a student is enrolled in by using an association table called "EnrollmentModel". Normally if this were SQL my query would be something like:

SELECT * 
FROM EnrollmentModel as e, StudentModel as s, CourseModel as c
WHERE s.ID = e.StudentID AND e.CourseID = c.ID

So is there a way to just do that using LINQ? Or do I have to 'translate' the syntax from SQL to LINQ (which I have no idea how to do).

My Schema:

public class StudentModel
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public DateTime EnrollmentDate { get; set; }

    public virtual ICollection<EnrollmentModel> Enrollments {get; set; }
}

public class EnrollmentModel
{
    public int ID { get; set; }
    public int CourseID { get; set; }
    public int StudentID { get; set; }
    public Grade? Grade { get; set; }

    public virtual StudentModel Students { get; set; }
    public virtual CourseModel Courses { get; set; }
}

public class CourseModel
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ID { get; set; }
    public string Title { get; set; }
    public int Credits { get; set; }

    public virtual ICollection<EnrollmentModel> Enrollments  { get; set; }
}

Upvotes: 0

Views: 98

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

If you change the SQL query to explicit join syntax;

SELECT * 
FROM EnrollmentModel as e
JOIN StudentModel AS s ON e.StudentID = s.ID
JOIN CourseModel  AS c ON e.CourseID  = c.ID

...it's quite straight forward to translate to LINQ into an anonymous type;

var result = 
    from e in db.EnrollmentModel
    join s in db.StudentModel on e.StudentID equals s.ID
    join c in db.CourseModel  on e.CourseID  equals c.ID
    select new {e,s,c};

Upvotes: 0

Related Questions