LINQ To SQL and Stored Procedures : Visual Studio 2008 and Repository Factory

I forgot to mention that while I was at SQLSaturday in Tampa, Florida I ran into some very passionate database administrators (DBAs). They were more than happy to give me unsolicited opinions on the evils of O/R Mappers and Dynamic SQL as well as the importance of the DBA on development teams.

I wasn't planning on entering a debate on these topics, but more importantly wanted to make sure that they understood that LINQ To SQL supported Stored Procedures. Now if they would have attended my talk on SQL Server Data Access Tips & Techniques, they would have known this :)

LINQ To SQL and Stored Procedures

You can add stored procedures to LINQ To SQL in a few ways, but the easiest way to add stored procedures support to LINQ To SQL is to drop stored procedures from the Server Explorer in Visual Studio 2008 to the LINQ To SQL Designer Surface. One half of the LINQ To SQL Visual Designer is for mapping classes to tables in SQL Server, the other half allows you to drag and drop stored procedures for stored procedure support. Here is an example using the Northwind Database, where we see the Category Entity that maps to the Categories Table as well as several stored procedures that I dragged and dropped onto the Visual Designer:

I talked about the process of adding stored procedures in a previous tutorial:

• Creating Data Access Layer Using LINQ To SQL - Stored Procedures Support
   

Once you add the stored procedures to the LINQ To SQL Visual Designer Surface, you can map the stored procedures to the CRUD operations of the DataContext Class. Normally the DataContext class will dynamic create parameterized SQL for you to do insert, update, delete, etc. of Categories. However, in this case I specified that LINQ To SQL should use the stored procedures I added to the designer surface instead. Here we see me overriding the runtime behavior with a custom behavior to insert a new category using the InsertCategory Stored Procedure:

Now when you do something as simple as inserting a new category into the categories table, you will notice that LINQ To SQL calls a stored procedure as opposed to creating a parameterized query at runtime:

NorthwindDataContext db = new NorthwindDataContext();
db.Log = Console.Out;

Category category = new Category();
category.CategoryName = "Green Tea";
category.Description = "Yummy...";

db.Categories.InsertOnSubmit(category);

db.SubmitChanges();

You can view these results by reviewing the data sent to the DataContext.Log Property or using SQL Server Profiler.

Visual Studio 2008 and Repository Factory

At my presentation, I also received a few questions / concerns about Visual Studio 2008 and the Repository Factory. You can find more information about using the Repository Factory for generating a data access layer here:

• Repository Factory - New and Improved Data Access Guidance Package

I also have two screencasts on the Repository Factory:

• Repository Factory Guidance Package Code Walkthrough Screencast
• Create Data Access Layer Using Repository Factory Guidance Package Screencast

Here is a screencast on the older Data Access Guidance Package that used to ship with the Web Service Software Factory:

• Create Data Access Layer Using Data Access Guidance Package Screencast

Personally, it is my humble opinion that if you are using Visual Studio 2008 to target the .NET 3.5 Framework, I would use LINQ To SQL within my respository classes. This will avoid the fact that there is currently no Visual Studio 2008 version of the Repository Factory Guidance Package, and LINQ To SQL does what the Repository Factory does and then some.

Some reasons for using LINQ To SQL over Repository Factory when targeting .NET 3.5 Framework:

• As mentioned above, all you need to do is drag and drop your stored procedures to the LINQ To SQL Visual Designer Surface to support stored procedures.
• The amount of code to maintain is dramatically reduced as all of those Factory Classes generated by the RepositoryFactory for converting IDataReader to Entity Classes is done for you by LINQ To SQL.
• LINQ To SQL will also detect timestamp columns in your SQL Server tables just like the Repository Factory as well as support other types of concurrency methods.
• LINQ To SQL does change tracking, something the Repository Factory does not do.
• LINQ To SQL supports dynamic SQL, the Repository Factory does not.
• LINQ To SQL can be used with other cool technologies easily like ASP.NET Dynamic Data and ADO.NET Data Services.
• etc...

Don't get me wrong, I appreciated the Repository Factory when we did not have LINQ To SQL and the upcoming ADO.NET Entity Framework. It was an okay interim solution, but now I think it is time to move on, at least when you are targeting the new .NET 3.5 Framework.

Hope this helps.

by David Hayden