Alik Levin's

Clarity, Technology, and Solving Problems | PracticeThis.com

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

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

  • Comments 2
 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

  • Hasn't Microsoft stopped work on LINQ to SQL? Shouldn't you be using the Entity Framework and LINQ to Entities?

  • @Terry

    ADO team announced that it recommends using EF vs. L2S but L2S is not going away any soon:

    blogs.msdn.com/.../update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx

    I am just reading a book about LINQ where L2S covered nicely so I thought to give it a try and I loved that - so here in the post I quickly posted a cheat sheet of steps to accomplish L2S simple work  - it is not a recommendation L2S over EF, it's my personal preference and the power of context ;)

Page 1 of 1 (2 items)