Linq to SQL Left Join
Jun
16
Written by:
6/16/2009 8:02 PM
I have avoided left outer joins in Linq to SQL because I have been always able to use a linq projection to get the needed values in a sub query. However, I had a bug in ADefHelpDesk that would not allow a search on the description of a ticket if the Ticket didn't have a related details record.
ADefHelpDesk_Task contains the ticket, and ADefHelpDesk_TaskDetail contains 0 or more detail records. The previous search code looked like this:
But this creates a "inner join" so you will only get a ADefHelpDesk_Task record if it also has a ADefHelpDesk_TaskDetail record.
The fixed code looks like this:
This part of the code:
join details in objADefHelpDeskDALDataContext.ADefHelpDesk_TaskDetails
on Search.TaskID equals details.TaskID into joined
from leftjoin in joined.DefaultIfEmpty()
creates a "leftjoin" variable that that can be queried like this:
leftjoin.Description.Contains(strSearchText)
This allows me to get to the ADefHelpDesk_TaskDetail record without causing the matching ADefHelpDesk_Task record to be suppressed if it doesn't have a matching ADefHelpDesk_TaskDetail record.
My CodeProject Blogs