Linq to Entities - eager loading using Include()

Question

I've got this really basic table structure:

dbo.tblCategory
dbo.tblQuestion (many to one relationship to tblCategory)
dbo.tblAnswer (many to one relationship to tblQuestion)

So basically, what I'm trying to do is when I load a category, I want to also load all Questions, and all Answers.

Now, I've been able to do this using the following code:

public tblCategory Retrieve(int id)
{
    using (var entities = Context)
    {
        var dto =
            (from t in entities.tblCategory.Include("tblQuestion")
                                           .Include("tblQuestion.tblAnswers")    
                 where t.Id == id
                 select t).FirstOrDefault();

            return entities.DetachObjectGraph(dto);
        }
    }
}

However, I'm not completely enamored with this; if the relationship names change in my model; I'm not going to get a error when building the project. Ideally, I'd like to use a lambda expression; something like this:

public tblCategory Retrieve(int id)
{
    using (var entities = Context)
    {
        var dto =
            (from t in entities.tblCategory.Include(t => t.tblQuestion)
             where t.Id == id
             select t).FirstOrDefault();

        return entities.DetachObjectGraph(dto);
    }
}

Now, with the above snippet; I'm stuck on how to drill down to the Answers table. Any idea on what I could use for a lambda expression for this one?

Solution

OK; I was able to get this to work, with some help from here.

Basically, I need to do this:

public tblCategory Retrieve(int id)
{
    using (var entities = Context)
    {
        var dto =
            (from t in entities.tblCategory.Include(t => t.tblQuestion)
                                           .Include(t => t.tblQuestion.First().tblAnswer)
             where t.Id == id
             select t).FirstOrDefault();

        return entities.DetachObjectGraph(dto);
    }
}

From the link above, I can only dereference tblAnswers on individual items of the questions collection. Here I chose to dereference tblAnswers on the first item of the collection. In reality, this lambda expression is merely used to produce the property path “tblQuestion.tblAnswers”, which will eager load the answers of all questions.

So even though it looks like I'm only pulling the answers for the first question, I'm actually pulling all the answers for all the questions.

Comments

[gronostaj]

If you can't use Include() with lambda expressions, add using System.Data.Entity;. (src)

[Cas Bloem]

Or: using Microsoft.Data.Entity;