c# - Linq join query with left outer join throwing object reference not found -

i have list , 2 tables. (this simplified version of actual schema, should work question)

list_a
fpi
1
2
3
4

table_b
fpi_______ni
2_________1
4_________2

table_c
ni_______name
1_________x
2_________y

my linq query:

(from in list_a  join b in table_b on a.fpi equals b.fpi ab  b in ab.defaultifempty()  join c in table_c on b.fi equals c.fi bc  c in bc.defaultifempty()  select new {    fpi = a.fpi,    name = c?.name}).tolist(); 

this code throws exception object reference not set instance of object.. after lot of trial , experiment, have reached conclusion in second join when i'm doing b.fi equals c.fi, @ time failing entries there no value in table_b.

the expected output of query should be
abc fpi____ni___name
1_____null__null
2_____1_____x
3_____null__null
4_____2_____y

i'm not sure why error coming , best solution problem.

your query valid if linq entities query translated sql.

however, since root of query list_a not iqueryable, whole query executes in linq objects context, supposed perform null checks on right side variable of left outer join anywhere, including further join conditions.

so simple fix using

join c in table_c on b?.fi equals c.fi bc 

however, note query highly inefficient. since resolved enumerable methods, whole table_b , table_c read in memory , joined.

a better approach separate db , in memory queries:

var dbquery =     b in table_b     join c in table_c on b.fi equals c.fi bc     c in bc.defaultifempty()     select new { b.fpi, c.name };  var query =     in list_a     join bc in dbquery on a.fpi equals bc.fpi abc     bc in abc.defaultifempty()     select new     {        fpi = a.fpi,        name = bc?.name     };  var result = query.tolist();