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

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

}

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.