What you will learn in this chapter:
wrox.com code downloads for this chapter
You can find the wrox.com code downloads for this chapter at www.wrox.com/remtitle.cgi?isbn=9781118336922 on the Download Code tab. The code in the Chapter15 folder is individually named according to the names throughout the chapter.
SQL is so simple to use; wouldn't it be nice if you could apply its simplicity to data structures other than databases? That's what Microsoft's LINQ is designed to do. LINQ stands for Language INtegrated Query and is a feature that appeared with the debut of Visual Studio 2008.
Although you can use SQL with relational database objects, LINQ can also query object types where the data source is not a database. You can use LINQ to query an object type, including arrays, class objects, and XML, in addition to relational databases. Visual Studio incorporates the LINQ query engine directly but also has defined an extension definition that enables third-party data sources to tie in to the engine via a translator. Just as SQL queries result in datasets stored in memory, LINQ returns a collection of memory-based objects.
SQL has specific keywords used in query statements, and LINQ provides a similar set of keywords. Perhaps the easiest way to begin to understand LINQ is to try the following simple example.
Listing 15-1: Program Using LINQ (frmMain.cs)
using System; using System.Collections.Generic; using System.Linq; using System.Windows.Forms; public class frmMain : Form { private const int MAXNUM = 100; // Max random numbers static List<int> numbers = new List<int>(); // static list private Button btnClose; private ListBox lstOutput; private ListBox lstFull; private TextBox txtLow; private Label label1; private Label label2; private TextBox txtHi; private Label label3; private Label label4; private Label label5; private Button btnCalc; #region Windows code //========================== Constructor ============================ public frmMain() { InitializeComponent(); GenerateRandomValues(); } //=========================== Program Start ========================= public static void Main() { frmMain main = new frmMain(); Application.Run(main); } //========================= Helper Methods =========================== private void btnCalc_Click(object sender, EventArgs e) { int lo; int hi; lstOutput.Items.Clear(); SetTheLimits(out lo, out hi); DoLINQQuery(lo, hi); } /**** * Purpose: To generate a MAXNUM sequence of random integer values * * Parameter list: * int lo the lower limit for query * int hi the upper limit for query * * Return value: * void * ****/ private void DoLINQQuery(int lo, int hi) { var query = from p in numbers // The "Query" where p > lo && p < hi select p; foreach (var val in query) // Display results { lstOutput.Items.Add(val.ToString()); } } /**** * Purpose: Set the upper and lower limits of the query * * Parameter list: * out int lo reference to the lower limit * out int hi reference to the upper limit * * Return value: * void * ****/ private void SetTheLimits(out int lo, out int hi) { bool flag = int.TryParse(txtLow.Text, out lo); // Input validation if (flag == false) { MessageBox.Show("Numeric only, 0 to 100", "Input Error"); txtLow.Focus(); } flag = int.TryParse(txtHi.Text, out hi); if (flag == false) { MessageBox.Show("Numeric only, 0 to 100", "Input Error"); txtHi.Focus(); } } /**** * Purpose: To generate a MAXNUM sequence of random integer values * * Parameter list: * void * * Return value: * void * ****/ private void GenerateRandomValues() { int temp; DateTime current = DateTime.Now; Random rnd = new Random((int)current.Ticks); for (int i = 0; i < MAXNUM; i++) // Random values { temp = rnd.Next(MAXNUM); numbers.Add(temp); // Copy into list lstFull.Items.Add(temp.ToString()); } } private void btnClose_Click(object sender, EventArgs e) { Close(); } }
using System; using System.Collections.Generic; using System.Linq; using System.Windows.Forms;
System System.Core System.Data System.Data.DataSetExtensions System.Data.Linq System.Drawing System.Windows.Forms
List<int> numbers = new List<int>();
var query = from p in numbers // The "Query" where p > lo && p < hi select p;
var myVar = 61;
var myVar = 61; object myObj = 61; myVar += 1; myObj += 1;
long big1 = myVar; long big2 = myObj;
myVar = "Lynne York"; myObj = "Tom Bangert";
var myVar; // Causes compiler error!
Implicitly-typed local variables must be initialized
var query = from p in numbers // The "Query" where p > lo && p < hi select p;
foreach (var val in query) // Display results lstOutput.Items.Add(val.ToString());
Listing 15-2: Using LINQ with String Data (frmMain.cs)
using System; using System.Linq; using System.Windows.Forms; public class frmMain : Form { static int passes = 0; static int count; private Button btnClose; private ListBox lstOutput; private ListBox lstFull; private Label label1; private TextBox txtState; private Button btnCalc; #region Windows code public frmMain() { InitializeComponent(); ShowAll(); } public static void Main() { frmMain main = new frmMain(); Application.Run(main); } private void btnCalc_Click(object sender, EventArgs e) { ShowAll(); } private void ShowAll() { int i; var friends = new[] { new {name = "Tom", state = "IN"}, new {name = "Terry", state = "MT"}, new {name = "Tammy", state = "IN"}, new {name = "Jane", state = "OH"}, new {name = "Don", state = "IN"}, new {name = "John", state = "IN"}, new {name = "Linda", state = "FL"}, }; if (passes == 0) { count = friends.GetUpperBound(0); i = 0; for (i = 0; i <= count; i++) { lstFull.Items.Add(friends[i].name + " " + friends[i].state); } passes++; } else { lstOutput.Items.Clear(); var query = from p in friends // The "Query" where p.state == txtState.Text.ToUpper() select p; foreach (var val in query) // Display results lstOutput.Items.Add(val.name + " " + val.state); } } private void btnClose_Click(object sender, EventArgs e) { Close(); } }
var query = from p in Friends // The "Query" where p.state == txtState.Text.ToUpper() select p; foreach (var val in query) // Display results lstOutput.Items.Add(val.name + " " + val.state);
var friends = new[] { new {name = "Tom", state = "IN", age = 48}, new {name = "Terry", state = "MT", age = 61}, new {name = "Tammy", state = "IN", age = 46}, new {name = "Jane", state = "OH", age = 65}, new {name = "Don", state = "IN", age = 60}, new {name = "John", state = "IN", age = 61}, new {name = "Debbie", state = "IN", age = 58}, };
var query = from p in Friends // The "Query" where p.age < age select p;
Keyword/Operator | Description |
select, selectmany | Like SELECT in SQL, selectmany may be used with another collection and enables the result set to return pairs. |
where | Similar to the WHERE predicate in SQL. |
join, groupjoin | Enables result sets to span multiple tables based upon matching keys in the tables. |
take, takewhile | Selects the first N objects from a collection. takewhile uses a predicate to further refine the query. |
skip, skipwhile | A complement of the take operator. The set skips the first N objects in the collection. skipwhile is the complement of takewhile. |
oftype | Enables you to select elements of a certain type. |
concat | Enables concatenation of two collections. |
orderby, thenby | Specifies the primary sort order for a collection. The default is ascending order. You can use orderbydescending to reverse the default order. thenby enables subsequent orders after the primary sort key. |
reverse | Reverses the current order of the collection. |
groupby | Returns a collection of objects that supports the IGrouping<key, values> interface. |
distinct | Removes all duplicates from the result set. |
union, intersect, except | Used to perform specialized operations on two sequences. |
equalall | Checks to see if all elements in two collections are equal. |
first,firstordefault, last, lastordefault | Uses a predicate to return the first element for which the predicate is logic True. An exception is thrown if no match is found in the collection. firstordefault is like first but returns the first item in the collection if no match is found (that is, if no exception is thrown). last works in a similar fashion but looks for the last match in the collection. |
single | Uses a predicate to find a match but throws an exception if none is found. |
elementat | Returns an element of the collection at the specified index. |
any, all, contains | Uses a predicate to see if there are any matches (returns logic True or False), if they all match, or if the collection contains a match (returns logic True or False). |
count, sum, min, max, average, aggregate | Like the aggregate functions in SQL. |
This chapter presented a brief introduction to LINQ, which brings a lot of SQL query functionality to data structures other than databases. I encourage you to experiment with the sample programs in this chapter and try to create a different array on your own. After you do that, add data to the tables so that they use LINQ instead of SQL to query the database. This should solidify your understanding of the database concepts in this chapter as well as LINQ.
You can find the answers to the following exercises in Appendix A.
Topic | Key Points |
LINQ | Language Integrated Query. |
LINQ query | How LINQ may be used to query data objects other than databases. |
var | The data tied to LINQ whose type is determined by the context in which it is used. |
LINQ namespaces | Certain namespaces must be available to programs that use LINQ. |
LINQ advantages | The ability to apply SQL-like queries to objects other than databases. |
18.223.170.21