Left Join in Entity Framework on null values

Question

I need to perform 10 left joins in a C# project using Entity Framework. I have checked at least 10 different pages and stackoverflow posts on how to perform this operation. None of them worked.

My current join looks like this:

from tbl1 in context.tblName1
join varOne in context.tblName2 on tbl1.paramOne equals varOne.paramOne into j1
from jResOne in j1.DefaultIfEmpty()

I get the error message:

"The cast to value type 'System.Int32' failed because the materialized value is null- Either the result type's generic parameter or the query must use a nullable type."

Which I was lead to believe should be solved by the ".DefaultIfEmpty()"

How do I solve this issue?

The exception cannot be from the join, but from some projection / aggregate. Check the other parts of the query. And consider using navigation properties as suggested in the current answer. — Ivan Stoev
– Ivan Stoev, Commented May 25, 2017 at 20:16
@IvanStoev you were correct. It was the select clause where i needed a "VarName = tblRowVar == null ? 0 : tblRowVar.rowParam," This resolved my Issue, add an answer to my question so i can select it as answer, if you want to. — DannyBoy
– DannyBoy, Commented May 26, 2017 at 8:30

Ivan Stoev · Accepted Answer · 2017-05-26 10:49:01Z

@David Browne is right about navigation properties vs joins in EF. But here the problem is different (unrelated to join operations, but to processing the result) and is contained in the exception message:

The cast to value type 'System.Int32' failed because the materialized value is null - Either the result type's generic parameter or the query must use a nullable type.

What it means is that the projection (select) is trying to assign a non nullable type value from some of the right sides of the left outer joins. In LINQ to Objects it would be a simple NullReferenceException. However, LINQ to Entities translates the query to SQL, and databases (and SQL in particular) have natural support for NULL values even for non nullable columns. So EF is able to execute the SQL successfully, but when it comes to the point it needs to materialize the result (i.e. to put it into anonymous/specific class members), and the db query returns null while the corresponding property is non nullable type, EF cannot proceed and is throwing exception asking you to resolve it by either casting it to a nullable type, or converting it to some default value etc. - it's up to you, EF can't make that decision.

Let say TableB has int Property and you have a query like this:

var query = from a in db.TableA
            from b in db.TableB.Where(b => b.Key == a.Key).DefaultIfEmpty()
            select new { Property = b.Property };

The C# implied type of the result Property is int, hence you'll get the aforementioned exception when the query result contains b that does not match a (b will be null from C# point of view, but from the SQL side the b.Property will be NULL).

In order to resolve it, you could promote it to nullable type:

Property = (int?)b.Property

or to some default value

Property =  b != null ? b.Property : 0

depending on your needs. And do similar for any non nullable value type like int, decimal, DateTime etc. (string is a reference type, so it has no such issue (can hold null value)).

David Browne - Microsoft · Accepted Answer · 2017-05-25 20:06:57Z

4

need to perform 10 left joins in a C# project using Entity Framework

That is highly unlikely. There's almost always a better, easier way to express the query in LINQ to Entities using Navigation Properties instead of Joins.

Instead just navigate across your Navigation Properties to project related values.

answered May 25, 2017 at 20:06

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

Collectives™ on Stack Overflow

Left Join in Entity Framework on null values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related