62

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?

Improve this question
4
  • Is there a defined relationship between Question and QuestionType in either the db or model? Commented Dec 17, 2010 at 21:41
  • Yes. It will load the objects when LazyLoading is enabled. Commented Dec 17, 2010 at 21:42
  • 2
    you should use lambdas instead of 'magic strings' - you need to add 'using System.Data.Entity' - see stackoverflow.com/a/14518244/16940 Commented May 7, 2013 at 7:56
  • 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 Commented Feb 29, 2016 at 0:55

6 Answers 6

Reset to default
63

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

This worked. I had to cast it as ObjectQuery<Question> but it worked after that.
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.
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.
This doesn't work in Entity Framework Core (v2, at least). I cannot cast to (ObjectQuery or ObjectQuery<>)
I love you man. This article what you have provided described my two days problem with combining Select and Include.
|
30

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

Improve this answer

2 Comments

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);
Thank you -- was having a really strange problem & realized we hadn't added the System.Data.Entity namespace to our class!!
25

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

Improve this answer

5 Comments

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.
Wow that was unexpected! 🤯
using System.Data.Entity strikes again.
Strikes again exactly! The length of time between it happening is just long enough to forget this is the cause.
Thanks! I was looking for a solution for several hours and finally found your suggestion.
7

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.

Improve this answer

1 Comment

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

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.

Improve this answer

Comments

-2

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
Improve this answer

3 Comments

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.
Mauricio, I don't think you understand the problem. I'm doing this because there are situations when Include() doesn't work. +5
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.