Entity framework returns null for a row if the fir

Question

I'm seeing a weird behavior in my Entity Framework model. I've got a query that looks like this:

var rows = ( from alarm in context.Alarms
             join temp  in context.ListDetails on alarm.ListDetailId equals  temp.ListDetailId into entries from entry in entries.DefaultIfEmpty()
             join read  in context.Reads       on alarm.ReadId       equals  read.ReadId
             join plate in context.Images      on alarm.ReadId       equals plate.ReadId
             where alarm.IActive == 1 && ! alarm.TransmittedAlarm 
             where  read.IActive == 1 
             where plate.IActive == 1 && plate.ImageTypeId == 2
             select new { alarm, entry, read, plate } ).ToArray();

The query returns all columns in alphabetical order by column name. It turns out that this column is NULL for a few rows in the result set. When I expand the rows variable in the debugger, I see that the entire row is null!

EDIT: Some clarification.

By "first column", I mean the first column of the first row, i.e., in "SELECT A, B, C FROM ...", I mean A. It just happens that the query that Entity Framework builds returns all of the columns in the joined result set in alphabetical order, and the first one alphabetically is nullable and is null for some rows.

The column in question is not a primary key; if it were a primary key, it couldn't be null.

When Entity Framework processes the rows of the returned data into objects, it's looking at the value of the first column in each row. If that column is null, it's returning null for the row, instead of an object with the property that corresponds to that column set to null.

I don't believe this has anything to do specifically with a left outer join; it just happens that my query uses one. I haven't done any testing to verify this, however, so it's just a supposition.

Has anybody seen this before? Does anyone have a fix for this?

Tony

Answer 1

I can confirm that I have exactly same trouble: when SQL query formed by EF has null in first column of result, it return null instead of entity. It means exactly following:

using (var dc = new MyDbContext())
{
    var q = dc.Lands; //Lands is DbSet<Land>
    q = q.Where(criteria); //this leads to SQL query returning null in first column, confirmed by profiler
    var t = q.Single(); //t is now null
}

If we take another criteria, which does not result in null in first column, t is normal non-null entity.

It seems to me that it is some kind of obscure behavior/bug in EF.

Update

After several hours of probing I've found following behavior. EF does return null for entites having NULL in first column of result (which breaks expected behavior), but it has several notions about what column to put first in the select list. So, after making my model precisely consistent with my database state (which means denoting all NULLABLE database columns and replacing required navigation properties with optional) - I am using Code First, so there're plenty of places to be explicit about database - I've managed to get it to work.

It means that you should check store-level definition of your model (depending on what paradigm you use, designer-based, or code-based, it would be different places) against your database.

Answer 2

You use DefaultIfEmpty in your join. It means

If there're no items in entries that match the join criterion (alarm.ListDetailId equals temp.ListDetailId), I want you to put a single default value of type ListDetail (which is null) into entries.

In this case EF generates a LEFT JOIN. In fact, this code is the well-known way to generate a LEFT JOIN with Linq2Sql or EF.

The LEFT JOIN statement selects NULL values for all columns of the table if no row in the table matches the JOIN criterion. In fact, the only column that matters is the PK column of the entity. EF checks if the PK value is NULL, decides that no entity exists and puts null into entries. Then you get this null as the entry property value of the results.