SQL Sever 2008 - Merge

Sample:

merge into item_lookup l
using (
select 3 product_type_id, 3 item_id
) t on l.product_type_id = t.product_type_id
when matched then update
set l.item_id = t.item_id
when not matched then insert
(product_type_id, item_id)
values
(t.product_type_id, t.item_id);

Linq to SQL with GroupBy - SUM

http://csainty.blogspot.com/2008/01/linq-to-sql-groupby.html

Linq to SQL
var pcs = from a in db.Addresses
group a by a.PostalCode into g
select new {PostalCode = g.Key, Orders = (int?)g.Sum(addr =>addr.SalesOrderHeaders_ShipTo.Count) ?? 0, OrderValue = (decimal?)g.Sum(addr => addr.SalesOrderHeaders_ShipTo.Sum(o => o.SubTotal + o.Freight)) ?? 0m};

SQL
SELECT [t5].[PostalCode]
, COALESCE([t5].[value],@p0) AS [Orders]
, COALESCE([t5].[value2],@p1) AS [OrderValue]
FROM (
SELECT SUM([t4].[value2]) AS [value]
, SUM([t4].[value]) AS [value2]
, [t4].[PostalCode]
FROM (
SELECT (
SELECT SUM([t3].[SubTotal] + [t3].[Freight])
FROM [Sales].[SalesOrderHeader] AS [t3]
WHERE [t3].[ShipToAddressID] = [t2].[AddressID]
) AS [value]
, [t2].[PostalCode]
, [t2].[value] AS [value2]
FROM (
SELECT (
SELECT COUNT(*)
FROM [Sales].[SalesOrderHeader] AS [t1]
WHERE [t1].[ShipToAddressID] = [t0].[AddressID]
) AS [value]
, [t0].[AddressID]
, [t0].[PostalCode]
FROM [Person].[Address] AS [t0]
) AS [t2]
) AS [t4]
GROUP BY [t4].[PostalCode]
) AS [t5]

Linq to SQL with left outer join

http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx

Linq to SQL
var query = from c in Customers
where c.CustomerID.StartsWith("b")
join o in 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};

SQL
SELECT c.CustomerID
, c.ContactName
, (CASE WHEN o.OrderID IS NULL THEN @p1 ELSE o.OrderID END) AS value
FROM Customers c
LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.CustomerID LIKE @p0

Linq Query using DataTable

var query = from c in datatable.AsEnumerable()
where c.Field("value") == "A"
group by c by c.field("rowID")
select c;