One of the most frequently asked query type is outer join. With T-Sql by using ‘left outer join’ type it was possible to get the records of the Customer table without the orders.

T-Sql usage:

select c.ContactName, OrderID= case when o.OrderID is null then ‘(no orders)’ else convert(varchar,o.OrderID) end
from Customers c
left outer join Orders o on o.CustomerID = c.CustomerID

With Linq type instead of “outer” sentence, “DefaultIfEmpty()” method is used to get “left outer join”.

The usage of the T-Sql query above in Linq type:
(more…)

If you newbie to linq, you should download and install some tools for trying samples and developing apps.

Visual Studio 2008 and Sql Server express editions:

(more…)

T-Sql coalesce usage:

select c.ContactName, EmpID= coalesce(o.EmployeeID,-1)
from Customers c
left outer join Orders o on o.CustomerID = c.CustomerID

Same Query with Linq ?? operator:

(more…)

T-Sql exists Usage:

select c.CompanyName, o.*
from Customers c
join Orders o on o.CustomerId = c.CustomerId
where o.ShipCountry = ‘Germany’

Linq contains usage instead of exists:

DataClassesDataContext db = new DataClassesDataContext();
var Cust = from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID
where o.ShipCountry == “Germany”
select new { c.CompanyName, o.OrderDate, o.ShipCountry };

T-Sql exists Usage:

select *
from Customers c
where exists(select *
from Orders
where ShipCountry = ‘Germany’
and CustomerId = c.CustomerId)

Linq contains usage instead of exists:

DataClassesDataContext db = new DataClassesDataContext();
var Cust = from c in db.Customers
where (from o in db.Orders
where o.ShipCountry == “Germany”
select o.CustomerID).Contains(c.CustomerID)
select c;