WebMingle: Simple

Saturday, September 4, 2010

Partitioning Operators

Home	Table Design

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} " , 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} " , 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} ",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} ",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

Friday, September 3, 2010

Quantifiers

Home	Table Design

Any - Simple

This sample uses Any to determine if any of the words in the array contain the substring 'iaa'.

DataClassesDataContext db = new DataClassesDataContext();

LINQ/ Lambda:

var result = (from c in db.Customers
select c).Any(c => c.Name.Contains("iaa"));

Response.Output.WriteLine("There is a word that contain iaa : {0} ", result);

Result:

There is a word that contain iaa : False

Any - Grouped

This sample uses Any to return a grouped a list of Customer only for Education that have at least one “2” education.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from c in db.Customers
group c by c.Sex into CustGroup
where CustGroup.Any(p => p.Education == "2")
select new { CustGroup.Key, CustGroup };

foreach (var item in result)
{
Response.Output.WriteLine("Key : {0} ", item.Key);

foreach (var value in item.CustGroup)
Response.Output.WriteLine("Name : {0} ", value.Name);
}

Result:

Key : 1
Name : Rachit Rokad
Name : Ayush Patel

All - Simple

This sample uses All to determine whether records contains only MCA.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from value in db.Customers
select value).All(n => n.Education == "MCA");

Lambda:

var result = db.Customers.All(n => n.Education == "MCA");

Response.Output.WriteLine("The list contains MCA : {0} ", result);

Result:

The list contains MCA : False

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = (from value in db.Customers
where value.Age > 24
select value).All(n => n.Education == "MCA");

Lambda:

var result = db.Customers.Where(n => n.Age>24).All(n => n.Education == "MCA");

Response.Output.WriteLine("The list contains MCA : {0} ", result);

Result:

The list contains MCA : True

All - Grouped

This sample uses All to return a grouped a list of Customer only for Education that have all of them are in MCA and age higher than 25.

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from value in db.Customers
where value.Age>25
group value by value.Sex into Cust
where Cust.All(p => p.Education == "MCA")
select new { Cust.Key, Cust };

foreach (var item in result)
{
Response.Output.WriteLine("Sex: {0} ", item.Key);

foreach (var ite in item.Cust)
Response.Output.WriteLine("Name: {0} ", ite.Name);
}

Result

Sex: 1
Name: Rachit Rokad