software solutions / project leadership / agile coaching and training

LINQ to SQL: a three-month checkpoint

Posted on January 19, 2008

We are now about 3 months into our project using LINQ to SQL. Our project is a Winforms app using SQL Server 2005 (LINQ to SQL only works with SQL Server). We are planning on moving to an n-tier system with a WCF service layer, but for now our application talks directly to the database. Even though we don’t have the service layer in there yet, we’re architecting the system as if we had the service layer in there so we’re having many of the same issues that we will have when actually have the service layer.

Microsoft hasn’t always been known for stellar 1.0 releases (e.g. Vista, the Zune, etc.). When it comes to something that’s in the .NET Framework, I had a little more faith because it’s a little harder for them to go back and fix something if they screw it up. I figured that because of that, they’ll make sure that they get it right.

LINQ to SQL is not complete. There are some issues that Microsoft knows about that LINQ to SQL doesn’t currently handle. None of these issues are show-stoppers. While we’ve had to jump through some hoops to get around these issues, but we’ve been able to do everything that we’ve needed to do. I’ll get into more detail on the hoop-jumping in later posts.

Even with all of these issues, I give LINQ to SQL a rousing endorsement. I’ve always been an ORM fan, and I’ve used Nhibernate on several projects.

Getting Started

When we started our project, we inherited a legacy database that has been developed over the last 10 years. There are some interesting things in the database, such as numerics being used as primary keys, tables that aren’t normalized, and spotty referential integrity.

For the first week, three of us dragged all of the tables onto the LINQ to SQL designer and renamed all the properties to more friendly names. This was a fairly painless process. Now we had all of our entity objects created and ready to go.

Well, almost. We created a BusinessEntityBase class and all of the entity objects derive from this class. We do this by creating partial classes that match up with the classes generated by LINQ to SQL (all of the classes generated by LINQ to SQL are partial classes) and specifying that those classes derive from BusinessEntityBase. We don’t have much in the BusinessEntityBase class — the main thing in there is an abstract Id property that each entity must override to specify the value of the primary key. We use this to keep track of whether an entity object is unsaved or not.

At this point, we were ready to start working! All of our entity objects were generated for us. Contrast this with Nhibernate, where we had to write (or generate) all of our entity objects and the Nhibernate mapping files. It takes most people a long time to figure out how to write those Nhibernate mapping files!

Working with LINQ

“LINQ” is the general term for the syntax that we now use to write queries. These queries can be executed against a database (LINQ to SQL), a collection (LINQ to Objects), and various other things (LINQ to Amazon).

The LINQ syntax and particularly lambda expressions were very foreign concepts at first. You’re just not used to using those types of things in C# code. Then one day is just clicks, and you start discovering all kinds of new ways to use LINQ queries and lambda expressions.

Personally, I think lambda expressions are more revolutionary than the LINQ syntax. They don’t provide you with anything that you couldn’t do in .NET 2.0 with anonymous delegates, but now the syntax is much more concise. You can do what you want to do in fewer lines of code, which also makes for more readable code. Here’s an example of why I like lambda expressions.

Let’s say that I’m working with everyone’s favorite sample database (Northwind) and I want to find a Employee by first name, last name, or both. In the past, you probably wrote a stored procedure that looked like this:

create procedure EmployeeSearch
     @FirstName varchar(20),
     @LastName varchar(20)
select EmployeeID, FirstName, LastName, Title, TitleOfCourtesy, 
    BirthDate, HireDate, Address, City, Region, PostalCode, Country, 
    HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath
from Employees
where (@FirstName is null or FirstName = @FirstName)
and (@LastName is null or LastName = @LastName)

That worked fine, but having to check if the parameters are null is a performance hit in the stored procedure, and someone had to write the stored procedure in the first place.

With lambda expressions and LINQ to SQL, you can now do something like this and build your query incrementally:

public IQueryable SearchEmployees(string firstName, string lastName)
    NorthwindDataContext dc = new NorthwindDataContext();

    // We'll start with the entire list of employees.
    IQueryable employees = dc.Employees;

    if (!string.IsNullOrEmpty(firstName))
        // Filter the employees by first name
        employees = employees.Where(e => e.FirstName == firstName);

    if (!string.IsNullOrEmpty(lastName))
        // Filter the employees by last name
        employees = employees.Where(e => e.LastName == lastName);

    return employees;

Why is this better?

  • We didn’t have to write a separate stored procedure.
  • The generated SQL code won’t have to check for NULL parameters passed into a stored procedure.
  • This code is much more testable and easier to read than a stored procedure (IMO).
  • This is compiled, type safe code!

Here is the SQL code that LINQ to SQL runs as a result of this method:

SELECT [t0].[EmployeeID], [t0].[LastName], [t0].[FirstName], [t0].[Title], [t0].[TitleOfCourtesy], [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[ReportsTo], [t0].[PhotoPath]
FROM [dbo].[Employees] AS [t0]
WHERE [t0].[LastName] = @p0
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Kruger]

I’m not saying that stored procedures are obsolete. There will still be cases where you have a query that is so complex that it’s easier to do it in a stored procedure, or it may not be possible to do it in LINQ at all. But LINQ to SQL is allowing me to scrap many of the stored procedures that I used in the past.

More to come…

Over the next few weeks, I’ll post in more detail about how we are using LINQ to SQL and some of the things we’ve had to do to make it work.


  1. Hi,

    How can you obtain the SQL generated code by Linq ?

    Perharps it is obvious but .. I am new with Linq to SQL.



    dominique — February 1, 2008 @ 1:52 pm

  2. Dominique –

    Check out this post on how you can have all of the SQL executed by LINQ to SQL in the output window in Visual Studio. This is what I do on my project and it works great.

    LINQPad is also a handy tool for this sort of thing… think of it as a lightweight version of SQL Server Management Studio for LINQ to SQL.

    Jon Kruger — February 3, 2008 @ 5:01 pm

  3. Thanks Jon, I tried the code in your link, I translated it to VB and it is fine.

    In MSDN Library, I found 2 another solutions (easier) :

    Dim db As New NorthwindDataContext

    Console.WriteLine(“solution 2 : msdn”)
    db.Log = Console.Out

    Dim q = From c In db.Customers _
    From o In db.Orders() _
    Where (c.CustomerID = o.CustomerID) _
    And c.Country = “France” _
    Order By c.CustomerID _
    Select o.OrderID, o.ShipCity, c.CustomerID

    DataGridView1.DataSource = q

    Console.WriteLine(“solution 3 : msdn”)
    Dim dc As Common.DbCommand = db.GetCommand(q)
    Console.WriteLine(vbNewLine & “Command Text: ” & vbNewLine & dc.CommandText)

    These 3 solutions run only with Linq (use db.Log).

    The syntax of the second solution seems strange to me :
    db.Log = Console.Out
    In fact, we send to the console the result of the log of db, I think that :
    Console.Out = db.Log
    would be more logical

    I think it is also possible to do that with SQL Profiler but I don’t know how to use it.

    dominique — February 4, 2008 @ 4:19 pm

  4. I am using linq 2 sql store procedures . I didn’t change the return type of any store procedure so it is returning isingleResult
    I make a class ”mydataconetcion” which use the datacontext class object and I am getting all store procedures in that class by
    Datacontext db=new datacontext();
    One example of my store procedure is
    public ISingleResult getCategoryManufecturer(int catId)
    ISingleResult CategoryManufecture = db.dashCommerce_Store_FetchCategoryManufacturers(catId);
    return CategoryManufecture;
    In my web pages I make object of mydataconetcion class
    Mydataconetcion dc=new mydataconetcion();
    I am storing the result in var then by foreach I am traversing it . or I simply bind it to my gridview and repetors and so on .
    I am doin this
    Var selectedProduct =dc. getCategoryManufecturer(7);
    CatGridview.datasource= selectedProduct;

    It works fine but now I want something like this
    If (selectedProduct!=null )
    CatGridview.datasource= selectedProduct;

    Or empty or what ever condition that tell me that there is any value in selectedProduct
    So help me out on this how can I make sure that there is any value or not

    Ahtesham — May 22, 2008 @ 2:07 am

  5. This post was written a while ago, so hopefully you already know this, but you can use SQL Metal (a command line tool that comes with VS 2008 and that I think the LINQ to SQL visual designer uses under the covers) to generate your class files and/or mapping files. If you need to generate classes for a database with a large number of tables it is MUCH easier and faster than dragging all the tables onto the visual designer (IMO).

    Jon — June 3, 2008 @ 5:26 pm

Leave a comment

I have over 15 years of software development experience on several different platforms (.NET, Ruby, JavaScript, SQL Server, and more). I recognize that software is expensive, so I'm always trying to find ways to speed up the software development process, but at the same time remembering that high quality is essential to building software that stands the test of time.
I have experience leading and architecting large Agile software projects and coordinating all aspects of a project's lifecycle. Whether you're looking for technical expertise or someone to lead all aspects of an Agile project, I have proven experience from multiple projects in different environments that can help make your project a success.
Every team and every situation is different, and I believe that processes and tools should be applied with common sense. I've spent the last 10+ years working on projects using Agile and Lean concepts in many different environments, both in leadership roles and as a practitioner doing the work. I can help you develop a process that works best in your organization, not just apply a prescriptive process.
Have any questions? Contact me for more information.
Ditching the Office - How an everyday corporate development team turned into a remote working team
From Stir Trek 2018
From Stir Trek 2017, cbus.js 2017
Iteration Management - Your Key to Predictable Delivery
From Stir Trek 2016 and QA or the Highway 2015
From CodeMash 2016, QA or the Highway 2014, Stir Trek 2012
The Business of You: 10 Steps For Running Your Career Like a Business
From CodeMash 2015, Stir Trek 2014, CONDG 2012
From Stir Trek 2013, DogFoodCon 2013
(presented with Brandon Childers, Chris Hoover, Laurel Odronic, and Lan Bloch from IGS Energy) from Path to Agility 2012
From CodeMash 2012 and 2013
(presented with Paul Bahler and Kevin Chivington from IGS Energy)
From CodeMash 2011
An idea of how to make JavaScript testable, presented at Stir Trek 2011. The world of JavaScript frameworks has changed greatly since then, but I still agree with the concepts.
A description of how test-driven development works along with some hands-on examples.
From CodeMash 2010
From CodeMash 2010