LEFT OUTER JOIN in LINQ To SQL
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
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.