LINQ Farm: Connecting to a Database with LINQ to SQL
The previous posts in this series have focused on using LINQ to Objects. It is now time to turn our attention to querying a database.
In this post I will show connectivity to Microsoft SQL Server 2005 or SQL Server Express from C# using LINQ. You can use the May 2006 CTP to run the code shown in this post. The May CTP installs on top of Visual Studio 2005. At the time of this writing, the February 2007 Orcas CTP is not yet out, but once it is out, then you should use it, or later versions of Orcas, as the preferred means to run LINQ queries. The screen shots shown in this post are taken from both the May CTP and pre-release versions of Orcas. Except for a few minor details, LINQ will look much the same whether you use the May CTP or an early build of Orcas.
Overview
This post covers three primary topics.
- Using the LINQ to SQL Designer
- Understanding the code generated by the LINQ to SQL Designer
- Writing a simple LINQ query to access data from the Northwind database.
The queries are run against the Northwind database and the text assumes basic familiarity with accessing databases from Visual Studio. If you are not familiar with the Visual Studio Database/Server Explorer, or if you need help getting Northwind installed, then view the Connecting to Northwind post.
NOTES: Since all of the examples shown in this post are run against pre-release software, we can assume that at least some minor changes will occur between now and the time when Orcas ships. You can double click the screen shots in this post to see full size images. All the hotkeys used in this post are part of the Visual C# 2005 Key Binding or its Orcas equivalent. To choose this binding, select Tools | Options from the menu, then select Environment | Keyboard, and apply the appropriate keyboard mapping scheme.
Creating a LINQ Project
The example program shown in this post is based on a simple Console application. The technique for creating a LINQ console application differs slightly in Orcas and in the May CTP.
- In Orcas, bring up the New Project dialog (Ctrl + Shift + N) and create a new console application.
- If you are using the May CTP, bring up the New Project dialog and select the LINQ Preview node under Project Types, and choose LINQ Console Application in the Templates section.
The LINQ to SQL OR Designer
The LINQ to SQL Designer is an invaluable tool for developers who want to connect to a database using LINQ. The process for doing this is essentially identical in the May CTP and in Orcas, but the terminology differs in each instance.
You now need to select the tables from the Northwind database that you want to query. This can be done using the LINQ to SQL Designer. Press Ctrl + Shift + A to bring up the Add Item dialog.
- If you are using the May CTP, choose DLinqObjects from the dialog.
- In Orcas, select LINQ to SQL File.
Press Ctrl + W, L to bring up the Database/Server Explorer and drag the Customer table onto the Designer, as shown in Figure 3.
Figure 03: Viewing the Customer table in the LINQ to SQL Designer. Drag the Customer table from Server Explorer on the left to the Designer, which is shown in the middle of this screen shot.
The purpose of this designer is to help you create a mapping between the tables in your database and the code in your program. With the help of the code generated by the designer, you get both type checking and IntelliSense support for your LINQ queries. In particular, the designer creates a class which represents a single row from the Customer table in the database. It specifies all of the fields in the Customer table and includes numerous methods for helping you to access the row from your code. To view this class declaration, press Ctrl+W,S to bring up the Solution Explorer.
- In Orcas, by default, the mapping can be seen in a file called DataClasses1.designer.cs.
- In the May CTP, the default name for the file is DLinqObjects1.cs. (You may need to compile the project before the code will be generated in the May CTP.)
The first few lines of the class are shown in Listing 1.
Listing 1: The first few lines of the declaration for the Customer table.
1: [global::System.Data.Linq.Table(Name="dbo.Customers")]
2: public partial class Customer : global::System.Data.Linq.INotifyPropertyChanging, global::System.ComponentModel.INotifyPropertyChanged
3: {
4: private string _CustomerID;
5: private string _CompanyName;
6: private string _ContactName;
7: private string _ContactTitle;
8: private string _Address;
9: private string _City;
10: private string _Region;
11: private string _PostalCode;
12: private string _Country;
13: private string _Phone;
14: private string _Fax;
The attribute at the top of Listing 1 tells the compiler that this is a mapping to an object in a database. The class itself is declared as partial so that you can optionally extend it in your own source files. You should not try to change the code in the generated source as you may need to regenerate it if the objects in your database change or if you decide to add or remove objects in the designer.
The code generated by the designer allows the compiler and Visual Studio to provide type checking and IntelliSense when you are writing LINQ queries. It makes the structure of the tables and rows in the database visible to you. It is like a clear window allowing C# developers to see and work with the objects in the database. You are not yet seeing the values in the tables, but you can see the structure of the tables.
The Data Context
The LINQ to SQL Designer provides a mapping between the objects in your database and classes in your code. However the designer also creates a handy class derived from DataContext which helps you automatically connect to the database, and also gives you a simple means of accessing the Customer class.
Listing 2: The DataContext descendant generated by the Linq to SQL Designer.
1: public partial class DataClasses1DataContext : global::System.Data.Linq.DataContext {
2:
3: [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
4: public DataClasses1DataContext(string connection) :
5: base(connection) {
6: }
7:
8: [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
9: public DataClasses1DataContext(global::System.Data.IDbConnection connection) :
10: base(connection) {
11: }
12:
13: [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
14: public DataClasses1DataContext() :
15: base(global::ConsoleApplication4.Properties.Settings.Default.NorthwindConnectionString) {
16: }
17:
18: public global::System.Data.Linq.Table<Customer> Customers {
19: get {
20: return this.GetTable<Customer>();
21: }
22: }
23: }
The first three methods in DataClasses1DataContext help you connect to the database. Each of these methods are marked with the DebuggerNonUserCodeAttribute, which tells the debugger to ignore the methods. The compiler knows they are there, of course, but the debugger essentially ignores them so as not to clutter up your debugging experience with methods that you don't really need to see. If for some reason you want to step through these constructors while debugging, you can remove that attribute and rerun the program.
The Customers property at the end of the class gives you easy access to the to the Customers table from the database. It exposes this table as a collection of objects. Each object is an individual row encapsulated by the Customer class shown in Listing 1.
By returning an instance of the Table class, the Customers property also provides the following services:
- It supports the IQueryable<T> and IEnumerable<T> interfaces that make LINQ queries possible.
- It provides methods to support adding, deleting and updating rows in the database.
Listing 3 shows the declaration for the Table class. Note that it supports IQueryable<T> and IEnumerable<T> . (Remember that you can read these class as "IQueryable of T" and "IEnumerable of T.") These two interfaces are needed if you want to query an object with LINQ. Note also that there are methods called Add and Remove. These methods make it possible for LINQ to update the database behind the scenes.
Listing 3: The Table class is built into LINQ. It is a helper class that converts a simple class like that shown in Listing 1 and makes it part of a collection of objects representing a table. The table is fully LINQ enabled and supports the ability to modify the rows in the database.
1: public sealed class Table<T> : IQueryable<T>, IEnumerable<T>, ITable, IQueryable, IEnumerable, IListSource
2: {
3: public Table(DataContext context, MetaTable metaTable);
4: public DataContext Context { get; }
5: public bool IsReadOnly { get; }
6: public string Name { get; }
7: public Type RowType { get; }
8: public void Add(T item);
9: public void Attach(T item);
10: public IEnumerator<T> GetEnumerator();
11: public void Remove(T item);
12: public void RemoveAll(IEnumerable<T> items);
13: public override string ToString();
14: }
Querying the Customers Table with LINQ
We have finally covered all the background tasks and information needed to support a LINQ query. If you have been following this series as it develops, you will now find the code for the actual query needed to access the database almost anti-climatic in appearance. This is a good thing, since we want LINQ to be simple and easy to use, and indeed you will find that it easily achieves those goals.
The code is shown in Listing 4. The key lines are found on lines 12, 14 and 15.
Listing 4: The code for running a simple LINQ query.
1: using System;
2: using System.Linq;
3: using System.Collections.Generic;
4: using System.Text;
5:
6: namespace ConsoleApplication4
7: {
8: class Program
9: {
10: static void Main(string[] args)
11: {
12: DataClasses1DataContext dataContext = new DataClasses1DataContext();
13:
14: var query = from c in dataContext.Customers
15: select c;
16:
17: foreach (var q in query)
18: {
19: Console.WriteLine(String.Format("{0}, {1}, {2}", q.CompanyName, q.ContactName, q.City));
20: }
21: }
22: }
23: }
Line 12 shows the code for creating an instance of the DataContext from Listing 2. Needless to say we are using the third constructor shown on lines 13-15 of Listing 2. The actual connection string used to access the database is stored in the Settings.Designer.cs file found under the Properties node in the Solution Explorer.
The DataContext is used again in line 14, when we access the Customers property discussed at length at the end of the previous section of this post. Translated into standard SQL, lines 13 and 14 read something like "Select * from Customers." The exact line of SQL is accessible from the dataContext.Log property. To view it, add the following code on Line 16 of your program:
dataContext.Log = Console.Out;
If you then re-run your program, the following SQL will be printed in the first few lines of text printed to the console:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
[t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
You will probably have to scroll the contents of the console window up to view this query. Remember that you can get a handy view of the console output if you run your program from the IDE by pressing Ctrl + F5.
The code on line 19 prints out a few fields from the results of the query. There are actually tricks and helper classes which make it easy to print out the results of a database query. For now, however, I want to work with this simple means of displaying the results so that what is happening is as obvious and transparent as possible. If you are using Orcas you get both an IntelliSense window and automatic code completion to help you discover the fields of the table exposed in the query. I have chosen to expose just three of the fields, but you can access all eleven fields from the Customers table if you wish.
Summary
This post explains how to write a simple LINQ query expression to pull data from a table stored in Microsoft SQL Server. The majority of the text focused on how to use the LINQ to SQL Designer, and in particular how to understand the code generated by the designer. The most important section of that discussion focused on the Customers property which exposes the Customers table as a collection of Customer objects, where each Customer object represents a single row in the database. The actual code for querying the database turned out to be extremely simple. If you find the query expression shown in lines 14 and 15 mysterious, you might want to reread some of the earlier posts in this series on LINQ.
Comments
Anonymous
February 16, 2007
What kind of extensibility will there be in the LINQ to SQL implementation? So far, nothing I've seen shows that you can use CTE's or Full-Text functions directly from LINQ code. It would be very useful if there was a mechanism where additional functions could be added to expression tree that could add additional SQL calls. Is there any way to do this?Anonymous
February 17, 2007
Pingback: http://oakleafblog.blogspot.com/2007/02/linq-to-sql-c-primer.htmlAnonymous
February 19, 2007
Hi Charlie, In your post you refer to the "March 2007 Orcas CTP" - does this mean there isn't going to be a Orcas CTP in February now? Kind regards, tomAnonymous
February 20, 2007
Tom, The date for the next CTP is a bit of a moving target. At this time it has moved back into February, which means it should be out quite soon. The team is working to get it out as soon as possible, and right now it looks like we will get it out very soon.
- Charlie
Anonymous
February 20, 2007
You've been kicked (a good thing) - Trackback from DotNetKicks.comAnonymous
February 25, 2007
This short video contains the basic information that you need to use LINQ to SQL to connect to a relationalAnonymous
March 01, 2007
Welcome to the twenty-second Community Convergence, the March CTP issue. I'm Charlie Calvert, the C#Anonymous
November 06, 2007
I can connect to and query a database much faster using the SqlClient. It really seems to me that it is a choice of learning LINQ or learning SQL. And I don't think you would be able to get a job knowing LINQ and not knowing SQL. Of course, with all the shops that swarm to every new "simplification" Microsoft does, I suppose knowing LINQ will be a job prerequisite for many shops.Anonymous
July 15, 2008
Lexapro and alcohol. Lexapro withdrawal.Anonymous
July 15, 2008
Valium during pregnancy. What is valium used for. Valium.Anonymous
January 04, 2009
How in the world do you contact in Database Explorer to a SQLExpress Instance.... When I view the advanced properties it is attempting to use .SQLExpress.. I need INSTANCEDBSERVER.. Everyone uses Northwid so examples are of no help.