CODEWITHSUNDEEP
.netlinqentity-framework-4
LJM
LJM

Reputation: 6444

Linq to Entities DefaultIfEmpty

I'm using EF 4 to PostgreSQL using a 3rd party ADO.NET provider. In my data source, I have no item lines that match items based on this join condition. For this reason I expect query 1 to fail based on a null reference exception. However, it completes and gives me value type defaults for each of the properties on il. item_display_name is a string so ilName is set to null. ilStartDate is set to DateTime's default. x.il.item_id and x.il.item_line_no are both 0. 

var query1 =
                    _db.items
                      .GroupJoin(_db.item_line.Where(x => x.start_date == selectedStartDate), x => x.item_id, il => il.item_id, (x, ilgroup) => new { x, ilgroup })
                      .SelectMany(x => x.ilgroup.DefaultIfEmpty(), (x, il) => new { x.x, il })
                      .Select(x =>
                          new
                              {
                                    itemId = x.x.item_id,
                                    ilName = x.il.item_display_name,
                                    ilStartDate = x.il.start_date,
                                    ilItemId = x.il.item_id,
                                    orderLine = x.il.item_line_no});

However, if I force execution by calling ToArray on my results before the Select, I then get my null reference exception.

var query2 =
                _db.items
                    .GroupJoin(_db.item_line.Where(x => x.start_date == selectedStartDate), x => x.item_id, il => il.item_id, (x, ilgroup) => new {x, ilgroup})
                    .SelectMany(x => x.ilgroup.DefaultIfEmpty(), (x, il) => new {x.x, il}).ToArray()
                    .Select(x =>
                            new
                                {
                                    itemId = x.x.item_id,
                                    ilName = x.il.item_display_name,
                                    ilStartDate = x.il.start_date,
                                    ilItemId = x.il.item_id,
                                    orderLine = x.il.item_line_no});

To my understanding, DefaultIfEmpty should return the default for that type. My type is clearly a reference type, so why won't query 1 fail?

Upvotes: 1

Views: 1111

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109080

This is because the first query is completely translated into SQL. SQL is different than C# when it comes to null "objects". In SQL it is perfectly OK to write something like

SELECT o.Date, ol.Number FROM Order o LEFT JOIN OrderLine ol ON ... (etc.)

It won't crash when there are Orders without OrderLines. No null reference exception on ol here. SQL just outputs null values for ol.Number where order lines are missing.

So in the first statement the anonymous type is built directly from values obtained from SQL. The whole expression x.il.item_display_name is populated by output from a DbDataReader, which is null when no ilgroup is present.

In the second statement, first an array of objects is built in memory, consisting of x and il pairs, some which have no il (il is null). Now the anonymous type is built from the object array and the expression x.il.item_display_name tries to read item_display_name from some non-existing objects.

Upvotes: 2

digaomatias
digaomatias

Reputation: 1194

This is a tricky characteristic of the Entity Framework. When you are doing the whole query, you don't actually hit the database. EF will only go to the database when you do a ToArray, ToList, etc...

In the first query, you should get the NullReference if you put the ToArray() at the end. While you don't actually do a ToArray, you'll be only constructing the query to run when you do the ToList(), or ToArray().

Take a look at this post: Am I hitting the database?

Upvotes: 0

Related Questions