Microsoft has created a wonderful technology called “Language Integrated Query” or LINQ to provide an object-oriented front end to access and update data on a SQL Server or in an XML file. In these notes, we will focus on the SQL situation.
To support the mapping of SQL data, structure, and
behavior to an object-oriented set of classes,
Microsoft provides a designer tool that is reached
by creating a New Item in
using the dialog choice:
LINQ to SQL Classes
Once you choose a
name for the
designer file, Visual Studio will create the file:
This file contains the design decisions. Once this file is saved, Visual Studio creates two additional files:
dbml.layout file captures the visual
representation (boxes and arrows) of the designer
designer.cs file contains the
auto-generated C# code for the LINQ to SQL mapping.
This is the file that must be deployed to the server.
The first two files may be deployed but they will
only be of interest in Source or Statistics Server.
It turns out that if you use the designer in a naive
way then it has some annoying defaults. My thanks
to Armin Galliker who pointed out that if the
Properties of the designer
are set before beginning a design then the
defaults may be overridden to great benefit.
Prior to knowing this, I was manually editing the auto-generated C# code to fix the problems with the defaults. Now I no longer need to engage in such a problematic software engineering practice.
Step Zero for the LINQ to SQL Designer
We insert here a link to a story with snapshots for the steps needed to use the LINQ to SQL Designer in Visual Studio 2010.
We strongly urge you to look at this story before continuing with the prose below.
The First Step in the LINQ to SQL Designer
Names and Namespaces
It is important that the name given to the
file strongly suggest what data the file will manage.
On my site, I have a file
pubs.dbml that manages
the tables, views, and stored procedures for the pubs
For the Northwind database, I have a file
Northwind.dbml that mananges the tables, views,
and stored procedures in the Northwind database.
In my own
rasala database, I have two independent
tables so I have two separate
Browsers.dbmlthat mananges the tiny
Browserstable with user browser preferences.
US_State.dbmlthat mananges the small
US_Statetable with state and population data.
In each case, I use the file name to strongly suggest what data the file will manage. I urge you to use similar care in selecting the file name.
By default, when a
dbml file is created, it does not
protect the auto-generated C# code with a namespace. This is very
unfortunate because if two databases have tables with the same name
a name conflict will be created in the auto-generated code. This
error will prevent your entire site from compiling.
Thus, it is quite important to supply a namespace when creating a
We recommend that the namespace for a
dbml file be set
up with the following convention:
username is your CCIS username and
filename is the name used on the
dbml file. This convention will prevent conflicts both
within your site and across sites if your code is used by another
On my site, this convention leads to the following namespaces:
As you can see, this makes it easy to remember the relationship
between a database or table, its
dbml file, and
its associated namespace.
The Connection String used in the LINQ to SQL Designer
By default, the LINQ to SQL designer auto-generates a connection
string to the database being used in the designer. In an act
that is even more evil, the designer inserts this string into
~/web.config with no notification. This interferes
with maintaining the parallelism of the various versions of the
web.config needed for the remote machine, the CCIS
lab, and the IIS Server.
The creation of the auto-generated connection string will happen the moment you transfer the first bit of information from the SQL database to the designer. Therefore, if you are going to stop this action, you must act at the very start of using the designer.
Properties as the First Step
The first step in working with a new
dbml is to set
the critical properties.
Absolutely do not touch the database or its information before setting the critical properties.
Here is how to set the critical properties.
Open the Properties Window. This may be available as a tab on the right side of the Visual Studio window. If not, use the menu sequence:
> Properties Window
The next actions are best explained with a snapshot taken from the Properties Window of the pubs.dbml file.
You can see that there are 2 namespace settings but I take the simple path and make both settings the same following my naming conventions:
When the Properties Window opens, the Connection field
is blank. If it remains blank as database information is added, then
a new connection string will be auto-generated and immediatly added
both to this field and to
~/web.config. Hence to avoid
an auto-generated connection string, this field must be set.
It turns out that the field is actually a dropdown list that will show
all of the connection strings installed in
Since I already had prepared a connection string for pubs,
pubsCS, I can select this and I am set to go. No
auto-generated connection string will be created and
~/web.config will remain unmodified.
Once these 3 property settings are done, you may move on to acquiring the desired database information.
Acquisition of Database Information
The LINQ to SQL designer provides a drag-and-drop interface to permit a user to specify what tables, views, and stored procedures will be imported into the designer. To be precise, actual data is not imported, only table structure, relations, definitions, etc.
To use the drag-and-drop interface, one must set up a connection to the database in Server Explorer. There may be a tab for this on the right side of the Visual Studio window. If not, use:
> Server Explorer
A snapshot of the Server Explorer with the connection to the pubs database open is shown below:
To establish such a connection, you must have the
tunnel active. Right-click on Data Connections and select
Add Connection.... Then provide credentials as in the
Accessing SQL. You
should save your password if this is happening on your own machine.
Then select or enter the database name and click OK.
The database connection will be stored with your web site project in Visual Studio so it will be available in the future as needed.
For the pubs database, I decided to import everything: tables, views, and stored procedures. Another option I might have chosen is to import just all tables. If you want to import finer subsets, you should examine the table structures to make sure you are not missing tables whose presence is needed for other tables. I find it easier to “import all”.
The actual import of tables, views, and stored procedures is trivial.
- Select all tables and drag them to the left pane of the design surface.
- Select all views and drag them to the left pane of the design surface.
- Select all stored procedures and drag them to the right pane of the design surface.
After each drag, allow the designer a few seconds to create the visual feedback of boxes and arrows or procedure calls.
Here is a snapshot of the left pane of the design surface
Here is a snapshot of the right pane of the design surface
The US_State Example
There is a very simple example on the Rasala site that uses one table with data about the 50 US states plus Washington DC and Puerto Rico. This table has 3 columns: Abbr, Name, and Population. The data is old but whatever. Here is a snapshot of the table in Server Explorer.
Here is the link to the simple LINQ based web page that uses this data:
Here is the link to the source of this page:
Here are the links to the associated files in
We strongly suggest that you play with the state application until you are sure how it works. Then you can fruitfully examine the source files to learn why it works.
We will simply make some remarks to highlight important points.
The 4 columns in the state application are all sortable. The only work that needs to be done is in the markup:
AllowSorting="True"in the header of the
The choice of a
SortExpressionfor each column.
Note that columns 3 and 4 use the same
Population. This is perfectly fine. Note also that there is no C# script. The LINQ to SQL classes together with
GridViewhandle sorting automatically.
In the markup for the
LinqDataSource, one must explicitly give the full type for
Unfortunately, using a namespace does not apply to the markup side of ASP.NET.
US_State.designer.csis exactly the file that is auto-generated by
US_State.dbml. Note that is has the correct namespace and correct connection string
rasalaCS. The connection string always refers to the containing database not to any individual table.
US_State.designer.extra.csis created by hand to add the properties needed to show the population bars and to tweak one definition. This file makes use of a wonderful C# feature: partial classes. Partial classes allow one to add to the definition of a class in a separate file. The main definitions are given in
US_State.designer.csand are left alone. The extra definitions are placed in the new file.
The main extras in
US_State.designer.extra.csare the properties to define the population bar image tag, its url, and its alt tag. Fortunately, the
asp:ImageFieldcan make use of the url to enable the bar images to be placed in the
GridView. It is gap in the design of
asp:ImageFieldthat the alt tag cannot actually be set dynamically and that a title tag (for tool tips) cannot even be set.
- The LINQ to SQL designer has a practice of appending s to a table name to create its plural. Unfortunately, Data is plural so adding the s is stupid. The tweak in the extra file is to permit the use of DataTable in place of the awkward Datas.
This LINQ to SQL code was based on an earlier example that explored the use of text files, SQL databases, and XML files to provide data for assorted markup objects. The sample site is:
Here is the link to the source of this page:
Here are the links to the associated files in
You should be able to see the relationship of these earlier files to the code utilized with LINQ to SQL.
Other files related to the 13 earlier experiments may be found
Finally, the bars are created with the