Is it worth moving from stored procedures to linq ?

Question

I'm looking at standardizing programming in an organisaiton. Half uses stored procedures and the other half Linq. From what i've read there is still some debate going on on this topic.

My concern is that MS is trying to slip in it's own proprietry query language 'linq' to make SQL redundant.

If a few years back microsoft had tried to win customers from oracle and sybase with their MSSQL database and stated that it didn't use SQL by their own proprietry query langues ie linq. I doubt many would have switched. I believe that is exactly what is happening now by introducting it into the applicaiton business layer.

I have used MS for many years but there is one gripe that I have with them and that is that they change their direction a lot. By a lot I mean new releases of .net, silverlight etc are more than 30% different from previous version. So by the time you become productive a new release is on the way.

As things stand now a web developer using .net would need to know either vb.net or c#, xml, xaml,javascript,html, sql and now linq. That doesn't make for good productivity in my books.

My concern is that once we all start using linq MS will start changing it between releases. and it will become an ever changing landscape. I believe that 'linq to sql' has already been deprecated. At leas with SQL we are dealing with a more stable and standardized language.

Are we looking at a programming revolution or a marketing campaign? As far as I know other languages like Cobol have stayed the same for years. A cobol program from 20 years ago could pick up todays code and start working on it. Could a Vb3 person work on a modern .net web app ? Would these large changes need to be made if the underlying original foundation had been sound ? I worry about following MS shaking roadmap with it's deadends and double backs. are there any architects out there who feel the same ?

regards

Josef

Answer 1

I would still use stored procedures where it makes sense. For example, if you need to do some logic while doing changes or to select the correct info, then a stored procedure makes sense.

If you don't want to write SQL then DLINQ or the other frameworks MS has come out with can help.

I find that DLINQ can be handy if I want to just loop through, processing one line at a time, where I don't necessarily need all the lines, or, it can be a way to pull information from a database, combine with information from other sources, and then put that one version into the list.

My point is that both have their strengths and weaknesses, so it is important to decide where each would best be useful.

I don't believe DLINQ is replacing all stored procedures.

Answer 2

LINQ to SQL is not deprecated, and LINQ is not intended to be a replacement for SQL or any language - LINQ simply provides a neat, clean way to encapsulate all the heavy lifting of translating expression trees from native .NET code into their target language.

To your other point - MS does a remarkably good job of keeping releases backward-compatible. Just because a new release includes twice as much new functionality doesn't mean any of your skills on the previous version are obsolete; it takes several releases for skills to become obsolete. But staying up on new evolutions and additions is part of the job. COBOL is a terrible example now because COBOL is a dead language. Back when it was the hot new thing, it probably changed all the time.

Answer 3

Depends what you want.

If you want to make the database side and the Application side distinct, then use stored procedures. The main advantages of them is that you can update the database queries without the need to re-compile your program (very good for short quick fixes).

I know there has been intense debate in the "geek" circles over which is faster, after the longheld belief that Stored Procs ewre faster owing to their caching ability, most evidence now probably suggests that linq and stored procs performance-wise may not be that different.

SQL has indeed been there a long time. I don't it will be possible to completely get rid of it - it'll certainly create some shake-up in the industry if Microsoft finished support on it. Java has also shown interest in this field, so linq would no doubt be popular. But certainly I do anticipate as times goes on, those using stored procedures will slowly decrease in favour of LINQ.

Answer 4

OK, first off, don't forget that LINQ is not an O/R mapper. LINQ stands for Language INtegrated Query, and works on all types of objects, from XML files (System.Xml.Linq) to plain old arrays and objects (System.Linq), to databases (System.Data.Linq) to high level entities. LINQ is here to stay. It's going to be extended, but you will be hard pressed to find any major breaking changes in LINQ itself in the coming years.

Now, you may be thinking of Linq to SQL (formerly DLINQ) vs. Stored Procedures. My view is this: If you're using stored procedures to do CRUD operations, you're wasting a LOT of time. Yes, Linq to SQL is intended to be replaced by the next version of LINQ to Entities when Visual Studio 2010 ships, but that upgrade path won't be too bad. LINQ itself is not changing, just the way you make your entity mappings.

LINQ to SQL and LINQ to Entities solve big problems that you used to have with O/R mappers. It was tough to get sets of objects out of the database in an efficent way. Either you had to go row-by-agonizing-row to make your objects or you had to denormalize the hell out of the data and reassemble it in code in order to make your relational objects look semi-usable. Does that mean that Stored Procedures have lost all of their use? Of course not. If you're doing very large set-based updates or inserts, stored procedures are still the way to go, because you don't have to marshal your data between the database and your middle tier in order to just write it all back. In cases like these, stored procedures are faster and scale better. But 80 to 90 percent of the time, I find that most queries are far simpler than that. Creating a stored procedure for every operation is painful in the extreme.

As for your comparison with COBOL, that's partially true. Most old COBOL programmers could recognize "modern" COBOL fairly easily. But then again, most COBOL programs don't deal with graphics or multimedia, or even a lot of networking. These technologies are more well suited to other languages. But take as a counter-example, C++. IMO C++ is barely recognizable from what it was just ten years ago, when every Tom, Dick, and Harry had their own string library. The bad old days indeed.

It is true that Microsoft has had some missteps and false starts, but they have been fewer and farther between since .NET has come out. .NET is here to stay. LINQ is here to stay. It's a truly huge innovation, and it's only a matter of time before it is copied in more languages.