NullReferenceException when Selecting from Left Join

Question

I am trying to do 2 left joins. I have tested the query in SQL server and it works but I am unable to recreate the query in linq.

The query:

select Master.InvoiceId,Consumer.ConsumerId,ConsumerCharge.ChargeId , Amount 
from Master 

left outer join  Consumer on 
Master.InvoiceId=Consumer.InvoiceId 

left outer join ConsumerCharge on 
Consumer.ConsumerId = ConsumerCharge.ConsumerId and 
Consumer.InvoiceId = ConsumerCharge.InvoiceId and 
Master.InvoiceId = ConsumerCharge.InvoiceId

order by InvoiceId

In LINQ:

var query = from m in IM.GetMaster()

            join co in CM.GetConsumers()
            on m.InvoiceId equals co.InvoiceId into temp2
            from co in temp2.DefaultIfEmpty()

            join ch in CCM.GetCharge()
            on new { co.InvoiceId, co.ConsumerId,  } equals new { ch.InvoiceId, ch.ConsumerId }   into temp
            from ch in temp.DefaultIfEmpty()

            orderby m.InvoiceId
            select new
            {
                InvioceID = m.InvoiceId,
                ConsumerID = co == null ? 0 : co.ConsumerId,
                ChargeID = ch == null ? 0 : ch.ChargeId,
                Amount = ch == null ? 0 : ch.Amount
            };

I am getting

Object reference not set to an instance of an object.

at line on new { co.InvoiceId, co.ConsumerId, }. If I remove into temp2 from co in temp2.DefaultIfEmpty(), it displays but invoice ids which does not have any consumer id is not displayed. How do I do a proper left join where 3 tables are involved?

Answer 1

Having the left join means that if there is no matching record in the second table then all those values are null (different from a normal join that it will not return the record from the left table).You might have the co equals null for that record so you have to check it

Try this:

var query = from m in IM.GetMaster()

        join co in CM.GetConsumers()
        on m.InvoiceId equals co.InvoiceId into temp2
        from co in temp2.DefaultIfEmpty()

        join ch in CCM.GetCharge()
        on new { co?.InvoiceId, co?.ConsumerId,  } equals new { ch?.InvoiceId, ch?.ConsumerId }   into temp
        from ch in temp.DefaultIfEmpty()

        orderby m.InvoiceId
        select new
        {
            InvioceID = m.InvoiceId,
            ConsumerID = co?.ConsumerId,
            ChargeID = ch?.ChargeId,
            Amount = ch?.Amount
        };

Also see use of ?. in your select new