Entity returns null for a row if the first column in this row is NULL

Question

I see strange behavior in my Entity Framework model. I have 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 this column is NULL for multiple rows in the result set. When I expand the rows variable in the debugger, I see that the whole line is null!

EDIT: Some clarifications.

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 builds the Entity Framework returns all the columns of the combined result set in alphabetical order, and the first in alphabetical order is zero and is null for some lines.

This column is not a primary key; if it is a primary key, it cannot be null.

When the Entity Framework processes rows of returned data into objects, it looks at the value of the first column in each row. If this column is null, it returns null for the row, not an object with a property that matches this column set to null.

I do not believe that it has anything special with a left outer join; it just happens that my request uses one. However, I did not do any checks to verify this, so this is just an assumption.

Has anyone seen this before? Can anyone fix this?

Tony

Answer 1

I can confirm that I have exactly the same problem: when the SQL query generated by EF has null in the first column of the result, it returns null instead of the entity. This means the 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 one more criterion that does not lead to null in the first column, t is a normal non- null object.

It seems to me that this is some kind of obscure behavior / error in EF.

Update

After several hours of research, I found the following behavior. EF returns null for entites with null in the first column of the result (which violates the expected behavior), but has several ideas about which column is placed first in the select list. So, after my model exactly matches the state of my database (which means that all the columns of the database are NULLABLE and the navigation properties are NULLABLE to optional ) - I use Code First, so there are a lot of places explicitly about the database - I managed to get it work.

This means that you have to check the definition of the model at the store level (depending on which paradigm you use, design or code based, would be in different places) against your database.

Answer 2

You are using DefaultIfEmpty in your connection. It means

If entries do not have elements matching the join criteria ( alarm.ListDetailId equals temp.ListDetailId ), I want you to put a single default value of type ListDetail (which is null ) in entries .

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

The LEFT JOIN statement selects null values ​​for all columns of a table if no row in the table matches the JOIN criterion. In fact, the only column that matters is the PK column of the object. EF checks to see if PK is null , decides that the entity does not exist, and puts null in entries . You will then get a null value as the value of the entry property for the results.