Linq query causes null reference exception when accessing linked tables

Question

So I have the following linq query for a test blog:

var random = new Random();
var random = context.Blog.OrderBy(x => random.Next())
                         .Where(x => x.DisplayStatus == "Approved")
                         .Select(x => new Blog 
                          { 
                              Title = x.Title,
                              Content = x.Content,
                              AuthorId = x.AuthorId,
                              Author = x.Authors.AuthorName //null pointer exception here
                          }).Take(5).ToList()

The trouble is, it throws a null pointer exception when it hits 'x.Authors.AuthorName'. I cannot figure out why. I know the author is there because it works fine when I use linq expressions (from x in context.Blog ...etc), and it works in LinqPad. I can't use the linq expression, though, because I don't know how to declare 'OrderBy(x => random.Next())' without using a lambda expression.

this is the version that works without the random

var working = (from x in context.Blog
                 //NO known code to select random
                 where x.DisplayStatus == "Approved"
                 select new Blog 
                 {
                    Title = x.Title,
                    Content = x.Content,
                    AuthorId = x.AuthorId,
                    Author = x.Authors.AuthorName //NO null pointer exception
                 }).Take(5).ToList()

Blog is a POCO class with no database relations. Authors is a database entity class with AuthorName being a simple string. Any idea on what is going on here?

The typo in `x.DisplayStaus` proves it's not copy/pasted code you claim is working. "Everybody lies" (c) Dr. House. So we ignore this claim. What value has `x` and `x.Authors` when you get NRE? — Sinatr, Apr 15 '20 at 08:46
Can you try changing the name of the second variable to something that is not the same as the first one assigned to new Random()? — JonaFane, Apr 15 '20 at 08:47
I don't want to actually copy paste my code because the full linq query is rather long. I put together a sample that describe the problem more succinctly. I did fix it if that bothers you. — N.MORR, Apr 15 '20 at 08:51
Does this answer your question? [Linq to Entities, random order](https://stackoverflow.com/questions/654906/linq-to-entities-random-order) — Drag and Drop, Apr 15 '20 at 09:45

score 0 · Answer 1 · answered Apr 15 '20 at 09:00

0

I am not allowed to add a comment, so I ask here. What type is Authors? From the name I guess it could be some type of Collection.

What I often do if one Property might be null is to add a ?:

Author = x.Authors?.AuthorName

answered Apr 15 '20 at 09:00

Tim U.

129
6

1

Authors is a database entity type class. AuthorName is a simple string – N.MORR Apr 15 '20 at 09:05

score 0 · Answer 2 · edited Apr 15 '20 at 09:13

You can get random sort using Guid.NewGuid():

var random = context.Blog.OrderBy(x => Guid.NewGuid())
                     .Where(x => x.DisplayStatus == "Approved")
                     .Select(x => new Blog 
                      { 
                          Title = x.Title,
                          Content = x.Content,
                          AuthorId = x.AuthorId,
                          Author = x.Authors.AuthorName
                      }).Take(5).ToList()

Or SqlFunctions.Rand:

var random = context.Blog.OrderBy(x => SqlFunctions.Rand())
                     .Where(x => x.DisplayStatus == "Approved")
                     .Select(x => new Blog 
                      { 
                          Title = x.Title,
                          Content = x.Content,
                          AuthorId = x.AuthorId,
                          Author = x.Authors.AuthorName
                      }).Take(5).ToList()

Both of the yield: "NotSupportedException: This function can only be invoked from LINQ to Entities." Blog is not an entity. I should have mentioned that. — N.MORR, Apr 15 '20 at 09:14

Rand Random · Accepted Answer · 2020-04-15T09:24:44.293

Change your query to this:

var working = (from x in context.Blog
                 where x.DisplayStaus == "Approved"
                 select new Blog 
                 {
                    Title = x.Title,
                    Content = x.Content,
                    AuthorId = x.AuthorId,
                    Author = x.Authors.AuthorName
                 })
                 .AsEnumerable().OrderBy(x => random.Next())
                 .Take(5).ToList();

Edit:

I wasn't 100% sure what the error is and wanted to wait for the OP to check if my answer actually does what I believe it does.

I assumed that Entity Framework will not translate your query to SQL and therefor lose the ability to automatically load x.Authors, by moving the OrderBy the first "half" of the query, can be translated to SQL while the AsEnumerable().OrderBy() will run in memory.

So AsEnumerable() will force the query to be translated and exceuted in SQL, and the following OrderBy will run in memory.

Edit2:

About how to do it on the SQL Server and not loading the whole query to memory, I am afraid I can't help you with that, a google search brought this

Linq to Entities, random order

This seems to be the solution. The trick seems to be to put .AsEnumerable().OrderBy(x => random.Next()) at the end. I tried putting .AsEnumerable().OrderBy(x => random.Next()) before (from x in ...) but it only made things worse. — N.MORR, Apr 15 '20 at 09:04
Your solution loads whole table to memory from DB, sorts it and takes 5 records. — Backs, Apr 15 '20 at 09:07

Linq query causes null reference exception when accessing linked tables

3 Answers3