Entity Framework Include() is not working

Question

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?

Thermodynamics

Is there a defined relationship between Question and QuestionType in either the db or model? – Dowsabel

Yes. It will load the objects when LazyLoading is enabled. – Thermodynamics

you should use lambdas instead of 'magic strings' - you need to add 'using System.Data.Entity' - see https://mcmap.net/q/330303/-entityframework-4-upgraded-to-5-lambda-is-not-available – Yellowstone

check, double check, triple check that your configuration is setup properly! Got burnt by this and my relationship was pointing at the wrong foreign key – Nb

Answer 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.

Lorettelorgnette

This worked. I had to cast it as ObjectQuery<Question> but it worked after that. – Thermodynamics

Nice, Just as an addition: var questions = (from sq in db.SurveyQuestions where sq.Survey == surveyTypeID orderby sq.Order select q.Question).Include(“QuestionType”); might be a tiny bit cleaner. – Quadrivium

hmmm I am using this approach but it still does not work on first hit. if i execute the same query in parallel subsequent calls work, but not the first one. still trying to figure it out. – Saltworks

This doesn't work in Entity Framework Core (v2, at least). I cannot cast to (ObjectQuery or ObjectQuery<>) – Melissamelisse

I love you man. This article what you have provided described my two days problem with combining Select and Include. – Abarca

I believe the links here are invalid. I think the new repo is on Github, but I have no idea how Include translates: github.com/thomasgalliker/EntityFramework.Toolkit – Petaliferous

Answer 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>?

Abduction

To make that better, you can use the Include extension method in that namespace to get rid of the string literal all-together: questions.Include(q => q.QuestionType); – Feriga

Thank you -- was having a really strange problem & realized we hadn't added the System.Data.Entity namespace to our class!! – Beverage

Answer 3

The error was the use of using System.Data.Entity, when it should have been using Microsoft.EntityFrameworkCore.

Annmarieannnora

Yes!! This. Holy crap what an annoying silent issue without a single error message or even warning. How is this design even possible. In my case I think Visual Studio had simply auto-added a using and guessed. Alright, back on track again, three hours later. – Arruda

Wow that was unexpected! 🤯 – Footstone

using System.Data.Entity strikes again. – Cliffordclift

Strikes again exactly! The length of time between it happening is just long enough to forget this is the cause. – Table

Answer 4

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.

Adsorb

I'm facing this exact issue right now. I cannot however just rearrange the order of code :/ Do you know if there are any other ways to get include to work? – Oasis

Answer 5

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.

Jenijenica

Answer 6

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

Classroom

i'd advice against using this approach. aside from the clunky structure of the code, prone to readability issues; the queries generated by EF are too, awkward and heavy. whereas using Include(), provides clean joins as well as a natural flow in semantics and intent. – Jacobus

Mauricio, I don't think you understand the problem. I'm doing this because there are situations when Include() doesn't work. +5 – Classroom

Solving a problem by creating another problem is NOT a good solution. This is how codebases end up becoming impossible to maintain for new developers. – Jacobus