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
Hi blogger, i must say you have high quality articles here.
Your blog should go viral. You need initial traffic only.
How to get it? Search for: Mertiso’s tips go viral
You have posted: It is important to be able to translate form LinqPad ; don’t you mean It is important to be able to translate from LinqPad.
fyi: The folk who control the network on which I work don’t allow the use of Northwind database. (It’s a STIG finding. http://en.wikipedia.org/wiki/Security_Technical_Implementation_Guide) Not your problem, of course, but I appreciate your articles. Just wish you didn’t use Northwind. Take care, Paul
If you are doing any tutorial for newbies (as this was) then you need to appreciate the audience’s level and for such a small demonstration such as this I think it’s very poor. The URL to the database was not direct link. You should have checked that the NW database was IDENTICAL to yours. you should even have explained how to turn on line numbering and the format of the numbers.
For future demonstrations may I suggest it would be better to use the new AdventureWorks database so that we all are, so-to=speak singing from the same hymn book.
As long as you’re taking flack about Northwind, let’s really mix things up with an equivalent Lambda expression (Language = C# Statements, make sure you click the “Use Northwind” hyperlink in the upper-right corner):
DataContext dataContext = this;
dataContext.GetTable()
.Where (Customers => (Customers.City == “London”))
.Select (
Customers =>
new
{
CustomerID = Customers.CustomerID,
CompanyName = Customers.CompanyName,
ContactName = Customers.ContactName
}
)
.Dump(“London Customers”)
;
And you thought LINQPad was only for LINQ. Shame on you 🙂
The GetTable got mangled during copy/paste of my example above. The GetTable should have been preceded by Customers in angled brackets. I suspect other people posting replies are a victim of WordPress’es lack of good copy/paste behavior.
If you’re looking to buy these atcirles make it way easier.
Ok, it seems YOUR Northwind has a Contacts table (well, SOMETIMES…) instead of a Customers table. I refactored the first query to:
DataContext dataContext=this;
var query=from Customers in dataContext.GetTable()
where Customers.City==”London”
select new {Customers.CustomerID,Customers.CompanyName,Customers.ContactName};
query.Dump();
and all is well…
Lesson: All Northwind db’s weren’t created equally.
Curiously, your examples starting at the “Map the table to an Entity Class” section switch over to using the Customers table… Inquiring minds want to know if this is a case for the Fringe Division (were there two Jesse’s writing this article?)
I ran into the same error as newbie above…which indicates that the Northwind db I downloaded from:
http://www.microsoft.com/download/en/details.aspx?DisplayLang=tw&id=23654
doesn’t contain a Contacts table (as easily seen in the LINQPad connection treeview and within SSMS).
I’ve also checked with the CodePlex project at:
http://sqlserversamples.codeplex.com/
thinking that there might be a different Northwind db there. No such luck! The zipped copy there is the same as on the 23654 page. Nor could I find any other copy of it at any other obvious looking CodePlex project…
Thus, the primary question is: Where’d you get your Northwind db? (Can you give us a better URL than http://msdn.microsoft.com/en-us/library/ms227484%28v=vs.80%29.aspx ? That URL is an Alice-In-Wonderland rabbit hole…)
Of course, the secondary question is: Why aren’t you using AdventureWorks, since Northwind is deprecated (as noted on the 23654 page)? (No one should be using Northwind these days…)
I didn’t realize i was using an older (or odd) copy of Northwind. In any case, the lessons here transcribe pretty easily to any mdf file. If you think it is truly confusing, I’m happy to write out the corresponding steps with AdventureWorks.
No, that’s ok. I got the gist of it. But you might change the first two queries to use Customers instead…
Overall, your tutorials are pretty good. Thanks!
Newbie here. Selected “C# Statement”. Copied an pasted the first query into the Query 1 window of LINQPad and got the following error:
The type or namespace name ‘Contacts’ could not be found (press F4 to add a using directive or assembly reference)
Used Customers instead of Contacts and it worked ok. I have the version of LINQPad with Intellisense so was able to play around nicely.
Thanks for the article.
Please clarify your post so that newbies will know what to set the all-important “Language:” dropdown box in LINQPad to. They may become confused unless you are crystal clear about whether your examples rely upon a “Language” setting of C# Expression, C# Statements, C# Program, or something else. I know I may seem picky, but even those of us who are fairly familiar with LINQPad (2.x or 4.x) will apreciate the clarfication rather than relying upon the vague “leave the other settings as-is…”.
Very good point, and I should have done so. For the examples in this posting you’ll want to set the language to C# Statements.
I have no trouble biindng a TextBlock to a property in a ViewModel and having it show up at design time. In fact, the ViewModel base class has a property called IsInDesignMode that you can use to return data that will only show up at design time.The way the InjectedViewModelLocator works is that it uses MEF to supply a service agent, either Real or Mock, to a ViewModel. MEF does not play nicely when used in design mode, so I have code in the locator that ensures MEF is only called at runtime and not design time.You can specify an agent type of Real or Mock in an attribute you place on the service agent. This acts as metadata for MEF so that the locator can decide to create either a real or a mock service agent. For example, if you look at the definition of MockItemListServiceAgent, you’ll see the ServiceAgentExport attribute with the AgentType set to Mock.Cheers,Tony
LinqPad is an essential tool for me now. I have found it quicker and easier, for the most part, to use it to do quick, ad hoc, queries of the DB than firing up MSSQL and writing a SQL query.
Honestly, Joseph’s kit is well worth investigating and I bought a copy that has intellisense enabled too.
Stop reading this and go download it!
Dittos on the worthiness of the extra-cost IntelliSense add-on to LINQPad.
More dittos on using LINQPad for quick, ad hoc queries, like this one against the Microsoft Enterprise Library Logging database for displaying only the most recent entries:
// view most recent Enterprise Library log entries
from l in Logs
where l.Timestamp > DateTime.UtcNow.AddHours(-1)
orderby l.LogID descending
select new
{
l.LogID
, l.Timestamp
, l.Priority
, l.Severity
, l.Title
, l.Message
}