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

Artikel
05/24/2010

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.

Related Books

Comments

Anonymous
May 25, 2010
Hasn't Microsoft stopped work on LINQ to SQL? Shouldn't you be using the Entity Framework and LINQ to Entities?
Anonymous
May 25, 2010
The comment has been removed

Freigeben über