我如何在LINQ查询中加入多个级别?[英] How can I include more than one level deep in a LINQ query?

问题描述

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

推荐答案

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.