Entity Framework Include() is not working

I have the following EF query:

TestEntities db = new TestEntities();
var questions = from q in db.Questions.Include("QuestionType")
                from sq in db.SurveyQuestions
                where sq.Survey == surveyTypeID
                orderby sq.Order
                select q;

foreach( var question in questions ) {
    // ERROR: Null Reference Exception
    Console.WriteLine("Question Type: " + question.QuestionType.Description);
}
  

I am getting a null reference exception when I access the QuestionType property. I am using Include("QuestionType") but it doesn't appear to be working. What am I doing wrong?

Edit: It does not throw a null reference exception when I have Lazy Loading turned on.

Edit: Include() seems to be working when i do the following:

var questions = db.Questions.Include("QuestionType").Select(q => q);

When I predicate on a separate entity Include seems to fail. Is that not allowed when using Include? What about my query is causing this thing to not work?

The problem might be related to the subquery in your Linq expression. Subselects, grouping und projections can cause eager loading with Include to fail silently, as mentioned here and explained in more detail here (see answers of Diego Vega somewhere in the middle of the thread).

Although I cannot really see that you violate any of the rules to follow when using Include as described in those posts, you could try to change the query according to the recommendation:

var questions = from q in db.Questions
                from sq in db.SurveyQuestions
                where sq.Survey == surveyTypeID
                orderby sq.Order
                select q;

var questionsWithInclude = ((ObjectQuery)questions).Include("QuestionType");

foreach( var question in questionsWithInclude ) {
    Console.WriteLine("Question Type: " + question.QuestionType.Description);
}

(Or use the extension method mentioned in the posts.)

If I understand the linked posts correctly, this does not necessarily mean that it will work now (probably not), but you will get an exception giving you more details about the problem.

Add "System.Data.Entity" and you will be able to call Include on IQueryable:

var questions = from q in db.Questions
                from sq in db.SurveyQuestions
                where sq.Survey == surveyTypeID
                orderby sq.Order
                select q;

questions = questions.Include("QuestionType");

see : https://stackoverflow.com/questions/4766122/how-can-i-convert-a-dbqueryt-to-an-objectqueryt

I ran into this issue of Include(e => e.NavigationProperty) not working, but the solution was different than above.

The problematic code was as follows:

    UserTopic existingUserTopic = _context.UserTopics
            .Include(ut => ut.Topic)
            .FirstOrDefault(t => t.UserId == currentUserId && t.TopicId == topicId);

        if (existingUserTopic != null)
        {
            var entry = _context.Entry(existingUserTopic);
            entry.State = EntityState.Deleted;
             
            if (existingUserTopic.Topic.UserCreated) 
            {
                var topicEntry = _context.Entry(existingUserTopic.Topic);
                entry.State = EntityState.Deleted;
            }

            await _context.SaveChangesAsync();
        }

So the problem was the order of the code. Entity Framework appears to nullify navigation properties in memory as soon as an entity is marked as EntityState.Deleted. So to access existingUserTopic.Topic in my code, I have to do it before marking existingUserTopic deleted.

As this question is the top search result for "Entity Framework Include not working" I'm just going to mention a couple of other possibilities even though neither are relevant for @Dismissile's original post.

Case Sensitivity

SQL Server (and possibly other database platforms) often work in a case-insensitive manner. So, if you have a primary key value ABC1, the database will accept ABC1, abc1, AbC1 etc as valid foreign key values. However, .Net string comparisons are case-sensitive by default, so even if your .Include is generating the extra SQL to pull the extra values into EF, it might fail to populate child objects if there is a case difference in the keys. This is discussed in a bit more depth in this SO question with a couple of good links. Using a case sensitive collation for primary key and foreign key columns can reduce the risk of this cause of .Include failure.

Trailing Spaces

This is the one that caused me to lose a day of my life trying to work out why my .Include wasn't working. SQL Server (and possibly other database platforms) often ignore trailing spaces in string comparisons. So, if you have a primary key value (not including the quotes) "ABC " (one trailing space), the database will accept "ABC " (one space), "ABC" (no space), "ABC " (2 spaces) etc as valid foreign key values. However, .Net string comparisons don't ignore trailing spaces, so even if your .Include is generating the extra SQL to pull the extra values into EF, it might fail to populate child objects if there are differences in trailing spaces in the keys. The SQL Server behaviour is described in this MS Support page. I've not worked out a good strategy for preventing this sort of .Include failure other than careful data management, i.e. don't let users type foreign key values - use a dropdown list, or religously rtrim user input.

Here's how to do it in all types of queries. You don't need to use "Include". The only thing is that it doesn't seem like this works on many-to-many navigation properties.

Just add the navigation properties you want into the final result as "dummy" properties.

(This works with change tracking proxies. I haven't tested it in other situations. Also, don't specify ".AsNoTracking()")

   var results = context.Categories.Where(...)
      .GroupJoin(
         context.Books.Where(...),
         cat => cat.Id,
         book => book.CategoryId, 
         (cat, books) => new 
         {
             Category = cat,
             Books = books.ToList()
             Dummy_Authors = books.Select(b => b.Author).ToList() // dummy property
         });

Now, if you do something like this, the database won't be queried again.

var cat1 = results.First(); // query executed here
var authorName = cat1.Books.First().Author.Name; // already loaded