OpenLightGroup Blog

rss

Blogs from OpenLightGroup.net


Linq to SQL Left Join

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.

image

ADefHelpDesk_Task contains the ticket, and ADefHelpDesk_TaskDetail contains 0 or more detail records. The previous search code looked like this:

image

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:

image

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.





Comments are closed.