How can I include more than one level deep in a LINQ query?

Question

I have three SQL tables that are represented by classes and I would like to have Entity Framework 6 join these tables so I get all the details of the Exam, Test and UserTest tables where the UserTest.UserID is 0 or X.

I have already set up a respository and this works for simple queries however I am unable to join the UserTest class in the LINQ at the bottom of the question.

Here's my classes:

public class Exam
{
    public int ExamId { get; set; }
    public int SubjectId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Test> Tests { get; set; }
}

public class Test
{
    public int TestId { get; set; }
    public int ExamId { get; set; }
    public string Title { get; set; }
    public virtual ICollection<UserTest> UserTests { get; set; }
}

public class UserTest
{
    public int UserTestId { get; set; }
    public string UserId { get; set; }
    public int TestId { get; set; }
    public int QuestionsCount { get; set; }
}

What I would like to do is to have a query that looks something like this:

var exams = _examsRepository
           .GetAll()
           .Where(q => q.SubjectId == subjectId)
           .Include(q => q.Tests )
           .Include(q => q.Tests.UserTests) // Error on this line
           .ToList();

But it's not letting me include UserTests in VS2013.

Update:

Here is the query I first tried:

  var userTests = _userTestsRepository
        .GetAll()
        .Include(t => t.Test)
        .Include(t => t.Test.Exam)
        .Where(t => t.UserId == "0" || t.UserId == userId);

This one seemed to work however when I looked at the output I saw something like this:

[{"userTestId":2,
  "userId":"0",
  "testId":12,
  "test":{
      "testId":12,"examId":1,
      "exam":{
          "examId":1,"subjectId":1,
          "tests":[
               {"testId":13,"examId":1,"title":"Sample Test1",
                "userTests":[
                      {"userTestId":3,
                       "userId":"0",

Note that this starts to repeat and bring back a lot more data than I expected

score 1 · Accepted Answer · answered Mar 25 '14 at 05:03

1

That's because Tests is a collection and not just a single object, so it doesn't have a UserTests property. You use a lambda to specify grandchildren of multiple children rather than a single child:

var exams = _examsRepository
           .GetAll()
           .Where(q => q.SubjectId == subjectId)
           .Include(q => q.Tests.Select(t => t.UserTests))
           .ToList();

Note that there's no need for two Include calls because the children are implicitly included if you're including the grandchildren.

answered Mar 25 '14 at 05:03

jmcilhinney

50,448
5
26
46

@jmcihinney - Thanks - In this instance I need to get all records where the UserTest.UserID is "0" or "X". Can you show me how I could add this to the LINQ also. Sorry if you didn't see this it was added to my questin a few mins ago – Mar 25 '14 at 05:07
1

Are you saying that you want all UserTest records where any of them have one of those IDs for a Test or that you only want the UserTest records that have one of those IDs? You can certainly filter the parent records based on properties of their children, grandchildren, etc, but I'm not sure that it's possible to filter the children, etc, that are included in the result set. – jmcilhinney Mar 25 '14 at 05:21
The UserTest table is the only one with a UserId column. I would like to just get a result set so only those with the required UserIds are returned. With SQL it would be something like Join all three tables and then a simple Where UserTests.UserId = ... or .. As an alternative to this should I maybe be going against the UserTest repository and including the Test and Exam? – Mar 25 '14 at 05:24
1

It sounds like you should be querying for the UserTest entity and including the others rather than the other way around... As you suggested. :) – jmcilhinney Mar 25 '14 at 05:28
@jmcihinney - This is what I was doing initially. However what happened was that I included q.Test and q.Exam. When I looked at the output the q.Exam had gone down and included more tests ! Sorry maybe I didn't explain so good but it seem there was some circular including going on. – Mar 25 '14 at 05:31
1

If you don't specify that those extra entities be included then they won't be included. If you can access them then you must have lazy-loading enabled, which you don't want if you want specific child entities. – jmcilhinney Mar 25 '14 at 05:37
@jmcihinney - I updated my question to show what was happening with some test data when I tried to do the LINQ at the UserTest point. – Mar 25 '14 at 05:43

How can I include more than one level deep in a LINQ query?

1 Answers1

Linked