CODEWITHSUNDEEP
c#entity-frameworklinqleft-join
Pankaj Basnal
Pankaj Basnal

Reputation: 55

Linq join query with left outer join throwing object reference not found

I have a list and two tables. (This is a very simplified version of the actual schema, but should work for the 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 a in List_A
 join b in Table_B on a.FPI equals b.FPI into ab
 from b in ab.DefaultIfEmpty()
 join c in Table_C on b.FI equals c.FI into bc
 from c in bc.DefaultIfEmpty()
 select new {
   FPI = a.FPI,
   Name = c?.Name}).ToList();

this code throws an exception that Object reference not set to an instance of an object.. After a lot of trial and experiment, I have reached to a conclusion that in the second join when i'm doing b.FI equals c.FI, at that time it is failing for the entries for which there is no value in the Table_B.

The expected output of the query should be
ABC FPI____NI___Name
1_____null__null
2_____1_____x
3_____null__null
4_____2_____y


I'm not sure why this error is coming and what would be the best solution for this problem.

Upvotes: 1

Views: 1680

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205589

Your query would be perfectly valid if it was a LINQ to Entities query translated to SQL.

However, since the root of the query is List_A which is not a IQueryable, the whole query executes in LINQ to Objects context, where you are supposed to perform null checks on right side variable of the left outer join anywhere, including further join conditions.

So the simple fix would be using

join c in Table_C on b?.FI equals c.FI into bc

However, note that the query is highly inefficient. Since it is resolved to Enumerable methods, the whole Table_B and Table_C will be read in memory and then joined.

A better approach would be to separate the db and in memory queries:

var dbQuery =
    from b in Table_B
    join c in Table_C on b.FI equals c.FI into bc
    from c in bc.DefaultIfEmpty()
    select new { b.FPI, c.Name };

var query =
    from a in List_A
    join bc in dbQuery on a.FPI equals bc.FPI into abc
    from bc in abc.DefaultIfEmpty()
    select new
    {
       FPI = a.FPI,
       Name = bc?.Name
    };

var result = query.ToList();

Upvotes: 1

Eldaniz Ismayilov
Eldaniz Ismayilov

Reputation: 856

You can try

 var list=(from a in Table_A
                join b in Table_B on a.FPI equals b.FPI into ab
                from b in ab.ToList()
                join c in Table_C on b.NI equals c.NI into bc
                from c in bc.DefaultIfEmpty()
                select new {
                FPI = a.FPI,
                Name = c.Name}).ToList();

Update

         var list = (from a in Table_A
                     join b in Table_B on a.FPI equals b.FPI into ab
                     from b in ab.DefaultIfEmpty()
                     join c in Table_C on b == null ? 0 : b.NI equals c.NI into bc
                     from c in bc.DefaultIfEmpty()
                     select new
                     {
                         FBI = a.FPI,
                         NI = c != null ? c.NI : null,//if NI is nullable
                         //NI = c != null ? c.NI : 0,//if NI is not nullable
                         Name = c!=null?c.Name:null

                     }).ToList();

Upvotes: 0

Related Questions