Why is my code doing lazy loading even after I turned it off at every possible point?

Question

I would like to get Exams and Test entities that have a UserTest entity with a UserId that is either equal to "0" or to a provided value. I had a number of suggestions but so far none have worked. One suggestion was to start by getting UserTest data and the other solution was to start by getting Exam data. Here's what I have when I used the UserTests as the source starting point.

I have the following LINQ:

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

When I check _uow.UserTests with the debugger it's a repository and when I check the dbcontext's configuration.lazyloading then it is set to false.

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; }
}

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 it gets a UserTest object, then gets a test object and then an exam object. However the exam object contains a test collection and then it heads back down again and gets the different tests and the unit tests inside of those:

UserTest > Test > Exam > Test > UserTest ?

I have tried hard to ensure lazy loading is off and debug tell me it's set to false. I am using EF6 and WebAPI but not sure if that makes a difference as I am debugging at the C# level.

Check this link for EF6 lazy loading.. [http://stackoverflow.com/questions/18917423/entity-framework-eager-load-not-returning-data-lazy-load-does][1] [1]: http://stackoverflow.com/questions/18917423/entity-framework-eager-load-not-returning-data-lazy-load-does — petchirajan, Mar 25 '14 at 06:41
I checked this link and decided to remove the virtual keywords. This did not change anything. It still loaded in a circular fashion multiple copies of everything. — , Mar 25 '14 at 08:57
note that turning off the Lazy loading doesn't make sense unless you do its requirements and considerations based on its logic. for example using `virtual` attribute for navigational properties is a key feature of lazy loading! — Amirhossein Mehrvarzi, Mar 25 '14 at 10:00

score 9 · Accepted Answer · answered Mar 27 '14 at 22:33

You can't avoid that the inverse navigation properties are populated by EF, no matter if you load related entities with eager or lazy loading. This relationship fixup (as already explained by @Colin) is a feature you can't turn off.

You could solve the problem by nullifying the unwished inverse navigation properties after the query is finished:

foreach (var userTest in userTests)
{
    if (userTest.Test != null)
    {
        userTest.Test.UserTests = null;
        if (userTest.Test.Exam != null)
        {
            userTest.Test.Exam.Tests = null;
        }
    }
}

However, in my opinion the flaw of your design is that you try to serialize entities instead of data transfer objects ("DTOs") that are specialized to the view where you want to send the data to. By using DTOs you can avoid the inverse navigation properties that you don't want altogether and maybe other entity properties that you don't need in your view. You would define three DTO classes, for example:

public class ExamDTO
{
    public int ExamId { get; set; }
    public int SubjectId { get; set; }
    public string Name { get; set; }
    // no Tests collection here
}

public class TestDTO
{
    public int TestId { get; set; }
    public string Title { get; set; }
    // no UserTests collection here

    public ExamDTO Exam { get; set; }
}

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

    public TestDTO Test { get; set; }
}

And then use a projection to load the data:

var userTests = _uow.UserTests
    .GetAll()
    .Where(ut => ut.UserId == "0" || ut.UserId == userId)
    .Select(ut => new UserTestDTO
    {
        UserTestId = ut.UserTestId,
        UserId = ut.UserId,
        QuestionsCount = ut.QuestionsCount,
        Test = new TestDTO
        {
            TestId = ut.Test.TestId,
            Title = ut.Test.Title,
            Exam = new ExamDTO
            {
                ExamId = ut.Test.Exam.ExamId,
                SubjectId = ut.Test.Exam.SubjectId,
                Name = ut.Test.Exam.Name
            }
        }
    })
    .ToList();

You could also "flatten" the object graph by defining only a single DTO class that contains all the properties you need for the view:

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

    public int TestId { get; set; }
    public string TestTitle { get; set; }

    public int ExamId { get; set; }
    public int ExamSubjectId { get; set; }
    public string ExamName { get; set; }
}

The projection would become simpler and look like this:

var userTests = _uow.UserTests
    .GetAll()
    .Where(ut => ut.UserId == "0" || ut.UserId == userId)
    .Select(ut => new UserTestDTO
    {
        UserTestId = ut.UserTestId,
        UserId = ut.UserId,
        QuestionsCount = ut.QuestionsCount,
        TestId = ut.Test.TestId,
        TestTitle = ut.Test.Title,
        ExamId = ut.Test.Exam.ExamId,
        ExamSubjectId = ut.Test.Exam.SubjectId,
        ExamName = ut.Test.Exam.Name
    })
    .ToList();

By using DTOs you do not only avoid the problems of inverse navigation properties but also follow good security practices to "white-list" the exposed property values from your database explicitly. Imagine you would add a test access Password property to the Test entity. With your code that serializes eagerly loaded full entities with all properties the password would get serialized as well and run over the wire. You don't have to change any code for this to happen and in the worst case you wouldn't be aware that you are exposing passwords in a HTTP request. On the other hand when you are defining DTOs a new entity property would only be serialized with your Json data if you add this property explicitly to the DTO class.

Thank you for your very detailed answer and for your suggestions. I really appreciate your good advice and I will check out your suggestions and let you know if the work okay for me. One small question. In my models I am setting up my relationships using the virtual keyword. I'm a bit confused. Is this really needed and if so what difference is there between having it and not having it. Thanks — , Mar 28 '14 at 05:18
@Melina: Marking the navigation properties as `virtual` is required to make lazy loading possible. If they are not `virtual` lazy loading doesn't work, even if you set `LazyLoadingEnabled` to `true`. However, for your query and for the examples in my answer lazy loading is not needed. The question is only if you have other parts in your application where you want to use lazy loading. In that case you should keep the `virtual` keyword in place. If you don't use lazy loading anywhere you can remove the `virtual`. — Slauma, Mar 28 '14 at 13:00

Colin · Answer 2 · 2014-03-27T15:48:09.853

4

Your query will load all UserTests into the context where UserId == "0" || UserId == userId and you have eagerly loaded the related Test and its related Exams.

Now in the debugger you can see that the Exams are linked to some Tests in memory and are assuming that is because they have been lazy-loaded. Not true. They are in memory because you loaded all UserTests into the context where UserId == "0" || UserId == userId and you have eagerly loaded the related Test. And they are linked to the navigation property because EF performs a "fix-up" based on foreign keys.

The Exam.Tests navigation property will contain any entities loaded into the context with the correct foreign key, but will not necessarily contain all Tests linked to the Exam in the database unless you eagerly load it or turn on lazy loading

edited Mar 27 '14 at 15:48

answered Mar 25 '14 at 10:07

Colin

22,328
17
103
197

Thanks for your explanation. It does make sense but I am still not sure how to solve my problem. Would there be an easier way to do this if I was to go with a query like: var exams = _examsRepository .GetAll() .Where(q => q.SubjectId == subjectId) .Include(q => q.Tests.Select(t => t.UserTests)) .ToList(); This was what I had on my first attempt but with this query I do not know how to do a Where against the UserID in UserTests. – Mar 25 '14 at 10:14
You mention what was loaded. The output I show was what I saw coming across from the Web API to the browser. When I use the above code that's in my comment against the _examsRepository everything looks cleaner but I still don't know if I could only get a where to work with the UserID. – Mar 25 '14 at 10:17
Not sure I understand what you are trying to achieve. This tutorial might help: http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/reading-related-data-with-the-entity-framework-in-an-asp-net-mvc-application – Colin Mar 25 '14 at 10:48

score 2 · Answer 3 · answered Mar 25 '14 at 09:47

2

I believe that deferred execution causes nothing to happen unless something is actually read from userTests. Try to include var userTestsAsList = userTests.ToList() and check with the debugger if userTestsAsList contains the desired sequence.

answered Mar 25 '14 at 09:47

Codor

17,447
9
29
56

thanks. There was actually a .ToList() there but I missed it when entering the question. My problem is still there after adding the .ToList(). I upvoted your answer to thank you. – Mar 25 '14 at 09:59
You're welcome; however I do not fully understand how the achieved result differs from your expectation. Could you clarify this a bit? – Codor Mar 25 '14 at 10:07
My problem is that I want only UserTest > Test > Exam details but what I am getting is UserTest > Test > Exam > Test > UserTest ? Those last two add a lot of additional data to what is sent back. – Mar 25 '14 at 10:24

Dave Alperovich · Answer 4 · 2014-03-27T21:52:07.423

As far as I can read your POCO-Relationships and your query, your Repo is returning what you asked for. But did you know you asked for this?

You are navigating from Grand-Child <UserTest> to Child <Test> to Parent <Exam>

Your Entity of <Exam> is being treated as a Grand-Child when it seems to be a Grand-Parent (in fact, a graph root) having children of <Test> who have children / grand-children of type <UserTest>.

As you are eager loading (and serializing?), of course your <Exam> should eager load its <Test> Collection, which should load their <UserTest> Collections.

By working your way down the graph, you are causing a full circle.

Did you mean to have the opposite relationships?

public class Exam
{
    public int ExamId { get; set; }
    public int TestId { get; set; }
    public int SubjectId { get; set; }
    public string Name { 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; }
    public virtual ICollection<Exam> Exams { get; set; }
}

I'm making many assumptions about your data. This relationships simply makes more sense as real-world entities and by your usage. That Users have Tests and Exams rather than the reverse. If so, this relationship should work with your linq query.

Sorry if maybe my explanation was not so clear. What I have is for a single exam type "French 101" to have more than one test "French 101 - part 1", "French 101 - part 2". Then each user takes a test so that data would be stored in the UserTest Table. Hope this makes a bit more sense. What really confuses me is how I should form the LINQ Query and where I should start. With Exam or UserTest. — , Mar 28 '14 at 04:38
@Melina, I would start with Exam, or possibly even skip exam and create a list of Tests and include UserTests. It's 2am here, but I think I can slap something that works for you tmrw morning (NY time). — Dave Alperovich, Mar 28 '14 at 06:20

Jim Crandall · Answer 5 · 2014-03-28T13:01:08.450

If you try something like:

var userTest = _uow.UserTests
            .GetAll()
            .Where(t => t.UserId == "0" || t.UserId == userId)
            .First();

var name = userTest.Test.Title;

Would your code throw an exception because the Test property hasn't been loaded? I suspect the problem is your repository is using LINQ to SQL and not LINQ to Entities. You can't turn off Lazy Loading with LINQ to SQL. You would have to show how your repository works to fix the problem in that case.

score 1 · Answer 6 · answered Mar 29 '14 at 05:28

1

Is there any reson you are using "virtual" for your collections? If you're using "include", I would recommend getting rid of the "virtual"

answered Mar 29 '14 at 05:28

user547794

14,263
36
103
152

Why is my code doing lazy loading even after I turned it off at every possible point?

6 Answers6

Linked