Andrew Simpson Andrew Simpson - 1 month ago 6
C# Question

Left Join on Linq query when also using the Where clause on joint table

I can see this question has been asked a few times but I am puzzled as to how to implement for myself.

I am using SQL Lite and i am trying to join 2 tables but sometimes the 'linked' row in the 2nd table does not exist.

This is my code:

return from address in DB.Connector.Table<InformedWorkerModel.Tables.Address>()
join addressHistory in DB.Connector.Table<InformedWorkerModel.Tables.AddressHistory>()
on address.AddressRef equals addressHistory.AddressRef
where addressHistory.CustomerRef == customerRef
select new InformedWorkerModel.Tables.Address
{
Address1 = address.Address1,
Address2 = address.Address2,
Address3 = address.Address3,
Town = address.Town,
County = address.County,
Country = address.Country,
PostCode = address.PostCode,
AddressRef = address.AddressRef,
AddressId = address.AddressId
};


On Googling i can see a solution if I do not use a 'where' clause.. but i am..

Answer

You are missing the use of DefaultIfEmpty() part which is what turns it into a left join(Check out the documentation - it shows very clear how to perform all the different joins):

   from address in DB.Connector.Table<InformedWorkerModel.Tables.Address>()
   join addressHistory in (from x in DB.Connector.Table<InformedWorkerModel.Tables.AddressHistory>() 
                           where x.CustomerRef == customerRef
                           select x) 
   on address.AddressRef equals addressHistory.AddressRef into j
   from addressHistory in j.DefaultIfEmpty()
   select new InformedWorkerModel.Tables.Address
   {
       Address1 = address.Address1,
       Address2 = address.Address2,
       Address3 = address.Address3,
       Town = address.Town,
       County = address.County,
       Country = address.Country,
       PostCode = address.PostCode,
       AddressRef = address.AddressRef,
       AddressId = address.AddressId
   };

Also see that I moved the addressHistory.CustomerRef == customerRef to a nested select. If you do not do so then you will only get rows which have a value for it - which effectively turns this into a "normal" inner join. Another way is like before but:

where addressHistory == null || addressHistory.CustomerRef == customerRef

So it will look like:

   from address in DB.Connector.Table<InformedWorkerModel.Tables.Address>()
   join addressHistory in DB.Connector.Table<InformedWorkerModel.Tables.AddressHistory>() 
   on address.AddressRef equals addressHistory.AddressRef into j
   from addressHistory in j.DefaultIfEmpty()
   where addressHistory == null || addressHistory.CustomerRef == customerRef
   select new InformedWorkerModel.Tables.Address
   {
       Address1 = address.Address1,
       Address2 = address.Address2,
       Address3 = address.Address3,
       Town = address.Town,
       County = address.County,
       Country = address.Country,
       PostCode = address.PostCode,
       AddressRef = address.AddressRef,
       AddressId = address.AddressId
   };
Comments