DevLog

I was practicing a little bit on using Linq to Sql, and had this scenario:

Customers and Orders tables in Northwind Database. I added a new customer with no records, I wanted to apply a left outer join to get customers whose CustomerID starts with letter B such that, even if the Customer had no record in the Orders table, I wanted his/record to be present in the result.

The easiest way to go is to use LEFT OUTER JOIN between Customers' table and Orders' table so that I get all Customers that pass the filter expression and even have no records in the Order table, here is the Query Expression:

        NorthwindDataContext db= new NorthwindDataContext();
        var query=  from c in db.Customers
                    where c.CustomerID.StartsWith("B")
                    join o in db.Orders on c.CustomerID equals o.CustomerID into sr
                    from x in sr.DefaultIfEmpty()
                    select new {CustomerID= c.CustomerID, ContactName=c.ContactName, OrderID = x.OrderID == null ? -1 : x.OrderID};   

As you can see I used the join into operator in Query Expression which results in the LEFT OUTER JOIN, I added my filter expression in the where clause and finally, I returned an anonymous type containing CustomerID, ContactName, and OrderID which is -1 in case there is a null Order record for the Customer otherwise returns the OrderID.

The T-SQL representation of the above is as follows:

SELECT [t0].[CustomerID], [t0].[ContactName],
    (CASE
        WHEN [t1].[OrderID] IS NULL THEN @p1
        ELSE [t1].[OrderID]
     END) AS [value]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
WHERE [t0].[CustomerID] LIKE @p0

Notice how the LEFT OUTER JOIN appears, notice how the CASE appears which reflect the if/else that I have added in the above Query Expression.

Hope this helps you!
Regards

Tags: C# 3.0, DLinq, Linq