C# Linq join table, where left table might be null

Question

Please, see my example below. Here, for the question sake, I have 3 tables - mainTable, tableA and tableB. In tables, PK fields mean primary keys, FK fields mean foreign fields.

I have trouble joining 3 tables. In mainTable, there are field "nullableFK" which references to tableA. It might be null. So to join tableA, i use Left Join using DefaultIfEmpty(). Problem starts, when i need to join third table on fields which match in tableA and tableB. example below throws, InvalidOperationException with following message :

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

I tried joining tableB without DefaultIfEmpty - as far as i know, it would be inner join. But then Sequence contains no elements.

Goal is -

to return values of mainTable, tableA and tableB.

If in mainTable, nullableFK is null, return mainTable, and tableA and tableB values as null,

            var query = (from mainTable in db.mainTables
                        where mainTable.PK == id

                        join tableA_ in db.tablesA on mainTable.nullableFK equals tableA_.FK into A
                        from tableA in A.DefaultIfEmpty()

                        join tableB_ in db.tablesB on tableA.PK equals tableB_.PK into B
                        from tableB in B.DefaultIfEmpty()

                        select new {
                            val1 = mainTable.fieldA,
                            val2 = tableA.fieldB,
                            val3 = tableB.fieldC
                        }).First();

Answer - Check for null values in select new {}.

If you have referential integrity already set up, then you should have an entity model that has these foreign keys as collections from your main table to 'a' and 'b'. Stop making things complicated with the 'join' expression in linq, just go and get the value in the 'select new {' part, at that point check and default nulls where necessary. — atom.gregg
In select new {}, there are all the data, from mainTable, tableA and tableB. But the problem is not there. Even if I dont select anything at all from those tables, query doesn't work because the joins doesn't work.. EDIT: I might be wrong. I think i should check for null values in select new {} — Wish

CactusPCJack CactusPCJack · Accepted Answer · 2015-07-10T23:24:12

It looks like the OP specified the answer in an edit to the question, but it wasn't very clear how to implement. He said to check for null in the select new {}.

I was able to make this work by casting the values to nullable types. In the example below I assume that both tableA.fieldB and tableB.fieldC are of type int:

 var query = (from mainTable in db.mainTables
              where mainTable.PK == id

              join tableA_ in db.tablesA on mainTable.nullableFK equals tableA_.FK into A
              from tableA in A.DefaultIfEmpty()

              join tableB_ in db.tablesB on tableA.PK equals tableB_.PK into B
              from tableB in B.DefaultIfEmpty()

              select new {
                  val1 = mainTable.fieldA,
                  val2 = (int?)tableA.fieldB,
                  val3 = (int?)tableB.fieldC
              }).First();

C# Linq join table, where left table might be null

1 Answers