LINQ to SQL: Left Join With Null Values

I was staying up late last night coding on my project and hit a snag. Trying to join two tables that have a 1 to many relationship. The issue was that I was trying to get all the rows from one table, and where there were, get the corresponding entries from the other table. In short, I have one table containing to-do-lists and one table containing the tasks for each list. But I have been a good boy and done all the tasks on some lists so they are empty. When I tried to join them, I only got back the lists that had tasks on them. I dusted off my old copy of SQL Queries For Mere Mortals and was able to do it in SQL. But then I wanted to do it with SQL to LINQ. After a few hours, I couldn't get any further. Tried to Google it and came up short. So I posted this on stackoverflow.com:

I'm trying to get data from two tables, Todo_Lists and Todo_Items. I managed to join the two like this:

from list in dataContext.Todo_Lists
from item in dataContext.Todo_List_Items
where list.UserID == userID && list.ListID == item.ListID
select new
{
    ListID = list.ListID,
    ListName = list.ListName,
    ItemID = item.ItemID,
    ItemName = item.ItemName
};

That's all good in the hood if I just want the lists with Items on them. But I need to return all the lists, and where there are Items, I need them to be joined in.

Thankful for any information.

That's the good thing living in the far north, we have day when the US has night! So when I got back from work, I found two answers. Neither of them was a complete solution but combined, they pushed me in the right direction. I realized that I had to give the LINQ engine an alternative to null. This is what I came up with:

from List in dataContext.Todo_Lists
join Item in dataContext.Todo_List_Items on List.ListID equals Item.ListID into compList
from resList in compList.DefaultIfEmpty()
where List.ListTrashed == false && 
  (resList.ItemTrashed == false || resList.ItemTrashed == null)
orderby List.ListSortOrder ascending, resList.ItemSortOrder ascending
select new
{
    List.ListID,
    List.ListName,
    List.ListSortOrder,
    ItemID = (resList.ItemID == null ? int.MinValue : resList.ItemID),
    ItemSortOrder = (resList.ItemSortOrder == null ? int.MinValue : resList.ItemSortOrder)
};

So let's break it down!

from List in dataContext.Todo_Lists

I want to get the Lists from the Todo_Lists table.

join Item in dataContext.Todo_List_Items on List.ListID equals Item.ListID into compList

I want to join the Items/Task from Todo_List_Items table where the ListID column relationship matches. The two mashed tables goes into compList for the complete list.

from resList in compList.DefaultIfEmpty()

Then I select into resList for the result list from comList with .DefaultEmpty(). DefaultEmpty() returns the default value if there isn't an entry. That makes it possible to check for empty records later in the code.

where List.ListTrashed == false && 
   (resList.ItemTrashed == false || resList.ItemTrashed == null)

Now comes the where. I only want the list that I haven't sent to the trashcan. This is also true for the items, but they can also return a null value. So if I only say resList.ItemTrashed == false, I'm right back where I started, with only getting the lists with items on them. But by adding || (or) resList.ItemTrashed == null, I give the engine an option to match null as a suitable value for selection. That can only return an item that has null or false, and I will not receive any that are trashed (resList.ItemTrashed == true). Another thing to note here is all the where options for table Todo_Lists is on the List object and all others are on the resList object. Don't worry, the intellisense will take care of you there.

orderby List.ListSortOrder ascending, resList.ItemSortOrder ascending

Next, I want to sort my result. Of course, I want to prioritize my lists, so I use sort order values to do so. This is really straightforward, you don't have to take into account the possible null values, just tell how you want it sorted and it will all be good.

select new
{
    List.ListID,
    List.ListName,
    List.ListSortOrder,
    ItemID = (resList.ItemID == null ? int.MinValue : resList.ItemID),
    ItemSortOrder = (resList.ItemSortOrder == null ? 
                     int.MinValue : resList.ItemSortOrder)
};

Now, it's just the selection of the fields left. From List, I want all the records returned that meets the Where clause so I just select the fields I want. From Items, it's a different story. ItemID can be null, but that value can't be returned so you have to substitute it with something else. It also has to be of the same cast as the original field. So if the ItemSortOrder field is null, replace it with int.MinValue. That value is not part of the sort order and easy to match when I loop through the records to detect that the list doesn't have any items. If the field isn't null, I want the value, so I put in: resList.ItemSortOrder.

I have to credit msarchet and saus for getting me in the right direction. Without them, I wouldn't have got this far. Thanks guys!