A LINQ Tutorial
As noted in numerous other postings, I’m convinced that LINQ is an essential aspect of serious Windows Phone (or virtually any .NET) programming. One of the great tools to come along for learning and working with LINQ is LinqPad, created by Joseph Albahari.
In my forthcoming book Programming Reactive Extensions and Linq we use LinqPad for virtually all the exercises, as I do for many example postings here. It is important to be able to translate form LinqPad (which strips down a program to its essentials) to Visual Studio, where you will almost certainly be doing the bulk of your serious programming.
In this posting we’ll take a LINQ query, examine it in LinqPad and then migrate it to Visual Studio. Along the way we’ll look at how you can interact with a database file in LINQ and how you can set the datacontext for a LINQ to SQL query.
Northwind
To begin, download the Northwind database from Microsoft,
http://msdn.microsoft.com/en-us/library/ms227484%28v=vs.80%29.aspx
and place it in a known location. Open LinqPad and click on Add Connection in the upper left, as shown in the figure. Click Next and then choose AttachDatabase file and browse to the file you just saved. Leave the other settings as-is and click OK.
Notice that the Database setting (at the top of the main window) now indicates Northwind.mdf and the location of your file. You are ready to query against that database.
A Simple Query With A Filter
To begin, let’s do a simple query in which we will find the ContactID, CompanyName and ContactName for every Customer in London,
DataContext dataContext = this; var query = from Contact in dataContext.GetTable<Contacts>() where Contact.City == "London" select new { Contact.ContactID, Contact.CompanyName, Contact.ContactName }; query.Dump();
Running this query returns the appropriate records in the Results Window.
Dump() is a LinqPad method that displays very much as Console.WriteLine does.
Skip and Take
We can narrow these results with a couple standard LINQ operators:
- Skip
- Take
These do pretty much what you’d expect: skip skips over the given number of records, and then take puts the given number of records into the IEnumerable. We can append these to the query and then show the results.
DataContext dataContext = this; var query = from Contact in dataContext.GetTable<Contacts>() where Contact.City == "London" select new { Contact.ContactID, Contact.CompanyName, Contact.ContactName }; var afterSkipping = query.Skip(2); var takingTwo = afterSkipping.Take(2); takingTwo.Dump();
The output of this, as you would expect, is the third and fourth record,
Notice that by using LinqPad all the peripheral code involved with setting up the DataContext, managing the objects associated with the query and so forth are eliminated or simplified, allowing you to focus on the LINQ statements. That’s all well and good, but how do you translate this back into a C# program?
Migrating To Visual Studio
To move this program into Visual Studio, open a new Console application.
Library and Includes
We’re going to be working with LINQ to SQL so be sure to add the library for System.Data.LINQ and add the following two include statements to each file,
using System.Data.Linq; using System.Data.Linq.Mapping;
Map the table to an Entity Class
The very first job is to map the database to an entity; that is, to a class that will represent the table. To do so, create a new class named Customer and adorn the class and its properties with attributes
[Table( Name = "Customers" )] class Customer { [Column( IsPrimaryKey = true )] public string CustomerID { get; set; } [Column( Name = "ContactName" )] public string Contact { get; set; } [Column( Name = "CompanyName" )] public string Company { get; set; } [Column] public string City { get; set; } }
You are free to add properties that do not have the Column attribute and which do not map to columns in the database; thus allowing your objects to extend the values in the database.
Notice the Table attribute has the Name= attribute, allowing your class to have a different name than the table. Similarly, the second and third columns use the Name= attribute to allow a property to map to a column with a different name. Finally, the first property, CustomerID, has the IsPrimaryKey attribute set to true; as must be done for at least one property in every LINQ to SQL class.
Create The DataContext
You are ready to create the all-important DataContext object and then to use that to query the database and to populate instances of your class.
Open Program.cs and in the constructor, create the DataContext, passing in the path to the Northwind database file. On my machine that looks like this:
DataContext db = new DataContext( @"L:\Downloads\Northwind\Northwind.mdf" );
The DataContext encapsulates an ADO.Net Connection object that is initialized with the connection string that you supply in the constructor. This and all of ADO.Net is quite well hidden by LinqToSql
Query The Database
Use that database to retrieve a strongly-typed instance of Table<Customer> (the type you created earlier in Customer.cs). Then create your query statement, extracting it directly from LinqPad,
Table<Customer> Customers = db.GetTable<Customer>(); var CustomerQuery = from c in Customers where c.City == "London" select new { c.CustomerID, c.Company, c.Contact };
All that’s left is to iterate through the results and to show the output, in this case, using Console.WriteLine.
foreach (var cust in CustomerQuery) { Console.WriteLine( "id = {0}, Company = {1}, Contact = {2}", cust.CustomerID, cust.Company, cust.Contact ); }
Here’s the output,
id = AROUT, Company = Around the Horn, Contact = Thomas Hardy id = BSBEV, Company = B's Beverages, Contact = Victoria Ashworth id = CONSH, Company = Consolidated Holdings, Contact = Elizabeth Brown id = EASTC, Company = Eastern Connection, Contact = Ann Devon id = NORTS, Company = North/South, Contact = Simon Crowther id = SEVES, Company = Seven Seas Imports, Contact = Hari Kumar
19 Responses to LINQ Pad vs. Visual Studio for Learning LINQ