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 where 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 task on them. I dusted of my old copy of SQL Queries For Mere Mortals and was able to do it in SQL. But I wanted 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 manged to join the two like this:
[sql]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
};[/sql]
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.
Thankfull for any information!
That’s the good thing living in the far north, we have day when the US have night! So when I got back from work I found two answers. None 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:
[sql]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)
};[/sql]
So lets break it down!
[sql]from List in dataContext.Todo_Lists[/sql]
I want to get the Lists from the Todo_Lists table.
[sql]join Item in dataContext.Todo_List_Items on List.ListID equals Item.ListID into compList[/sql]
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 complete list.
[sql]from resList in compList.DefaultIfEmpty()[/sql]
Then I select into resList for 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.
[sql]where List.ListTrashed == false && (resList.ItemTrashed == false || resList.ItemTrashed == null)[/sql]
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). An other 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.
[sql]orderby List.ListSortOrder ascending, resList.ItemSortOrder ascending[/sql]
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 straight forward 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.
[sql]select new
{
List.ListID,
List.ListName,
List.ListSortOrder,
ItemID = (resList.ItemID == null ? int.MinValue : resList.ItemID),
ItemSortOrder = (resList.ItemSortOrder == null ? int.MinValue : resList.ItemSortOrder)
};[/sql]
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 the Items its 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 got this far. Thanks guys!
Hackviking aka Kristofer Källsbo 
Hi
Nice tutorial, I have same problem and just reached at solution following your steps. But facing problem,
var finalResult = (from filesFrmSystem in lstFileInfo.AsEnumerable().ToList()
join fileExtension in fileExtensionsLst.AsEnumerable().ToList() on filesFrmSystem.Extension.ToString().Trim().ToLower().Replace(“.”, “”) equals fileExtension.FileExtension.ToString().Trim().ToLower()
into compList
from resList in compList.DefaultIfEmpty()
select new
{
filesFrmSystem.Name,
filesFrmSystem.FullName,
ItemID = (resList.FileExtension == null ? “Message1” : “Message2”),
});
Error: Object reference not set to an instance of an object
Where On last column ItemId.
Can you help?
LikeLike
try this:
ItemID = (resList!=null?(resList.FileExtension == null ? “Message1″ : “Message2″):”Message3″),
LikeLike