Partitioning Operators

Home	Table Design

Take - Simple

This sample uses Take to get only the first 3 elements of the order item.

SQL:

SELECT TOP (3) CustOIId, CustOID, Item, Qty, Unit, Price
FROM CustOrdeItem

DataClassesDataContext db = new DataClassesDataContext();

Lambda:

var result = db.CustOrdeItems.Take(3);

foreach (var item in result)
Response.Output.WriteLine( "Order Item: Item- {0}, Qty- {1},
Price- {2}<br> " , item.Item, item.Qty, item.Price);

Result:

Order Item: Item- Colgate, Qty- 1, Price- 25
Order Item: Item- Grocery, Qty- 15, Price- 20
Order Item: Item- Shoes, Qty- 1, Price- 675


Take - Nested

This sample uses Take to get the first 2 orders from customers.

SQL:

SELECT Name, [Date], Total
FROM (
SELECT TOP (2) Name, [Date], Total
FROM Customer
INNER JOIN CustOrder ON Customer.CustID = CustOrder.CustID
) AS tbl
ORDER BY tbl.[Date]

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from c in db.Customers
join o in db.CustOrders on c.CustID equals o.CustID
select new { c.Name, o.Date, o.Total }).Take(2).OrderBy(o => o.Date);

foreach (var item in result)
Response.Output.WriteLine("Name- {0}, Date- {1}, Total- {2}<br> " ,  item.Name, string.Format("{0:dd-MMM-yyyy}", item.Date), item.Total);
 
Result:

Name- Ayush Patel, Date- 10-Feb-2010, Total- 1000
Name- Rachit Rokad, Date- 12-Feb-2010, Total- 1100

Skip - Simple

SQL:

SELECT Name, [Date], Total
FROM (
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Customer.Name, CustOrder.[Date], CustOrder.Total) AS [ROW_NUMBER], Customer.Name, CustOrder.[Date], CustOrder.Total
FROM Customer
INNER JOIN CustOrder ON Customer.CustID = CustOrder.CustID
) AS tbl
WHERE [ROW_NUMBER] > 1
) AS tbl
ORDER BY [Date], [ROW_NUMBER]

DataClassesDataContext db = new DataClassesDataContext();

LINQ/ Lambda:

var result = (from c in db.Customers
join o in db.CustOrders on c.CustID equals o.CustID
select new { c.Name, o.Date, o.Total }).Skip(1).OrderBy(o => o.Date);

foreach (var item in result)
Response.Output.WriteLine("Name- {0}, Date- {1}, Total- {2}<br> ",item.Name, string.Format("{0:dd-MMM-yyyy}", item.Date), item.Total);

Result:

Name- Rachit Rokad, Date- 12-Feb-2010, Total- 1100
Name- Nenshi, Date- 10-Apr-2010, Total- 800
 

Skip - Nested

This sample uses Take to get all but skip first 1 orders from customers.

DataClassesDataContext db = new DataClassesDataContext();

var result = (from c in db.Customers
join o in db.CustOrders on c.CustID equals o.CustID
select new { c.Name, o.Date, o.Total }).OrderBy(o => o.Date);

var allOrderbutSkip1 = result.Skip(1);

foreach (var item in allOrderbutSkip1)
Response.Output.WriteLine("Name- {0}, Date- {1}, Total- {2}<br> ",item.Name, string.Format("{0:dd-MMM-yyyy}", item.Total));

Result:

Name- Rachit Rokad, Date- 12-Feb-2010, Total- 1100
Name- Nenshi, Date- 10-Apr-2010, Total- 800

==============================================
Shradhdha Zalavadiya |Senior Software Engineer

WebMingle Technology
Accelerated by knowledge. Driven by values.
www.webMingle.in