Aug 1 2007


Category: C# 3.0 | DLinq | LinqBil@l @ 11:06

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],
        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!


You can make your own ringtones, for your cell phones; if those already set on your phones doesn’t suite you. You can download handy soft wares from electronics websites that help you make customized ringtones. These procedures carry very simple instructions still if you find some difficulty, you can use printers to print the instructions.

Tags: , ,

Comments are closed