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
-
Source Click Here.
Post a Comment