Saturday, September 4, 2010

LINQ - Home


Home Table Design


Restriction Operators
Where - Simple 1
Where - Simple 2
Where - Simple 3


Projection Operators
Select - Simple
Select - Transformation
Select - Anonymous Types 1
Select - Anonymous Types 2
Select - Filtered
SelectMany - Compound from 1
SelectMany - Compound from 2
SelectMany - Compound from 3
SelectMany - from Assignment
SelectMany - Multiple from


Partitioning Operators
Take – Simple
Take - Nested
Skip - Simple
Skip - Nested


Ordering Operators
OrderBy - Simple 1
OrderBy - Simple 2
ThenBy
ThenByDescending
OrderByDescending - Simple 1
ThenBy - Simple
ThenByDescending - Simple


Grouping Operators
GroupBy - Simple 1
GroupBy - Simple 2


Set Operators
Distinct
Union
Intersect
Except


Conversion Operators
ToArray
ToList
ToDictionary


Element Operators
First – Simple
FirstOrDefault – Simple


Quantifiers
Any – Simple
Any - Grouped
All - Simple
All – Grouped


Aggregate Operators
Count – Simple
Count - Conditional
Count - Grouped
Sum - Simple
Sum - Grouped
Min - Simple
Min - Grouped
Max - Simple
Max - Projection
Max - Grouped
Average - Simple
Average - Projection
Average – Grouped


Miscellaneous Operators
Concat


Join Operator
Inner Join
Group Join with Left Outer Join
Cross Join with Group Join


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

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

Table Design for LINQ


Home Table Design


Customer
CustId Name Sex Education IsStudent Age ContactNo
1 Rachit Rokad 1
0 30 9375750578
2 Poonam Makati 2 MCA 1 25 9426244131
3 Ayush Patel 1 2 1 7
4 Nenshi 2 BE-Comm 0 22
5 Yuti Vachhani 2 8 1 12

Supplier
SupplierId Name Sex ContactNo
1 Rachit Rokad 1 9375750578
2 Vimal Patel 1
3 Parul Makadiya 2
4 Aarti Chapani 2


CustOrder
CustOId CustID Date Total
1 3 10-Feb-2010 1000
2 1 12-Feb-2010 1100
3 4 10-Apr-2010 800

CustOrdeItem
CustOIId CustOID Item Qty Unit Price
1 1 Colgate 1 Pcs 25
2 1 Grocery 15 KG 20
3 1 Shoes 1 Pcs 675
4 2 Plastic chair 2 Pcs 300
5 2 iron 1 Pcs 500
6 3 Bag 1 Pcs 800


Abbreviation
ID Value Type
1 Male Sex
2 Female Sex





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

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

Restriction Operators


Home Table Design

Where - Simple 1


This sample uses where to find all Customers having Age less than 18.

SQL:

SELECT CustID, Name, Sex, Education, IsStudent, Age, ContactNo
FROM Customer
WHERE Age < 18

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result =
from c in db.Customers
where c.Age < 18
select c;

Lambda:

var result = db.Customers.Where(c => c.Age < 18);

Response.Output.WriteLine("Age < 18:<br>");

foreach (var cust in result)
Response.Output.WriteLine(cust.Name + " " + cust.Age + "<br>" );

Result:

Age < 18:  
Ayush Patel 7
Yuti Vachhani 12

 

Where - Simple 2


 This sample uses where to find all Customers that are Student.

SQL:

SELECT CustID, Name, Sex, Education, IsStudent, Age, ContactNo
FROM Customer
WHERE IsStudent = 1

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result =
from c in db.Customers
where c.IsStudent == 1
select c;

Lambda:

var result = db.Customers.Where(c => c.IsStudent == 1);

Response.Output.WriteLine("Student = 1:<br>");
 

foreach (var cust in result)
Response.Output.WriteLine(cust.Name + " " + cust.Age + "<br>" );

Result:

Student = 1:
Poonam Makati 25
Ayush Patel 7
Yuti Vachhani 12

 

Where - Simple 3


This sample uses where to find all Customers that have contact no and age more than 18 years.

SQL:

SELECT CustID, Name, Sex, Education, IsStudent, Age, ContactNo
FROM Customer
WHERE Age >= 18 AND ContactNo IS NOT NULL

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result =
from c in db.Customers
where c.Age >= 18 && c.ContactNo != null
select c;
 
Lambda:

var result = db.Customers.Where(c => c.Age >= 18)
.Where (c => c.ContactNo ! null);
 
Response.Output.WriteLine("Contact No not Blank and Age >= 18:<br>");

foreach (var cust in result)
Response.Output.WriteLine(cust.Name + " " + cust.Age + "<br>" );

Result:

Contact No not Blank and Age >= 18:
Rachit Rokad 30
Poonam Makati 25


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

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

Projection Operators


Home Table Design

Select - Simple


This sample uses select to customers Name, Age and three higher than original Age, having age less than 15 years.

SQL:

SELECT Name, Age + 3 AS ChangeAge, Age
FROM Customer
WHERE Age < 15

LINQ:

DataClassesDataContext db = new DataClassesDataContext();

var result =
from c in db.Customers
where c.Age < 15
select new { c.Name, ChangeAge = c.Age + 3, c.Age };

Lambda:

var result =db.Customers.Where(c => c.Age < 15).
Select(p => new {p.Name, p.Age, ChangeAge = p.Age + 3}) ;

Response.Output.WriteLine("Age + 3 having Age < 15:<br>");

foreach (var cust in result)
Response.Output.WriteLine("Name: " + cust.Name + " Age: "
+ cust.Age + " Changed Age: " + cust.ChangeAge+ " <br>");
 
Result:

Age + 3 having Age < 15:
Name: Ayush Patel Age: 7 Changed Age: 10
Name: Yuti Vachhani Age: 12 Changed Age: 15


Select - Transformation


This sample uses select to Customer and its Sex representing the text.

SQL:

SELECT Name, Value AS Sex
FROM Customer, Abbreviation
WHERE (Customer.Sex = Abbreviation.ID) AND(Type= 'Sex')

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result =
from c in db.Customers
from abb in db.Abbreviations
where c.Sex == abb.ID && abb.Type == "Sex"
select new { c.Name, Sex = abb.Value };

Response.Output.WriteLine("Name and Sex:<br>");


foreach (var cust in result)
Response.Output.WriteLine("Name: " + cust.Name
+ " Sex: " + cust.Sex + " <br>" );

Result:

Name and Sex:
Name: Rachit Rokad Sex: Male
Name: Ayush Patel Sex: Male
Name: Poonam Makati Sex: Female
Name: Nenshi Sex: Female
Name: Yuti Vachhani Sex: Female


Select - Anonymous Types 1


This sample uses select Name of the uppercase and lowercase.

SQL:

SELECT UPPER(Name) AS [Upper], LOWER(Name) AS [Lower]
FROM Customer

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result =
from c in db.Customers
select new { Upper = c.Name.ToUpper(),Lower = c.Name.ToLower() };

Lambda:

var result= db.Customers.Select(p => new{Upper
= p.Name.ToUpper(),Lower = p.Name.ToLower()});

Response.Output.WriteLine("Name:<br>");

foreach (var cust in result)
Response.Output.WriteLine("Upper: " + cust.Upper +
" Lower: " + cust.Lower + " <br>" );
 
Result:

Name:
Upper: RACHIT ROKAD Lower: rachit rokad
Upper: Poonam Makati Lower: poonam makati
Upper: AYUSH PATEL Lower: ayush patel
Upper: NENSHI Lower: nenshi
Upper: YUTI VACHHANI Lower: yuti vachhani


Select - Anonymous Types 2


This sample uses select to Name and representations of Age whether they are adult or not.

SQL:

SELECT Name,
(CASE
WHEN Age > 18 THEN 1 WHEN NOT Age > 18 THEN 0
ELSE NULL
END) AS [Adult]
FROM Customer
 
DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result =
from c in db.Customers
select new { c.Name, Adult = (c.Age > 18) };
 
Lambda:

var result = db.Customers.Select(p => new {p.Name, Adult = (p.Age > 18)});

foreach (var cust in result)
Response.Output.WriteLine(cust.Name + " is Adult? : " + cust.Adult + " <br>" );

Result:

Rachit Rokad is Adult? : True
Poonam Makati is Adult? : True
Ayush Patel is Adult? : False
Nenshi is Adult? : True
Yuti Vachhani is Adult? : False


Select - Filtered


This sample combines select and where to make a query that returns the Customer Name and Age in text format of each employee less than 30 years.

SQL:

SELECT Name, Value AS Sex
FROM Customer, Abbreviation
WHERE (Customer.Age < 30) AND(Abbreviation.Type = 'Sex') AND (ID = Sex)

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from c in db.Customers
from abb in db.Abbreviations
where (c.Age < 30) && (abb.Type == "Sex") && (abb.ID == c.Sex)
select new { c.Name, Sex = abb.Value };

foreach (var cust in result)
Response.Output.WriteLine(cust.Name+ " " +cust.Sex +" <br>");

Result:

Ayush Patel Male
Poonam Makati Female
Nenshi Female
Yuti Vachani Female


SelectMany - Compound from 1


This sample uses a compound from clause to make a query that returns all pairs of Name from both customer and supplier table such that the first two name from customer and name from supplier.

SQL:

SELECT [t1].[Name] AS [custName], [t2].[Name] AS [suppName]
FROM (
SELECT TOP (2) Name
FROM Customer
) AS t1
CROSS JOIN (
SELECT TOP (2) Name
FROM Supplier
) AS t2

DataClassesDataContext db = new DataClassesDataContext();

LINQ/Lambda:

var result = from customer in db.Customers.Take(2)
from supplier in db.Suppliers.Take(2)
select new { custName = customer.Name, suppName = supplier.Name };

Response.Output.WriteLine("Pairs from customer and supplier<br>");

foreach (var custSupp in result)
Response.Output.WriteLine("Customer Name : {0}, Supplier Name : {1} <br>" ,custSupp.custName, custSupp.suppName );

Result:

Pairs from customer and supplier
Customer Name : Rachit Rokad, Supplier Name : Rachit Rokad
Customer Name : Poonam Makati, Supplier Name : Rachit Rokad
Customer Name : Rachit Rokad, Supplier Name : Vimal Patel
Customer Name : Poonam Makati, Supplier Name : Vimal Patel


SelectMany - Compound from 2


This sample uses a compound from clause to select all orders where the order total is less than 1100.00.

SQL:

SELECT[t1].[Name], [t0].[Date], [t0].[Total]
FROM [dbo].[CustOrder] AS [t0], [dbo]. [Customer] AS [t1]
WHERE ([t0].[CustID] = [t1].[CustID]) AND([t0].[Total] < 1100)

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from o in db.CustOrders
from c in db.Customers
where o.Total < 1100
where o.CustID == c.CustID
select new { c.Name, o.Date, o.Total };

foreach (var cust in result)
Response.Output.WriteLine("Order Date : {1}, Customer Name : {0},Order Amount : {1} </br>" , cust.Name, cust.Date, cust.Total );

Result:

Order Date : 2/10/2010 12:00:00 AM, Customer Name : Ayush Patel, Order Amount : 2/10/2010 12:00:00 AM

Order Date : 4/10/2010 12:00:00 AM, Customer Name : Nenshi, Order Amount : 4/10/2010 12:00:00 AM



SelectMany - Compound from 3


This sample uses a compound from clause to select all orders where the order was made in 2010 or later.

SQL:

SELECT Name, [Date], Total
FROM Customer, CustOrder
WHERE (CustOrder.[Date] > '1-Jan-2010') AND (Customer. CustID= CustOrder.CustID)

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from c in db.Customers
from o in db.CustOrders
where c.CustID == o.CustID
where o.Date > new DateTime(2010,1,1)
select new {c.Name, o.Date, o.Total};

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


SelectMany - from Assignment


This sample uses a compound from clause to select all orders where the order total is greater than 900.00.

SQL:

SELECT CustOrder.[Date], CustOrdeItem.Item, CustOrdeItem.Price, CustOrdeItem.Qty
FROM CustOrder, CustOrdeItem
WHERE CustOrder.CustOId = CustOrdeItem.CustOID

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from o in db.CustOrders
from oi in db.CustOrdeItems
where o.CustOId == oi.CustOID
select new {o.Date, oi.Item, oi.Price, oi.Qty };

foreach (var item in result)
Response.Output.WriteLine("Order : Date- {0}, Item- {1}, Price- {2}, " +"Qty- {3} <br>", string.Format("{0:dd-MMM-yyyy}", item.Date), item.Item, item.Price, item.Qty );

Result:

Order : Date- 10-Feb-2010, Item- Colgate, Price- 25, Qty- 1
Order : Date- 10-Feb-2010, Item- Grocery, Price- 20, Qty- 15
Order : Date- 10-Feb-2010, Item- Shoes, Price- 675, Qty- 1
Order : Date- 12-Feb-2010, Item- Plastic chair, Price- 300, Qty- 2
Order : Date- 12-Feb-2010, Item- iron, Price- 500, Qty- 1
Order : Date- 10-Apr-2010, Item- Bag, Price- 800, Qty- 1

 

SelectMany - Multiple from


This sample uses multiple from clauses so that filtering on customers can be done before selecting their orders. This makes the query more efficient by not selecting and then discarding orders for customers outside of Male.

SQL:

SELECT Customer.Name, CustOrder.CustID, CustOrder.[Date]
FROM Customer
INNER JOIN CustOrder ON Customer. CustID = CustOrder.CustID
WHERE (CustOrder.[Date] > '11-Feb-2010') AND (Customer.Sex= 1)

DataClassesDataContext db = new DataClassesDataContext();

LINQ:

var result = from c in db.Customers
where c.Sex == 1
join o in db.CustOrders onc.CustID equals o.CustID
where o.Date> new face="Courier New">DateTime(2010,02,11)
select new {c.Name, o.CustID, o.Date};

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

Result:

Order : Name- Rachit Rokad, CustId- 1, Date- 12-Feb-2010


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

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

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