The LINQ Dilemma

Let me start off by saying that I'm a huge fan of Language Integrated Query (LINQ).  If you've ever had to write code to loop through objects to locate properties and then filter the collection based upon specific values then you'll definitely have an appreciation for what LINQ has to offer.  It's a phenomenal technology that will definitely make developing applications a more productive process.  Scott Guthrie has some really nice tutorials that help jumpstart learning more about LINQ and LINQ to SQL that you can view here.  For those that haven't seen many LINQ samples, here's a simple example of using LINQ to filter a collection of Customer types based upon a Country property:

List<Customer> custs = GetCustomers();
var filteredCusts = for c in custs
                           where c.Country == "USA"
                           select new {CustomerID = c.CustomerID, Name = c.ContactName, 
                                             Country = c.Country};

In addition to querying objects, LINQ can also be combined with other technologies such as SQL and ADO.NET (XML support is also included) to query databases and automatically fill collections without manually writting O/R mapping code.  This is a great feature and a big time saver.  However, it makes it really easy for developers to embed SQL logic directly into application classes without necessarily realizing it.  This leads to the LINQ dilemma....

Inline LINQ/SQL Versus Stored Procedures

If you've been developing for very long then you've certainly heard the old recommendation of "use stored procedures when calling a database".  There's a lot of wisdom to this recommendation since it promotes more fine-grained security, better re-use, and simpler maintenance as compared to embedding SQL directly in code.  It also lends itself well to schema modifications since changes can quickly be made to tables, views, triggers and stored procedures directly within a database without even touching any application code potentially (depending upon the changes of course). 

With the LINQ to SQL designer in Visual Studio 2008 you can use stored procedures (Scott demonstrates this in a few of the tutorials mentioned earlier), but many of the demos that you'll find out there covering LINQ to SQL perform LINQ queries that generate dynamic SQL directly within .NET classes.  Is that bad?  It depends on who you talk to.  Experienced developers will weigh their options here, but those who are newer to the world of LINQ to SQL may not consider the ramifications of embedding LINQ code to query databases directly in their application classes.

I personally think that embedding LINQ/SQL logic directly into classes will lead to maintenance nightmares down the road especially in large applications since any changes to a database now require not only updating the database tables, views, triggers, etc., but also locating the related C# or VB.NET code that also needs to be changed (similar to what happens with embedded SQL code now).  In enterprise situations there may be multiple applications that need to be modified.

Security is also a potential issue since instead of being able to lock-down specific stored procedures to defined users and roles, more generic queries against source tables are made (views can certainly be used as well though).  In talking with several DBAs about this at the DevConnections conference in Las Vegas, they're certainly not supportive of moving query code out of the database for security and maintenance reasons.  Some of them just didn't want to give up that type of control, but the majority just thought it was a bad idea from a security and maintenance standpoint. 

I think that adding LINQ to SQL code directly into application classes is extremely productive.  It's pretty amazing how quickly and easily you can access data.  However, I'm still on the stored procedures bandwagon when using LINQ to SQL because I'm sold on the security, re-use and maintenance story that goes along with them.  However, in talking with several other developers (some who are very experienced) they don't necessarily agree with this view and will be putting their LINQ/SQL queries directly within code since it's simply more productive.  If you have an opinion here I'd be interested in hearing it since I think this "LINQ dilemma" will certainly stir up a lot of discussions as .NET 3.5 is released.

 

Translate:

comments powered by Disqus

5 Comments

  • I like the benefits of direct LINQ queries too but still will use stored procedures in larger enterprise scenarios. But I'm not ruling out using LINQ queries for prototypes and small scale apps or scenarios where the data will always be tightly integrated with just that application.

    It definitely entrenches your applications more in the .NET world by using embedding LINQ queries versus database queries...hard to say what the long term prospects look like for any new / emerging technology....SQL stored procedures definitely have proven longevity behind them.

    This technology will likely impact new projects more than existing. It's worth considering the additional ramp up needed to learn how LINQ behaves - the syntax is quite SQL like but the behaviour not necessarily intuitive without testing - so another layer on top of SQL to learn just for querying, inserting, updating data.

    I also like stored procs because they are reusable across more than one application...how often do you run into scenarios where multiple applications are working with the same database data, or where database administrators need to maintain certain tables, or where developers that are maintaining an application need to perform periodic ETL type operations to their data store, custom reports, etc. etc. All of these types of scenarios lead me to believe that stored procs are still "better" just because of the additional re-use that you get from them outside of your .NET applications plus the self describing nature of stored procedures that makes those types of scenarios a lot easier in many cases.

    I would definitely consider using LINQ but in enterprise applications I'd likely be calling stored procs. That's my 2 second brain blurb at least...it's an interesting technology

  • I'm with you....thanks for adding your opinion. It really depends on the application, requirements, size, time constraints, etc. (which are all unique of course), but having worked in several enterprise scenarios where a database may be used by multiple apps, having re-useable stored procedures that you know work properly is sure nice.

  • Nick,

    Thanks for adding your comments. I definitely agree that LINQ is simply a new tool for us to use and it's very productive whether or not SQL enters the equation or not. Ultimately it's up to the developer to learn what works best in their situation and company/project. A lot of companies do have DBAs ready to create stored procedures while the others you mention don't. Either way, LINQ to SQL offers a much more productive way to do O/R mapping and once the entity framework is released the story will get even better. I just hope that MS and others will focus on the best practices because a lot of new developers I've worked with in the past seem to take samples at face value and assume that they're "best practice" rather than taking the time to learn about what "best practice" really means.

  • Dan, You hit the nail on the head about the samples. It's been my second biggest complaint (right behind how Delphi's databinding is still ahead of .NETs - I have high hopes that LINQ might solve some of this - we'll see) There are sooo many ways to do things because of all the cool tools we've been provided that figuring out the 'correct' (whatever that means) way to do things is difficult. The included sampels are usually to simplistic to be useful (there's that best use issue). The microsoft asp.net forums are questionable at best (who had the bright idea of rewarding people for their number of posts? cause sooo many useless posts - I just stay away) I get most of my info from books and blogs - which are also a bit hit or miss. Anyway, off to play with 2008!!!!

  • I think the LINQ is great in two scenarios, as my great freinds and you have explained here,i.e.
    1. During the creation of the smaller portals and smaller applications.

    2. To aviod the loops from the code for the better refactored code when used along with the generics, in short while working with the generic search kind of a work.

    So, you are right i am also the one who agrees that still the stored procedures are the ones which i would prefer.

Comments have been disabled for this content.