LINQ Operators
|
|
LINQ Operators |
Introduction to LINQ Operators
Letting a Sub-List
We saw that you can get the result of a LINQ statement from the select section. In reality, the select statement simply indicates that the result is ready and it hands it to the other parts of the program. Instead of getting the result directly from the select statement, you can first store it in a local LINQ variable. This allows you to treat the result as a variable that you can then manipulate before getting the final result.
To declare a local variable in the LINQ statement, you can use the let operator followed by the desired name of the variable. You (must) assign a value or an expression to the variable. You must create this new statement before the select statement to hold the result. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Basic Algebra</title>
</head>
<body>
@{
var numbers = new int[] { 12, 45, 38, 5, 128, 525, 2448, 39, 632, 207 };
var number = from n
in numbers
let even = n % 2
select n;
}
<h2>Basic Algebra</h2>
<ul>
@foreach (var member in number)
{
<li>@member</li>
}
</ul>
</body>
</html>
This would produce:
Notice that the result includes all items from the original list. If you want to use or get the results on a let statement, return it on the select statement. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Basic Algebra</title>
</head>
<body>
@{
var numbers = new int[] { 12, 24, 45, 38, 5, 128, 525, 64, 2448, 39, 632, 207 };
var number = from n
in numbers
let even = n % 2
select even;
}
<h2>Examination of Even Numbers</h2>
<ul>
@foreach (var nbr in number)
{
<li>Number is even: @nbr</li>
}
</ul>
</body>
</html>
This would produce:
If you need a where condition but your let variable would be used only to hold the final result, you can declare that let variable after the where statement. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Basic Algebra</title>
</head>
<body>
@{
var numbers = new int[] { 12, 24, 45, 38, 5, 128, 525, 64, 2448, 39, 632, 207 };
var number = from n
in numbers
where n % 2 == 0
let even = n
select even;
}
<h2>Even Numbers</h2>
<ul>
@foreach (var nbr in number)
{
<li>@nbr</li>
}
</ul>
</body>
</html>
This would produce:
You can create the let variable before the where statement and you would get the same result:
@{
var numbers = new int[] { 12, 24, 45, 38, 5, 128, 525, 64, 2448, 39, 632, 207 };
var number = from n
in numbers
let even = n
where n % 2 == 0
select even;
}
Creating a new List
To get the final result of a query, you may want to combine a few values (or a few fields or properties of a class). Besides, or instead of, the let operator, you can use the new operator to create such a combination.
To use the new operator, after the select keyword, type new followed by an opening curly bracket "{" and a closing curly bracket "}". Inside the brackets, create an expression as you see fit and assign it to a local variable in the curly brackets. When accessing the result in your foreach loop, apply the period operator on the foreach variable to access the new local variable(s). Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Basic Algebra</title>
</head>
<body>
@{
var numbers = new int[] { 12, 24, 45, 38, 5, 128, 525, 64, 2448, 39, 632, 207 };
var number = from n
in numbers
select new { Doubler = n * 2 };
}
<h2>Doubled Numbers</h2>
<ul>
@foreach (var nbr in number)
{
<li>@nbr.Doubler</li>
}
</ul>
</body>
</html>
This would produce:
To make the statement easier to read, you can span it on various lines:
@{
var numbers = new int[] { 12, 24, 45, 38, 5, 128, 525, 64, 2448, 39, 632, 207 };
var number = from n
in numbers
select new
{
Doubler = n * 2
};
}
One of the most valuable features of the new operator is that it allows you to create a selected query of fields of the members of a class. For example, you can create a new body in which you list the desired members of the class, each member must be qualified using the period operator. Here are examples:
var fullNames = from empls
in employees
select new
{
empls.EmployeeNumber,
empls.LastName
};
If you want one of the new fields to be a combination of the members of the class, you must create a name for it and assign an expression or a value to it. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Basic Algebra</title>
</head>
<body>
@{
var numbers = new int[] { 12, 24, 45, 38, 5, 128, 525, 64, 2448, 39, 632, 207 };
var number = from n
in numbers
select new
{
Factor = "Number",
Doubler = n * 2
};
}
<h2>Doubled Numbers</h2>
<ul>
@foreach (var nbr in number)
{
<li>@nbr.Factor: @nbr.Doubler</li>
}
</ul>
</body>
</html>
This would produce:
In the same way, if you want, in the new body, youcan create a name for each field and assign it the member of the class that holds the actual fields. Here are examples:
@{
var staffMembers = from empls
in employees
select new
{
Number = empls.EmployeeNumber,
FName = empls.FirstName,
LName = empls.LastName,
Wage = empls.HourlySalary
};
}
Grouping the Values
Grouping by Categories
For your query, you can ask the compiler to produce a list where the values are grouped by categories. To support grouping, the LINQ provides the group...by operation. The primary formula to follow is:
var sub-list-name = from value-holder in list group value-holder by Category;
The new keywords are group and by. They are required. The value-holder is the same variable created from the from operator. The category is the new factor of our formula. It must be a value that can be gotten from the value-holder list. Here is an example:
@{
CountryStatistics2.Controllers.HomeController hc = new CountryStatistics2.Controllers.HomeController();
var states = from state
in hc.States
group state by state.Region;
}
Notice that you don't create a select statement after the group...by clause.
The group...by expression creates a list of categories, not a list of values, unlike the select statement would do. This means that each item of the list produced by the group...by clause is a category. Therefore, to access a category, use a foreach loop that applies to each item of the list. Here is an example:
@{
HomeController hc = new HomeController();
var regions = from state
in hc.States
group state by state.Region;
@foreach(var region in regions)
{
}
}
Each category of the group...by list is its own list of items. Therefore, after accessing a category, inside of it, you can access an item. To do this, use a nested foreach loop.
Practical Learning: Grouping Records
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace AltairRealtors1.Controllers
{
public class HomeController : Controller
{
public Models.Property[] Properties;
public HomeController()
{
. . . No Chantge
}
public ActionResult Index()
{
return View();
}
public ActionResult About()
{
ViewBag.Message = "Your application description page.";
return View();
}
public ActionResult Contact()
{
ViewBag.Message = "Your contact page.";
return View();
}
public ActionResult Listing()
{
return View();
}
public ActionResult ConditionalSelection()
{
return View();
}
public ActionResult GroupingProperties()
{
return View();
}
}
}
@{
ViewBag.Title = "Altair Realtors - Grouping Properties";
}
@{
int i = 1;
AltairRealtors1.Controllers.HomeController hc = new AltairRealtors1.Controllers.HomeController();
var houses = from prop
in hc.Properties
group prop by prop.PropertyType;
}
<h2 class="text-center">Altair Realtors - Grouping Properties by Types</h2>
<div class="centralizer">
<table style="width: 100%" border="6">
<tr style="background-color: #560404; color: #FFFFFF">
<td style="width: 30px; text-align: center; color: #FFFFFF;">#</td>
<td style="text-align: center">Category</td>
<td style="text-align: center">City</td>
<td style="text-align: center">Locality</td>
<td>County</td>
<td style="text-align: center">State</td>
<td>Condition</td>
<td>Bed(s)</td>
<td>Bath(s)</td>
<td>Garage?</td>
<td>Stories</td>
<td style="text-align: right">Market Value</td>
</tr>
@foreach(var grouping in houses)
{
foreach (AltairRealtors1.Models.Property property in grouping)
{
if (i % 2 == 0)
{
<tr style="background-color: #d3b8b8">
<td style="text-align: center; color: #800000;">@i</td>
<td style="text-align: center;">@property.PropertyType</td>
<td>@property.City</td>
<td>@property.Locality</td>
<td>@property.County</td>
<td style="text-align: center">@property.State</td>
<td>@property.Condition</td>
<td>@property.Bedrooms</td>
<td>@property.Bathrooms</td>
<td>@property.HasIndoorGarage</td>
<td>@property.Stories</td>
<td style="text-align: right">@property.MarketValue.ToString("#,000")</td>
</tr>
}
else
{
<tr style="background-color: #FFFFFF">
<td style="text-align: center; color: #800000;">@i</td>
<td style="text-align: center;">@property.PropertyType</td>
<td>@property.City</td>
<td>@property.Locality</td>
<td>@property.County</td>
<td style="text-align: center">@property.State</td>
<td>@property.Condition</td>
<td>@property.Bedrooms</td>
<td>@property.Bathrooms</td>
<td>@property.HasIndoorGarage</td>
<td>@property.Stories</td>
<td style="text-align: right">@property.MarketValue.ToString("#,000")</td>
</tr>
}
i++;
}
}
</table>
</div>
@{
ViewBag.Title = "Altair Realtors - Grouping Properties";
}
@{
int i = 1;
AltairRealtors1.Controllers.HomeController hc = new AltairRealtors1.Controllers.HomeController();
var houses = from prop
in hc.Properties
group prop by prop.PropertyNumber.ToString();
}
<h2 class="text-center">Altair Realtors - Grouping Properties</h2>
@{
string strCondition = string.Empty;
if (IsPost)
{
strCondition = Request["cbxGroups"];
switch (strCondition)
{
case "Types":
houses = from prop
in hc.Properties
group prop by prop.PropertyType.ToString();
break;
case "Cities":
houses = from prop
in hc.Properties
group prop by prop.City;
break;
case "States":
houses = from prop
in hc.Properties
group prop by prop.State;
break;
case "Conditions":
houses = from prop
in hc.Properties
group prop by prop.State;
break;
default:
houses = from prop
in hc.Properties
group prop by prop.PropertyNumber.ToString();
break;
}
}
}
@using (Html.BeginForm())
{
<p class="text-center">
list the properties by
<select name="cbxGroups">
<option></option>
<option>Types</option>
<option>Cities</option>
<option>States</option>
<option>Conditions</option>
</select>
<input type="submit" name="btnSubmit" value="Submit" />
</p>
}
<div class="centralizer">
<table style="width: 100%" border="6">
<tr style="background-color: #560404; color: #FFFFFF">
<td style="width: 30px; text-align: center; color: #FFFFFF;">#</td>
<td style="text-align: center">Category</td>
<td style="text-align: center">City</td>
<td style="text-align: center">Locality</td>
<td>County</td>
<td style="text-align: center">State</td>
<td>Condition</td>
<td>Bed(s)</td>
<td>Bath(s)</td>
<td>Garage?</td>
<td>Stories</td>
<td style="text-align: right">Market Value</td>
</tr>
@foreach(var grouping in houses)
{
foreach (AltairRealtors1.Models.Property property in grouping)
{
if (i % 2 == 0)
{
<tr style="background-color: #d3b8b8">
<td style="text-align: center; color: #800000;">@i</td>
<td style="text-align: center;">@property.PropertyType</td>
<td>@property.City</td>
<td>@property.Locality</td>
<td>@property.County</td>
<td style="text-align: center">@property.State</td>
<td>@property.Condition</td>
<td>@property.Bedrooms</td>
<td>@property.Bathrooms</td>
<td>@property.HasIndoorGarage</td>
<td>@property.Stories</td>
<td style="text-align: right">@property.MarketValue.ToString("#,000")</td>
</tr>
}
else
{
<tr style="background-color: #FFFFFF">
<td style="text-align: center; color: #800000;">@i</td>
<td style="text-align: center;">@property.PropertyType</td>
<td>@property.City</td>
<td>@property.Locality</td>
<td>@property.County</td>
<td style="text-align: center">@property.State</td>
<td>@property.Condition</td>
<td>@property.Bedrooms</td>
<td>@property.Bathrooms</td>
<td>@property.HasIndoorGarage</td>
<td>@property.Stories</td>
<td style="text-align: right">@property.MarketValue.ToString("#,000")</td>
</tr>
}
i++;
}
}
</table>
</div>
Grouping by Conditional Categories
To restrict the list of records in the result, you can add a where condition. Here is an example:
var videos = from vdos
in lstVideos
where vdos.Title.StartsWith("S")
group vdos by vdos.Rating;
Grouping Into a Variable
When you create a grouping, you get a list of categories of values and that list becomes ready to be used. In some cases, before exploring the list, you may want to perform an operation on it. As one way you can do this, you can store that list in a (local) variable and use that variable as if it were a from variable.
To declare a variable to store the grouping values, use the into contextual keyword through the following formula:
var sub-list-name = from value-holder in list group value-holderby category into group-variable ...;
The group-variable is the new placeholder in our formula. You specify it as a regular name of a variable. Here is an example:
@{
CountryStatistics2.Controllers.HomeController hc = new CountryStatistics2.Controllers.HomeController();
var regions = from state
in hc.States
group state by state.Region into sections
}
Before ending the LINQ statement, you must create either a group...byexpression or a select statement that uses the into variable.
Joining Records
Introduction to Cross Joins
Joining records consists of creating a list of records from more that one set of records. In some cases, depending on how you want to join the records, they may not have anything in common or they may share something.
To simplest way to create a join is, after declaring the query variable, create a from...in... statement for each list:
@{
var organization = from provs in provinces
from govs in management
...;
}
As seen in previous sections, the from...in stament is followed by a select statement. In this case, create select new {} statement. In the curly brackets, the simplest thing to do is to put the names of the from...in item. In the body of your foreach loop, access each item of the curly brackets. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Country Statistics</title>
</head>
<body>
@{
System.Collections.ObjectModel.Collection<string> provinces = new System.Collections.ObjectModel.Collection<string>()
{
"British Columbia", "Alberta", "Saskatchewan", "Manitoba",
"Ontario", "Quebec", "Newfoundland and Labrador", "New Brunswick", "Nova Scotia", "Prince Edward Island"
};
System.Collections.ObjectModel.Collection<string> management = new System.Collections.ObjectModel.Collection<string>()
{
"Province", "Government"
};
var organization = from provs in provinces
from govs in management
select new
{
provs, govs
};
}
<p>Canada: Provinces</p>
<ul>
@foreach (var entity in organization)
{
<li>@entity.govs: @entity.provs</li>
}
</ul>
</body>
</html>
This would produce:
This type of join is called a cross join.
If necessary, you can set a condition to restric the number of records you need. You can do this using the where keyword. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Country Statistics</title>
</head>
<body>
@{
System.Collections.ObjectModel.Collection<string> provinces = new System.Collections.ObjectModel.Collection<string>()
{
"British Columbia", "Alberta", "Saskatchewan", "Manitoba",
"Ontario", "Quebec", "Newfoundland and Labrador", "New Brunswick", "Nova Scotia", "Prince Edward Island"
};
System.Collections.ObjectModel.Collection<string> management = new System.Collections.ObjectModel.Collection<string>()
{
"Province", "Government"
};
var organization = from provs in provinces
from govs in management
where govs == "Government"
select new
{
provs, govs
};
}
<p>Canada: Provinces</p>
<ul>
@foreach (var entity in organization)
{
<li>@entity.govs: @entity.provs</li>
}
</ul>
</body>
</html>
This would produce:
You can also set a condition on each sub-list. Each condition can following its sub-list statement. Here is an example:
@{
var organization = from provs in provinces
where provs.Contains("b")
from govs in management
where govs.Contains("ov")
select new
{
provs, govs
};
}
The conditions can be created after the sub-list. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Country Statistics</title>
</head>
<body>
@{
System.Collections.ObjectModel.Collection<string> provinces = new System.Collections.ObjectModel.Collection<string>()
{
"British Columbia", "Alberta", "Saskatchewan", "Manitoba",
"Ontario", "Quebec", "Newfoundland and Labrador", "New Brunswick", "Nova Scotia", "Prince Edward Island"
};
System.Collections.ObjectModel.Collection<string> management = new System.Collections.ObjectModel.Collection<string>()
{
"Province", "Entity", "Government", "Land"
};
var organization = from provs in provinces
from govs in management
where provs.Contains("b")
where govs.Contains("ov")
select new
{
provs, govs
};
}
<p>Canada: Provinces</p>
<ul>
@foreach (var entity in organization)
{
<li>@entity.govs: @entity.provs</li>
}
</ul>
</body>
</html>
This would produce:
Starting a Join
A join is a list created by joining two lists that have something in common. To create such a list, specify the collections, specify a value they have in common, and define the joining relationship. The primary formula to follow is:
join join-name in child-list on condition
Start with the join keyword followed by a name. The name can be used later or you can ignore it. Here is an eThe join condition is created before the select statement. Here is an example of starting a join:
var persons = from staffMembers
in employees
join std . . .
select staffMembers;
var vdos = from videos
in lstDirectors
join vdo . . .
select Something;
The join operation is followed by the in keyword. The child-list specifies the list previously created. This can be the list created by the previous in statement. Here is an example:
var persons = from staffMembers
in employees
join std in employees . . .
select staffMembers;
var vdos = from videos
in lstDirectors
join vdo in lstVideos . . .
select Something;
Of course you can write the in statement on its own line. The condition holds a logical expression that specifies the relationship by which the records will be joined.
To support joins, the C# language provides a context-sensitive keyword named equals. To create a join in LINQ, the values used to establish the link in the join statement should use the object of the from statement and that of the join clause. Having those values, you can access the joining property from each object and apply the equals operator on them. The formula would be:
child-field equals parent-field
When you create a condition, the compiler (or rather the LINQ interpreter) would check each record of the child list that has a value for the corresponding property of the parent list.
A cross join is a set that includes all records from two lists as follows: the first record from the first list is associated to each record from the second list. Then the second record from the first list is associated to each record from the second list, and so on. There are two ways you create a cross join in LINQ.
For a cross join, you can use one list of numbers and another list of names. Or, you can use one list for videos and another list of music albums. You can then create a common list that includes items from each list. To create a cross join, you can use a combination of from...in... statement for each sub-list. Here is an example:
var vdos = from videos in lstVideos from albums in lstMusic select . . .;
Then, in the select statement, provide a way to retrieve the members from each list. This can be done by using the new operator and create a list of fields inside the curly brackets.
The second technique of creating a cross join involves two lists that have a relationship. Start with a class that can be used as the basis of a list. Here is an example of a class that would be used to create a list of movie (or video) directors:
public class Director
{
public string DirectorID { get; set; }
public string Name { get; set; }
}
Before creating the join, you should (must) have a member from each class so that these two members will be used to join the lists. These members should hold unique values. In the above class, that would be the role of the DirectorID property, where we would make sure that each record has a unique DirectorID value.
When creating the other class, you should (must) create a property (or field) that represents the unique-value provider of the first class. In our example, when creating a class that will be used to create a list of videos, we must create a property (or field) that represents the DirectorID of the first class. The class can be created as follows:
public class Video
{
public string ShelfNumber { get; set; }
public string Title { get; set; }
public string DirectorID { get; set; }
public string Length { get; set; }
public string Rating { get; set; }
public string YearReleased { get; set; }
public Video(string number = "",
string title = "",
string dir = "",
string len = "",
string ratings = "",
string year = "")
{
ShelfNumber = number;
Title = title;
DirectorID = dir;
Length = len;
Rating = ratings;
YearReleased = year;
}
}
To create a cross join, apply the square brackets to the from and the join statements to indicate the index by which the records would be established (you can use the square brackets if the lists are created as arrays or as List<>). Here is an example of creating a cross join (all properties were created as strings for illustration; this was made so that, when they don't have a value, they would be empty; if they used the int data type, their values would be set to 0):
<!DOCTYPE html>
<html>
<head>
<title>Video Collection</title>
</head>
<body>
@{
Director[] lstDirectors = new Director[3];
lstDirectors[0] = new Director();
lstDirectors[0].DirectorID = "1";
lstDirectors[0].Name = "Rob Marshall";
lstDirectors[1] = new Director() { DirectorID = "2", Name = "Oliver Stone" };
lstDirectors[2] = new Director() { DirectorID = "3", Name = "Jonathan Lynn" };
Video[] lstVideos = new Video[]
{
new Video("927940", "Natural Born Killers", "2", "118 minutes", "R", "1994"),
new Video("", "Platoon", "2", "120 minutes", "", "1986"),
new Video("279472", "Outfoxed"),
new Video("725917", "Distinguished Gentleman (The)", "3", "112 minutes", "R"),
new Video("", "Memoirs of a Geisha", "1", "", "PG-13", "2006"),
new Video("291705", "My Cousin Vinny", "3", "120 minutes", "", "1992"),
new Video("248115", "", "0", "", "R"),
new Video("", "Wall Street", "2", "", "R", "2000")
};
var vdos = from videos
in lstDirectors
join vdo in lstVideos on lstDirectors[1].DirectorID equals lstVideos[1].DirectorID
select new
{
Shelf = vdo.ShelfNumber,
Title = vdo.Title,
Manager = videos.Name,
Length = vdo.Length,
Classification = vdo.Rating,
Copyright = vdo.YearReleased
};
}
<h1>Video Collection</h1>
<table border="2">
<tr>
<td style="width: 70px; text-align: center"><b>Shelf #</b></td>
<td style="width: 220px"><b>Title</b></td>
<td style="width: 120px"><b>Director</b></td>
<td><b>Lendth</b></td>
<td style="width: 50px"><b>Rating</b></td>
<td style="width: 60px; text-align: center"><b>©</b></td>
</tr>
@foreach (var item in vdos)
{
<tr>
<td style="text-align: center">@item.Shelf</td>
<td>@item.Title</td>
<td>@item.Manager</td>
<td>@item.Length</td>
<td style="text-align: center">@item.Classification</td>
<td style="text-align: center">@item.Copyright</td>
</tr>
}
</table>
</body>
</html>
This would produce:
Notice that each record of the parent class (Director) is associted to each record of the child class (Video).
Inner Joins
You can make your final list include only records that have a value for the child property. This is called an inner join.To create an inner join, start with a normal join as done for a cross join. For the join formula, instead of joining the fields using an index, access the key from the value of the join clause and assign it to the corresponding key from the value of the from statement. Here is an example:
var vdos = from videos
in lstDirectors
join vdo in lstVideos on videos.DirectorID equals vdo.DirectorID
select new { Shelf = vdo.ShelfNumber,
Title = vdo.Title,
Manager = videos.Name,
Length = vdo.Length,
Classification = vdo.Rating,
Copyright = vdo.YearReleased };
This would produce:
|
|
||
| Previous | Copyright © 2010-2019, FunctionX, Inc. | Next |
|
|
||