LINQ-to-SQL Best Practices

LINQ-to-SQL is a Microsoft Object Relational Mapper tool released part of Microsoft.NET 3.5 Framework, C# 3.0, and Visual Studio 2008.

There are rumors that ADO.NET Entity Framework will destroy LINQ-to-SQL but I think otherwise. I am sure Microsoft will try their best to preserve this wonderful light weight ORM tool. It’s been already successful among the developer communities. Practically LINQ-to-SQL is best suited for the light weight medium size applications to access SQL Server data. Entity Framework is more suited to the complex enterprise level systems.

Here are some of the LINQ-to-SQL practices I have applied in my recently architected system and I wanted to share with the general community.

  • Use the LINQ-to-SQL to write SQL queries to support the language compiler, visual studio intellisense support on the database lanaguage.
  • Use the auto generated DBML file. Do not customize the dbml file. To customize the data layer for validation rules, use Partial Classes and Partial Methods and save the class in the Data Access Layer.
  • In the large enterprise application, you can use the stored procedures for CRUD logic but try to use the LINQ-to-SQL for uniform data access logic in small to medium size applications to avoid multiple SQL code bases.
  • Use the SingleOrDefault instead of Single method to retrieve single item from the LINQ-to-SQL data collection.
  • Wrap the LINQ-to-SQL DataContext.SubmitChanges method with Try-Catch block during update/insert/delete code to check for ChangeConflictException
  • Use the Repository patten to encapsulate the persistence and retrieval logic and abstract the DataContext implementation.
  • Use the “Using” keyword to instantiate the DBML data context object to assist the .NET garbage collector – A classic C# practice.

Some of the resources on the LINQ-to-SQL

Scott Guthrie’s LINQ-to-SQL Series – http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

MSDN – 101 LINQ Samples in C# – http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

MSDN – LINQ-to-SQL Samples in VB.NET – http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

Using Linq To SQL with ObjectDataSource Control – http://weblogs.asp.net/zeeshanhirani/archive/2008/05/05/using-linq-to-sql-with-objectdatasource-control.aspx

CodePlex – Multi-tier Architecture for LINQ-to-SQL – http://www.codeplex.com/MultiTierLinqToSql

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s