Entity framework returns null for a row if the first column in that row is null

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

I've checked the SQL and it's correct. I'm getting a LEFT OUTER JOIN on the ListDetails table, as I wanted, and I have all the rows I expect. The only thing is those rows that have the first column set to NULL are NULL in the rows variable, but they have other columns that are not null. These are rows in the middle of the result set. — Tony Vitabile
– Tony Vitabile, Commented Apr 19, 2012 at 0:43
Can't help you since you don't give the SQL query. It's unclear what the 'first column' is, what it's meaning is. Is it a PK? Is it a EF-synthesized column? Based on what you have told, I can only say that it should work. — Pavel Gatilov
– Pavel Gatilov, Commented Apr 19, 2012 at 3:40

Sergei Rogovtcev · Accepted Answer · 2012-06-15 07:07:01Z

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.

Pavel Gatilov · Accepted Answer · 2012-04-18 16:19:18Z

1

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.

answered Apr 18, 2012 at 16:19

Pavel Gatilov

7,6711 gold badge30 silver badges42 bronze badges

1 Comment

Tony Vitabile Over a year ago

I need the Left Outer join. The problem isn't that the entry is NULL; the problem is that the first column in the alarm is NULLand the entire select new { alarm, entry, read, plate } is returned as NULL, not just the entry.

Collectives™ on Stack Overflow

Entity framework returns null for a row if the first column in that row is null

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related