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

 



-
Published 19 November 2008 01:00 by simonsabin
Filed under:

Comments

19 November 2008 05:41 by tonyrogerson

# re: LINQ to SQL – Abtsracting your database.

There are many things wrong with LINQ to SQL and here are some of the main points...

1) Because you are not using stored proceudres your security model means you have to give access to the base tables or views which means the login the application is using is free to be used in any other application like excel, access (link tables) which is a very big security problem and lets users write any query they want against your database.

2) Parameterisation in the version that was demo'd by the LINQ product team didn't fully work as was demonstrated in the Profiler output (remember the MVP summit?).

3) The argument you make about late binding and changing parameters sizes - this would all be picked up in testing - you do do testing right Simon? :)

4) The deferred execution is NOT a benefit, its actually a failing; the data may have changed from when you 'think' its been executed to when you actually get the data.

5) This was all done back in the early 90's when I was programming PL/1 on the mainframe against DB2 - it was a very bad idea then that we moved away from, there is no change now.

6) The best approach is yes, use an entity model in your data layer, however - that should be based on a loosely coupled database API - aka stored procedures. That frees the application(s) that are using the database and stored procedures and allows all manner of efficiencies not to mention significantly simpler and more cost effective support (you don't have to get a database guy and an application programmer in when making changes). LINQ to SQL's place is one user applications designed for the desktop - it has no place in anything else.

Tony.

19 November 2008 07:11 by rmaclean

# re: LINQ to SQL – Abtsracting your database.

@tonyrogerson

1) What is wrong with putting security into your views? It is easily done and means you don't need to use stored procs (I have nothing against them, but just trying to understand your point). Also L2S has GREAT support for stored procedures, so you can use them with it.

2) It does work fine

3) Having additional layer of testing, by the compiler at design time, helps. It does not remove the need for testing and I don't think that is what Simon was advocating.

4) So because the developer thinks incorrectly it's the tools fault? Of course it isn't. We need to make sure that deferred execution is highlighted in training and that people understand the issues so that problems don't arise.

5) I never worked with PL/1 or DB2 so can you explain why it was a bad idea. Maybe the reasons it was a bad idea have been resolved. Oddly as you point out in your next says that the best approach is

6)  As pointed out in my first point L2S does support stored procs well and returns a entity model in the data layer. How is this not what you saying you think is the best approach?

19 November 2008 10:25 by tonyrogerson

# re: LINQ to SQL – Abtsracting your database.

(1) Putting security into views does not stop them being used in any other application unless you are using some context sensitive stuff like application name from sysprocesses or something - security in views is not best practice.

(2) no it doesn't, when you have one of the pm's of the LINQ team demo'ing it in front of 70+ SQL MVP's showing the profiler trace only for it to be a hard coded value - you couldn't get much more proof...

(3) Hence my smily face; however - the serious note was that it is far far easier and cost effective to unit test a single stored procedure than have it buried in the application.

(4) The default behaviour is deferred execution which goes against everything people have worked with todate; if deferred execution was the option then my point would be mute.

(5) Embedding SQL ie. not using stored procedures is my point.

(6) ditto (5); and my point is that so long as you encapsulate the data access logic where it should be - inside stored procedures and NOT EMBED sql in the app then using entity modelling is fine. So, we have agreement - use stored procedures only with ORM.

Tony.

# Dew Drop - November 19, 2008 | Alvin Ashcraft's Morning Dew

Pingback from  Dew Drop - November 19, 2008 | Alvin Ashcraft's Morning Dew

19 November 2008 18:37 by Alexander Kuznetsov

# Defensive database programming: fun with column widths.

In Transact SQL you can assign a 10-character value to a VARCHAR(5) variable, as follows: DECLARE @c

19 November 2008 19:34 by Alexander Kuznetsov

# Defensive database programming: fun with changing column widths.

In Transact SQL you can assign a 10-character value to a VARCHAR(5) variable, as follows: DECLARE @c

22 November 2008 04:19 by steve dassin

# re: LINQ to SQL – Abtsracting your database.

@simon says:

"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>."

@tony says:

"This was all done back in the early 90's when I was programming PL/1 on the mainframe against DB2 - it was a very bad idea then that we moved away from, there is no change now."

The former gets 'it' the later doesn't. The sad truth of the matter is that most expert sql programmers don't understand 'what' Linq even is. Sql must render computer science irrelevant.

And without that viewpoint how in hell can one understand what MS is trying to do. How can one understand Linq to Sql without understanding an anonymous type?  Sql users can get along nicely in an essentially type-less world. The impedance mismatch for sql users seems to exist between the ears. The sql community also seems to be uninterested in the 'why' of Linq to Sql

let alone EF. A discussion of what motivated MS to go in this direction remains in the closet

for sql users. They seem so concerned with what's inside sql server they have forgetten

to take a look at what lies outside it. The only thing sql users see is...well sql. They see

the mapping, that be all and end all - ORM. And because mapping has been going on seemingly

forever they infer that stuff like Progress from 15 years ago is essentially the same thing

as Linq to sql. And, of course even if one doesn't know what the big picture is, if it doesn't direcly involve the idea of a stored procedure (and isn't "fast") something is rotten in Denmark. The very criteria that sql users use for criticism are reasons that MS is going to the object model. Unfortuneately the sql community doesn't see the irony. Linq to sql and EF will work and their too important to leave to guess-work. Besides, if sql programmers had a better understanding of what MS is trying to do they would have a better foundation to understand the relational model

(as opposed to sql). They both involve important but basic concepts in computer science.

For a better appreciation of Linq to sql see my comments in the following posts on the sql server MVP site, sqlblog:

'Beyond Relational ???' 10/29/07

by Paul Nielsen

http://tinyurl.com/686z6h

'LINQ - Lessons that could have been learned from languages like Progress' 2/25/08

by Greg Low

http://tinyurl.com/56powf

'PASS presentation: What would you like to hear about?' 2/23/08

by James Luetkehoelter

http://tinyurl.com/5lmj4m

'Continuing Industry Consolidation and Other Trends.' 1/16/08

by Kevin Kline

http://tinyurl.com/5opxlz

12 December 2008 21:59 by SQL Server 2008 (SSQA.net)

# LINQ to SQL - will this be a future of database development?

Since the CTP of SQL Server 2008 there has been much of traffic going around talking LINQ is a replacement