WebMingle: 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: ");

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

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: ");

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

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: ");

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

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 + " " );

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 +" ");

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 ");

foreach (var custSupp in result)
Response.Output.WriteLine("Customer Name : {0}, Supplier Name : {1} " ,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} " , 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} " , 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} ", 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} " ,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

WebMingle

Saturday, September 4, 2010

Projection Operators