LINQ Pad vs. Visual Studio for Learning LINQ

A LINQ Tutorial

As noted in numerous other postings, I’m convinced that LINQ is an essential aspect of LinqPad 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 upperAddConnectionLinqPad 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. LondonRecords

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, TakeSkip

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

About Jesse Liberty

Jesse Liberty has three decades of experience writing and delivering software projects and is the author of 2 dozen books and a couple dozen Pluralsight & LinkedIn Learning courses. He was a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a Xamarin Certified Mobile Developer and a Xamarin MVP and a Microsoft MVP.
This entry was posted in Data, Linq, Mini-Tutorial, WindowsPhone and tagged . Bookmark the permalink.

21 Responses to LINQ Pad vs. Visual Studio for Learning LINQ

  1. Pingback: LINQ Pad vs. Visual Studio for Learning LINQ – www.nalli.net

  2. Pingback: The Morning Brew - Chris Alcock » The Morning Brew #898

Comments are closed.