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

Question

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..

What do you want? To return data even if there's no rows in the `addressHistory` table? In that case you need the equivalent of a "left join". — ChrisF, Oct 20 '16 at 12:08
`from address in DB.Connector.Table() from addressHistory in DB.Connector.Table().Where(ah => ah.CustomerRef == customerRef).DefaultIfEmpty() select new ...` — Michael, Oct 20 '16 at 12:12
@ChrisF Hi chris thanks for your question. Yes, i am looking for a Left join but how do i perform it with the where clause being there? — Andrew Simpson, Oct 20 '16 at 12:12
@Dennis_E thanks will look and if same will delete my question — Andrew Simpson, Oct 20 '16 at 12:14
@Dennis_E slightly different as my question was with the inclusion of the where clause but thanks for flagging :) — Andrew Simpson, Oct 20 '16 at 12:15
@GiladGreen thanks will look at . just trying to see if the solution proposed works at the moment. I know flagged as duplicate but the other answers do not include the where clause — Andrew Simpson, Oct 20 '16 at 12:24

score 1 · Accepted Answer · edited Sep 29 '17 at 17:19

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
   };

thanks i was trying to work out where to put the DefaultIfEmpty. New to this stuff. thanks again :) — Andrew Simpson, Oct 20 '16 at 12:13
@AndrewSimpson - Your are welcome :) Saw you edited title - good :) Added an extra edit :) — Gilad Green, Oct 20 '16 at 12:27

score 1 · Answer 2 · answered Oct 20 '16 at 12:13

If you want to return data even when there's no rows in the addressHistory table for this address then you need to do the equivalent of a SQL "left join":

join addressHistory in DB.Connector.Table<InformedWorkerModel.Tables.AddressHistory>()
     on address.AddressRef equals addressHistory.AddressRef
     into joinedAddressHistories
     from joinedHistory in joinedAddressHistories.DefaultIfEmpty()

However, you'll also need to modify your where clause as that will now fail if you try:

where joinedHistory.CustomerRef = customerRef

as joinedHistory will be null. Without knowing exactly what you want here I can't suggest a solution.

thanks again. Let me look at these 2 answers and the comment from Michael — Andrew Simpson, Oct 20 '16 at 12:14

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

2 Answers2