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