Demystifying DLINQ: Part 4 - Stored Procedure/UDF Support in DLINQ.

Posted on 6/30/2006 @ 8:58 PM in #Vanilla .NET by | Feedback | 7589 views

Stored Procedure/UDF Support in DLINQ.


Sorry for the long gap in blogposts, I was at TechED 2006 @ Boston, MA. As Kimberly Tripp mentioned, TechED was a monster conference. I had the pleasure of shaking hands with her and introducing myself – she is really nice. I also had the pleasure of chit chatting at length with Kent Tegels, and Bob Beauchemin – both awesome folks. I also had the good luck chance of meeting up with Scott Guthrie, he showed BLINQ on one of his 3 laptops , what an incredible guy frankly. I met an incredibly long list of people, everyone better than the other.

Perhaps the most memorable moment was on Thursday Morning, 3 AM, when the fire alarm went off at the speaker hotel. I was in deep sleep, and

My first impression was “Goddamit this better be real”.
My second thought was – “I wish they hadn’t served alcohol at the party tonight”.
My third thought was – “Where is my laptop”.

So we ran out, and the building was lined with ambulances, fire brigades, lots of flashing lights. I immediately noticed two things –

a)      All the ladies without their makeup on. UHHHH what a shock!! Please no more fires!!

b)      All the nerds with their man-purses and their laptops. We left clean underwear up in the hotel room, but by god we carried our laptops outside. J

My two talks went fairly well, also the TechED Birds of Feather was a lot of fun. We had awesome participation, and fantastic representation from the Microsoft folks. Both Bob Beauchemin, and myself were fighting to prevent the crappy microphones from creating annoying feedback, but it was all worth it. The only regret I have is that someone ate my chicken while I was moderating with the microphone. You see, I do like chicken wings!! J

A major part of the discussion was focused on Stored Procedures/UDFs. So I figured, a blogpost elucidating stored proc/UDF support in DLINQ was in order (applause).

So here is something to consider – there are two seemingly contradictory goals.

a)      Anything new that comes out must support a huge current install base, and the upgrade path should be least painful. It is a fact that a majority of applications use stored procedures/UDFs as of today, it is also a fact that a number of developers and DBAs prefer to use stored procedures UDFs for various reasons.

b)      Stored procedures/UDFs are the biggest pain in the rear for anything that resembles an ORM, or anything that alleviates the need for an end developer, or even DBA to write SQL Queries by hand, embedded as strings.

So it is not a shock that stored procedures/UDFs are supported in DLINQ. But then it is also true that a simple DLINQ query, out of the box, will prefer to use dynamic SQL over stored procedures/UDFs.  But you do have enough extensibility points within the framework, allowing you to use your existing or future stored procedure base.

And a word of practical advice, and purely personal point of view: Wean your development and DBAs off the stored procedure crack. You are severely restricting your productivity by being stuck in a stored procedure world.

Okay good, so coming to stored procedures/UDFs, here is how they are supported in DLINQ:

In very short, any stored procedure whose meta data can be accurately determined by the runtime at design time, will be supported. This means, anything that uses temp-tables directly, or does something funky like string concatenation and then execute sp_executesql inside – anything that prevents the DLINQ runtime from accurately extracting the metadata, thus preventing strongly typed support, will not be supported (and I think that is a very reasonable limitation).

As usual, I am going to work with the ever-favorite Northwind database to demonstrate stored procedure support. The good news is, you can easily extract stored procedures in your strongly typed DataContext by passing in the /sprocs command line switch to SqlMetal. This can be done easily by executing the following at commandline –

"c:\Program Files\LINQ Preview\Bin\SqlMetal.exe" /server:. /database:Northwind /pluralize /sprocs /code:Northwind.cs

But before you execute the above, let us enumerate the various funky cases you may be faced with when working with stored procedures, and setup the requisite stored procedures up first.

a)      You may have a stored procedure that returns a scalar result.

b)      You may have a stored procedure that returns a definite shape, i.e. rows and columns, or sets or rows and columns that do not change as the input parameters change.

c)       You may have a stored procedure that returns variable shapes, i.e. depending upon the inputs, the output shape, rows and columns, can change. Note that DLINQ still needs to figure out, at design time, the exact definite set of all possible return shapes. sp_executesql and concatenated strings producing dynamic SQL won’t be supported by this scenario, because a definite set of all possible return shapes is not determinable at design time.

d)      You may have a stored procedure that returns various shapes – all in the same resultset, but in a definite order, sequentially.

e)      A stored procedure with output scalar parameters.

f)       A stored procedure that returns a resultset, and has output scalar parameters.

In subsequent blogposts, I will be covering each one of these cases, before discussing UDF support in DLINQ. So tighten your seatbelts, and enjoy the ride J.


Sound off but keep it civil:

Older comments..

On 11/16/2006 5:55:49 PM Mohammed Hossam said ..
Thanks for this nice blog post, I am preparing for a session about LINQ and C# 3.0,, you helped a lot.

Thanks agian,


On 11/16/2006 7:31:02 PM Sahil Malik said ..
Glad you liked it

On 11/18/2006 12:28:28 PM Bob Reselman said ..
In the experience of my DB brethren, stored procedures are almost required to query very large datastores, at the 500 GB to 1 TB level in a time efficient manner. Tell me please, how will LINQ address the performance advantage the stored procs offer?

On 11/18/2006 9:17:51 PM Sahil Malik said ..
Bob -

In certain situations Stored procs DO present advantages over dynamic SQL. Though the situations are borderline. So I disagree that stored procedures are almost always required. In fact, I'd insist that you can almost always do away with stored procedures. Though there are borderline situations.

One situation which is a bit more than a borderline is - existing installbase, permissions and DBA mentality.

Thus it is quite reasonable to assume that future versions of ADO.NET vNext will indeed have s.proc support. DLINQ in it's current incarnation has good sproc support (I blogged about it too).


On 12/17/2007 11:41:07 AM Paul Matthews said ..
After reading your blog, you stated
"And a word of practical advice, and purely personal point of view: Wean your development and DBAs off the stored procedure crack. You are severely restricting your productivity by being stuck in a stored procedure world."

Isn't this a security issue? If I allow read/write access to my tables, instead of just execute access to my Stored Procs, wouldn't this be easier for someone to SQL inject my database. (Assuming I left a hole in my application).

LINQ looks great, and I would love to get on board, but it seems I'm gonna have a security issue with the business.

On 12/17/2007 6:02:59 PM Sahil Malik said ..
Paul - yes security has been implemented in the past via a layer of stored procs. But that isn't the only way to implement security. Plus, think about it, when you leave home, do you lock the front door? I guess you do. But do you also lock the kitchen, the living room, the .. etc. etc.? I guess security is an important concern, but it is an overbeaten dead donkey .. just look at Vista. Too much security headiness is the antithesis of productivity and convenience :).


On 11/14/2008 1:27:47 AM Po said ..
thats an issue of defense in depth which everyone would agree is safe practice, especially in a client-server topology. Microsoft went overboard with Vista but that is an extreme example. I'd have to respectfully disagree with your dismissal of the benefits of stored procedures. Plus, if Linq supports procs, I don't see what the issue is.