Robust, Efficient, & Fast Data Access With LINQ to SQL

  • Article
 Alik Levin    In the post I have quickly captured the steps required to access a database using LINQ to SQL. I am reading a book LINQ in Action – good read, easy and practical. Love it a lot.

Quick Resource Box

General ORM Limitations

In the book the authors specify key limitations of existing ORM [object relational mapping] tools:

“Some of their [ORM tools] main limitations include the following:

  • A good knowledge of the tools is required before being able to use them efficiently and avoid performance issues.
  • Optimal use still requires knowledge of how to work with a relational database.
  • Mapping tools are not always as efficient as handwritten data-access code.
  • Not all the tools come with support for compile-time validation.”

I’d summarize the summary as “ORM usually hit developer’s and/or code’s performance. ”

Accessing Database with LINQ to SQL

Summary of steps:

  • Step 1 – Create entity class
  • Step 2 – Write LINQ to SQL Query
  • Step 3 – Test your code

The following section describes each step in details.

Step 1 – Create entity class

I am using Pet Shop database. I have created a simple ProductInfo entity [Table] class as follows:

[Table(Name = "Product")] public class ProductInfo {     [Column (IsPrimaryKey=true, Name="ProductId")]     public string ID { get; set; }     [Column]     public string Name { get; set; }     [Column (Name="Descn")]     public string Description { get; set; }     [Column (Name="CategoryId")]     public string Category { get; set; } }

Notice the annotations for each property. The annotations actually map the class’ properties to the table’s fields.

Step 2 – Write LINQ to SQL Query

Next is creating the DataContext object – effectively the connection to the database, and then building the query:

DataContext db = new DataContext(\@"Data Source=.\sqlexpress; Initial Catalog=MSPetShop4; Integrated Security=True"); var products=     from product in db.GetTable<ProductInfo>()     where product.Category.Equals("FISH")     select product;

Step 3 – Test your code

To test the code I have dumped the values to the console and received the result:

foreach (ProductInfo product in products) {        Console.WriteLine("NAME {0} DESCRIPTION {1}", product.Name, product.Description);

}

image

I have also ran a SQL Express Profiler to observe the SQL Statement issued against the DB":

exec sp_executesql N'SELECT [t0].[ProductId] AS [ID], [t0].[Name], [t0].[Descn] AS [Description], [t0].[CategoryId] AS [Category] FROM [Product] AS [t0] WHERE [t0].[CategoryId] = \@p0',N'\@p0 nvarchar(4000)',\@p0=N'FISH'

Analysis

In the book authors summarize the efficiency of the approach as follows:

“Let’s sum up what has been done automatically for us by LINQ to SQL:

  • Opening a connection to the database
  • Generating the SQL query
  • Executing the SQL query against the database
  • Creating and filling our objects out of the tabular results”
  • [ALIKL] Closing/Disposing connection to the database

As  performance guy I must also add the LINQ to SQL closes/disposes the connection automatically. In too many cases developers neglect closing/disposing the connection which usually leads to connection leak and as a result to unstable or less than optimal performance.

Conclusion

Seems like LINQ to SQL breaks the limitations I have mentioned in the beginning. For my Solution Architecture For the Masses series I am using old school database approach. I believe since the solution I have build utilizes layered approach and since the layers are abstracted one from another I will be porting the DAL [Data Access Layer] from ADO.NET to LINQ to SQL.

Read the book LINQ in Action.

Related Books