LINQ to SQL – Abtsracting your database.
One of the
arguments often put forward against the use of any (Object Relational Mapping)
ORM, whether LINQ 2 SQL or the others available, is the proliferation of Adhoc
SQL within your application code. (http://sqlblogcasts.com/blogs/simons/archive/2008/11/16/Whats-the-score-with-LINQ-to-SQL-.aspx)
I whole
heartedly agree, however the use of an ORM solution does not mean you have to
have SQL spread all over your code.
One of the
standard best practices is the use of a data layer. This layer provides a
defined set of interfaces to your data.
The argument
is that your application data layer should call stored procedures in your
database.
Thats ok in
that your application has a data layer that has defined interfaces which it
satisfies by getting the data by calling one or more stored procedures. But via
do you need to use stored procedures to achieve this.
Stored
procedures don’t provide a guaranteed interface, they have a defined input but
not a defined output, i.e. the contract is undefined. Its very easy to have some
underlying table changed and your SP is now returning something completely
different.
Further more
in writing SPs, until 2008, you have no intellisense for developing them and you
have to be doing them either in management studio of in a Database Professional
project.
If you use an
ORM solution you will have a representation of the database schema defined in
your application. You will have a set of objects which will be mapped to the
database against which you can make queries.
Your data
access layer can perform these queries just as it would have done using SPs
directly against the database. The benefit is that if you want to change the
structure of your database you can and this will propagate through your code, or
not. The difference with the ORM approach is that if you change a data type,
because you are working with strongly typed interfaces your application won’t
compile until fix your application to use the new types.
You might
think this is a floor. It’s not. I’ve seen many applications where they realise
that a field wasn’t big enough and so increase the field size, however they
don’t propagate the change through the code, i.e.
1.
Parameters in stored
procedures
2.
Related columns in other
tables
3.
Data types in ALL the code paths in the
application
With loose
coupling as in the case of stored procedures you don’t find out until a specific
code path in your application runs at run time. With strong typing you will find
out when you compile your application. (Erland has a suggestion for strict
checks that gets round some of this http://www.sommarskog.se/strict_checks.html , https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=260762 )
Whats more
you can put (note use of the word “put”, stored procedures aren’t compiled until
they are executed) a stored procedure in a database and it can be based on
objects that don’t exist. You won’t find out until the stored procedure is
executed at which time the compilation will fail. With the lack of intellisense
(prior to SQL 2008) that can be a real obstacle to fast development.
So the next
point made is one of compiled query plans. Most ORM solutions use parameterised
SQL that benefits from plan caching in just the same way that stored procedures
do. For this reason the only benefit that stored procedures have over adhoc
paramerised SQL is that the text of an SP name is likely to be shorter than a
SQL statement.
Finally the
use of stored procedures is advocated because they are easy to change. You can
easily pop into your database and make a change. This is very true. However if
you are a developer then you are likely to be more comfortable in visual studio
writing C# or VB.Net in which case writing ALL your data access in your
preferred language in the same solution in visual studio, with intellisense and
compile debugging is likely to result in a much quicker development time, than
if you have to switch between development environments with different
compilation semantics.
Oh and one
more thing about LINQ is that a LINQ query isn’t executed when you create it.
What that means is that you can add to and manipulate a query after you have
first created it by working with IQueryable<T>. This means that you can
return IQueryable<T> from a function and pass that to another function and
manipulate it by adding additional filters, order bys etc and then when you
finally need to you can enumerate over the query or call .ToList(). The benefits
of this are very similar to views but can be taken much much further because you
can define your query structures based on runtime values and not fixed at design
time.
So in summary
if you encapsulate your data access in a data layer using LINQ to SQL it will
provide just the same benefits as using stored procedures but with the benefit
of strong typing, intellisense (for whatever version of SQL) and a single
development environment
-