|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:
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:
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")]
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;
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)
I have also ran a SQL Express Profiler to observe the SQL Statement issued against the DB":
In the book authors summarize the efficiency of the approach as follows:
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.
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.