Doing a Left outer join in a linq query

Question

I have the query below that works in sqllite with a left outer join

        select * from customer cu
        inner join contract cnt on cu.customerId = cnt.customerid
        inner join address addy on cu.addressid = addy.addressId
        inner join csrAssoc cassc on cu.customerid = cassc.customerId
        left outer join CustomerServiceRepresentative csrr  on cassc.csrid = csrr.customerservicerepresentativeId
        inner join customerServiceManager csmm on cassc.csmid = csmm.customerservicemanagerId
        where cu.customernumber = '22222234'

I want to be able to apply a left outer join on this line in the linq query below

        join csrr in objCsrCustServRep.AsEnumerable() on cassc.CsrId equals
                                    csrr.CustomerServiceRepresentativeId

        VisitRepData = (from cu in objCustomer.AsEnumerable()
                                join cnt in objContract.AsEnumerable() on cu.customerId equals cnt.customerId
                                join addy in objAddress.AsEnumerable() on cu.addressId equals addy.addressId
                                join cassc in objCsrAssoc.AsEnumerable() on cu.customerId equals cassc.CustomerId
                                join csrr in objCsrCustServRep.AsEnumerable() on cassc.CsrId equals
                                    csrr.CustomerServiceRepresentativeId
                                join csmm in objCustServMan on cassc.CsmId.ToString() equals csmm.customerServiceManagerId
                                where cu.CustomerNumber == (customernbr)

How can I do a left outer join in a linq query?

Here is my comment after adjusting and running the code. The other section is also added. All am getting is object is not set to an instance of an object.

         var VisitRepData = from cu in objCustomer.AsEnumerable()
                                join cnt in objContract.AsEnumerable() on cu.customerId equals cnt.customerId
                                join addy in objAddress.AsEnumerable() on cu.addressId equals addy.addressId
                                join cassc in objCsrAssoc.AsEnumerable() on cu.customerId equals cassc.CustomerId
                                join csrr in objCsrCustServRep.AsEnumerable() on cassc.CsrId equals
                                    csrr.CustomerServiceRepresentativeId into temp
                                from tempItem in temp.DefaultIfEmpty()
                                join csmm in objCustServMan on cassc.CsmId.ToString() equals csmm.customerServiceManagerId
                                where cu.CustomerNumber == (customernbr)
                                select new
                                {
                                    cu.customerId,
                                    cu.CustomerNumber,
                                    cu.customerName,
                                    cu.dateActive,
                                    cnt.contractExpirationDate,
                                    addy.street,
                                    addy.street2,
                                    addy.city,
                                    addy.state,
                                    addy.zipcode,
                                    cu.EMail,
                                    cu.phoneNo,
                                    cu.faxNumber,
                                    csmm.customerServiceManagerName,
                                    tempItem.CustomerServiceRepresentativeName,
                                };

                            foreach (var item in VisitRepData)
                            {
                                var one = item.customerId;
                                var two = item.CustomerNumber;
                            }

Possible duplicate of [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Ivan Stoev, Jun 09 '16 at 18:03
Aren't 216 upvotes enough for you to read the answer? Not taking into account that the question is pretty basic and lacks elementary research - [join clause (C# Reference)](https://msdn.microsoft.com/en-us/library/bb311040.aspx), [How to: Perform Left Outer Joins (C# Programming Guide)](https://msdn.microsoft.com/en-us/library/bb397895.aspx) etc. — Ivan Stoev, Jun 09 '16 at 18:49
Interesting. The link to the example you sent describes outer join with a collection i.e two different collections. Does my example look like a collection to you? Your comment is very rude. — Baba, Jun 10 '16 at 12:51
The query syntax pattern is one and the same regardless of the **sequence** type (`IEnumerable` or `IQueryable`). Instead of posting comments, why don't you simply try it. I really don't understand what's the point. Anyway, looking at the updated post, did you forget that the `left outer join` includes `null` right side element, so someone has to check for `null` in order to avoid NRE. Also all that `AsEnumerable()` will cause loading all involved tables in memory. — Ivan Stoev, Jun 10 '16 at 13:07

CoolBots · Accepted Answer · 2016-06-09T18:21:24.383

Per documentation:

A left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join

(emphasis mine)

Based on MSDN link above, and if I understood your requirements correctly, query should look like this:

    VisitRepData = from cu in objCustomer.AsEnumerable()
                            join cnt in objContract.AsEnumerable() on cu.customerId equals cnt.customerId
                            join addy in objAddress.AsEnumerable() on cu.addressId equals addy.addressId
                            join cassc in objCsrAssoc.AsEnumerable() on cu.customerId equals cassc.CustomerId
                            join csrr in objCsrCustServRep.AsEnumerable() on cassc.CsrId equals
                                csrr.CustomerServiceRepresentativeId into temp
                            from tempItem in temp.DefaultIfEmpty()
                            join csmm in objCustServMan on cassc.CsmId.ToString() equals csmm.customerServiceManagerId
                            where cu.CustomerNumber == (customernbr)

Specifically, the left outer join is performed with this code:

join csrr in objCsrCustServRep.AsEnumerable()
    on cassc.CsrId equals csrr.CustomerServiceRepresentativeId 
    into temp
from tempItem in temp.DefaultIfEmpty()

It is looking good but needs to be able to pull this field. csrName = csrr.CustomerServiceRepresentativeName, This is giving me an error now — Baba, Jun 09 '16 at 18:34
@user2320476 I don't see this field referenced in your original question - it's hard to adapt my answer to your specific code without seeing the whole picture; however, the left outer join should work, and you should be able to adapt my answer to your situation. — CoolBots, Jun 09 '16 at 22:44
Thanks CoolBots. I added the last part of the code and all am getting is object is not set to an instance of an object. — Baba, Jun 10 '16 at 12:41
I changed this line csrr.CustomerServiceRepresentativeName to tempItem.customerServiceRepresentativeName — Baba, Jun 10 '16 at 12:47
@user2320476 did you resolve the issue, or still getting NullReferenceException? If so, what line of code throws the exception? — CoolBots, Jun 10 '16 at 18:38

Doing a Left outer join in a linq query

1 Answers1