How Do I display a value that exists in one table but doesn't have any row tied to it on another table where 3 tables are involved?

Question

I have 3 tables. master, consumer& charge. invoiceid is pk for master which exists in consumer&charge table. consumer pk is consumerid & invoiceid. charge pk is charge id and fk is consumer id. I am trying to display columns from all the tables but some consumer id does not show up when displayed as there is no row referring to it in the charge table. I have tried to do left join but still does not display. Any suggestions?

Edit:

So I have figured out on how to do one left join but how do I do it if for 3 tables? I am able to display consumerid where there is no entry in charge but can't get invoiceid where there is no entry in consumers. A master may or may not have a consumer and a consumer may or may not have a charge. .

var query = from m in IM.GetMaster()

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


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

See [here](https://msdn.microsoft.com/en-us/library/bb311040.aspx#Anchor_2) how to perform left outer joins correctly in LINQ. — Ivan Stoev, Jul 14 '16 at 08:09

score 0 · Answer 1 · edited May 23 '17 at 12:22

Customer table should be on top. Check this post about left join and this one.

var query = from ConsumerEntity in consumer
            join MasterEntityConsumerEntity in master
                    on ConsumerEntity.InvoiceId equals MasterEntity.InvoiceId
                    into tmp in MasterEntityConsumerEntity from tmp.DefaultIfEmpty()
            join ConsumerChargeEntity in charge
                    on new { ConsumerEntity.InvoiceId, ConsumerEntity.ConsumerId } 
                    equals new { ConsumerChargeEntity.InvoiceId, ConsumerChargeEntity.ConsumerId }
                    into ctmp in ConsumerChargeEntity from ctmp.DefaultIfEmpty()
            select new
            {
                InvoiceID = MasterEntity.InvoiceId,
                ConsumerID = ConsumerEntity.ConsumerId,
                ChargeID = ConsumerChargeEntity.ChargeId,
                Amount = ConsumerChargeEntity.Amount,

            };

UPDATED

You are almost close.

Your query : 1 INNER JOIN 2 AND 2 LEFT JOIN 3
Below query : 1 LEFT JOIN 2 AND 2 LEFT JOIN 3

var query = from m in IM.GetMaster()
            join co in CM.GetConsumers()
                    on m.InvoiceId equals co.InvoiceId  into tempCo
                    from co in tempCo.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()
            select new 
            {
                    InvioceID = m.InvoiceId,
                    ConsumerID = co == null? 0 : co.ConsumerId,
                    ChargeID = ch == null ? 0 : ch.ChargeId,
                    Amount = ch == null ? 0 : ch.Amount
            };

I have tried that but that just gives a "Object reference not set to an instance of an object" error, a null reference exceptiob at the line where "new { co.InvoiceId, co.ConsumerId } " — Scar, Jul 18 '16 at 08:10
Can you try this `new { (co == null ? -1 : co.InvoiceId), (co == null ? -1 : co.ConsumerId )}` — neer, Jul 18 '16 at 08:28

How Do I display a value that exists in one table but doesn't have any row tied to it on another table where 3 tables are involved?

1 Answers1