Entity Framework Include() is not working

Solution 1:

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.

Solution 2:

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 : How can i convert a DBQuery<T> to an ObjectQuery<T>?

Solution 3:

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.

Solution 4:

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.