How do I do a table join using LINQ and entity framework 6?

asp.net c# entity-framework linq sql

Question

I have three tables: Exam > Objectives > ObjectiveDetails

Here's what they look like:

public class Exam
{
    public Exam()
    {
        this.Objectives = new HashSet<Objective>();
    }
    public int ExamId { get; set; }
    public int SubjectId { get; set; }
    public virtual ICollection<Objective> Objectives { get; set; }
}

public class Objective : AuditableTable
{
    public Objective()
    {
        this.ObjectiveDetails = new HashSet<ObjectiveDetail>();
    }
    public int ObjectiveId { get; set; }
    public int ExamId { get; set; }
    public int Number { get; set; }
    public virtual Exam Exam { get; set; }
    public virtual ICollection<ObjectiveDetail> ObjectiveDetails { get; set; }

}

public partial class ObjectiveDetail
{
    public int ObjectiveDetailId { get; set; }
    public int ObjectiveId { get; set; }
    public int Number { get; set; }
    public string Text { get; set; }
    public virtual Objective Objective { get; set; }
}

Should I start from exam like this:

 var result = await db.Exams
                      .Where(e => e.Name == name)

or from ObjectiveDetails like this:

 var result = db.ObjectiveDetails .. ??

How can I get an object that shows the objectives and objective details for a given exam ? Should I start with db.Exams or should I start with db.ObjectiveDetails? All I need is to do a join (if in the relational only world). But with LINQ I am not sure where to start.

1
2
8/21/2014 11:42:54 AM

Accepted Answer

This is what i normally do when it comes to joining two or more tables, don't know if that's what you are looking for :

var info = from p in db.Exam 
           join q in db.objective on p.objectiveid equals q.id
           join r in db.objectivedetails on q.objectivedeailsId equals r.id
           select new
                       {
                           ExamId  = p.ExamId 
                           SubjectId= p.SubjectId
                           ObjectiveId= q.ObjectiveId
                           Number = q.Number
                           ObjectiveDetailId = r.ObjectiveDetailId
                           Text = r.Text
                       } into x
           select x;
6
8/22/2014 4:25:27 AM

Popular Answer

Assuming Entity Framework has wired up the relationships between your entities correctly, the following should return a single exam, together with associated Objectives and Details:-

var query = db.Exams.Include(e => e.Objectives.Select(o => o.ObjectiveDetails));

var examEntity = query.SingleOrDefault(e => e.ExamId == targetExamId);

The above query will use outer joins behind the scenes, so an Exam will always be returned (if found), even if there are no associated Objectives/Details. The Exam is returned as a single object with child Objectives included if applicable.

Alternatively, the following will return a flattened result set using inner joins:-

var query = from exam in db.Exams
            from objective in exam.Objectives
            from detail in objective.ObjectiveDetails
            select new
            {
                ExamId = exam.ExamId,
                SubjectId = exam.SubjectId
                ObjectiveId = objective.ObjectiveId
                ObjectiveNumber = objective.Number
                DetailId = detail.DetailId
                DetailNumber = detail.Number
                Text = detail.Text
            };

var examDetails = query.Where(e => e.ExamId == targetExamId).ToArray();

There's nothing wrong with using Linq's 'join' keyword explicitly, but it's generally unnecessary if EF knows how entities are related.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow