Click here to monitor SSC

Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page. As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management. In his spare time, he enjoys running, football, contemporary fiction and real ale.

Hype and LINQ

Published 2 February 2011 12:18 pm

Tired of querying in antiquated SQL?”

I blinked in astonishment when I saw this headline on the LinqPad site. Warming to its theme, the site suggests that what we need is to “kiss goodbye to SSMS“, and instead use LINQ, a modern query language! Elsewhere, there is an article entitled “Why LINQ beats SQL”.

The designers of LINQ, along with many DBAs, would, I’m sure, cringe with embarrassment at the suggestion that LINQ and SQL are, in any sense, competitive ways of doing the same thing. In fact what LINQ really is, at last, is an efficient, declarative language for C# and VB programmers to access or manipulate data in objects, local data stores, ORMs, web services, data repositories, and, yes, even relational databases.

The fact is that LINQ is essentially declarative programming in a .NET language, and so in many ways encourages developers into a “SQL-like” mindset, even though they are not directly writing SQL. In place of imperative logic and loops, it uses various expressions, operators and declarative logic to build up an “expression tree” describing only what data is required, not the operations to be performed to get it. This expression tree is then parsed by the language compiler, and the result, when used against a relational database, is a SQL string that, while perhaps not always perfect, is often correctly parameterized and certainly no less “optimal” than what is achieved when a developer applies blunt, imperative logic to the SQL language.

From a developer standpoint, it is a mistake to consider LINQ simply as a substitute means of querying SQL Server. The strength of LINQ is that that can be used to access any data source, for which a LINQ provider exists. Microsoft supplies built-in providers to access not just SQL Server, but also XML documents, .NET objects, ADO.NET datasets, and Entity Framework elements. LINQ-to-Objects is particularly interesting in that it allows a declarative means to access and manipulate arrays, collections and so on. Furthermore, as Michael Sorens points out in his excellent article on LINQ, there a whole host of third-party LINQ providers, that offers a simple way to get at data in Excel, Google, Flickr and much more, without having to learn a new interface or language.

Of course, the need to be generic enough to deal with a range of data sources, from something as mundane as a text file to as esoteric as a relational database, means that LINQ is a compromise and so has inherent limitations. However, it is a powerful and beautifully compact language and one that, at least in its “query syntax” guise, is accessible to developers and DBAs alike. Perhaps there is still hope that LINQ can fulfill Phil Factor’s lobster-induced fantasy of a language that will allow us to “treat all data objects, whether Word files, Excel files, XML, relational databases, text files, HTML files, registry files, LDAPs, Outlook and so on, in the same logical way, as linked databases, and extract the metadata, create the entities and relationships in the same way, and use the same SQL syntax to interrogate, create, read, write and update them.”

Cheers,

Tony.

7 Responses to “Hype and LINQ”

  1. sitio1 says:

    Some devs programming using NHibernate think similar things like the ones you’ve described in your article !
    Oh my God !

    It’s not related to the same thing, we will still need a DBA for the database server because the database servers need some kind of administrative tasks that are out of the scope of the programming tasks.

    Simple but hard to understand for some developers.

  2. BuggyFunBunny says:

    Oh my Codd!!! I wonder, will LINQ be the final motivation for (database) developers to think fully “normally”? That is to say, exorcise loops/iterations/fetches once and for all?

  3. JOdell says:

    I have played both roles – early in my career I spent several years at Oracle but later switched to OOP with Smalltalk and now .Net. I agree in general with your observations. LINQ as a “substitute” for SQL is a misnomer that really only serves to confuse what LINQ is and how it fits in the .Net programmer’s bag of tricks.

    I was somewhat disappointed with the “declarative” LINQ syntax and almost always cases use the “dot” syntax for accessing LINQ functionality. It was way overdue for the .Net community, which over time has embraced so many established programming constructs, to embrace lambda expressions. To hide them under the “SQL-like” declarative syntax seems like was a good faith effort to introduce programmers to the idea of source-independent data access. But it hides the real power of the underlying technology.

    I licensed LinqPad and use it debug my LINQ statements or try out different ways of writing LINQ. It is a really valuable tool. However my occasional experiments so far to use it as a substitute for SSMS have not panned out. I think it is just the nature of the task – when I need to spelunk through my SQL Server Database – SSMS is still the way to go. When I need to experiment with other data sources – OData feeds, etc. LinqPad is fantastic. With SQL Server however, I use it primarily to check the best/most efficient LINQ access method to SQL Server – but not for general querying.

  4. Louis Somers says:

    It’s another instance of Maslow’s “golden hammer” (if the only tool you know is linq…)

    Linq is like a powerful swiss army knife, and if anyone would have to choose one single tool to study regarding data manipulation, I guess Linq would be a great choice.

    But ask the butcher, woodcutter and surgeon if they will swap their tools for your super swiss knife that can do all jobs… They might use it if it’s the only tool lying around in an emergency, but the right tool in skilled hands is far more effective.

  5. Cheval says:

    Linq covering 90% of data querying is the point.

    I describe it like “Using Linq to access multiple data soures is like using poetry to write a book.”

    For the most cases it works out well and enjoyable but if that book is technical in nature then it comes up a little short, but how it should be; generalise to make tiresome things easy and specialse when things get tough.

  6. timothyawiseman@gmail.com says:

    Linq is a fantastic tool, but I would hardly expect it to replace SSMS.

    For one thing, it is very inelegant (though entirely possible) to handle certain tasks in Linq compared with using SSMS. I would not want to create the initial layout for a new database in Linq, and I would be reluctant to do many routine maintenance tasks in it. Moreover, I find it would be difficult to explore a new database when first joining the team working on it through Linq. I have yet to find a better tool than SSMS for familiarizing myself with a new database structure when joining a project.

    Even for tasks where Linq may be suitable, it often produces SQL statements for the server to execute that are less effecient than what a skilled coder could craft by hand. In many cases, this is a very small tradeoff for the advantages that Linq can bring to a project. But in others where micro-optimizations are significant, it is well worth handcrafting the SQL to be executed and codifying as a stored procedure to be called rather than relying Linq.

    While Linq is a fantastic tool for certain types of jobs, it should not be the only tool and SSMS or something very similar to SSMS will be needed for the foreseeable future.

  7. wazz says:

    thanks for this entry. i’ve read a few articles that give or at least hint at misleading info about linq and they’ve nagged at me. i’ve barely used linq at all so far, but i have read about it a fair bit. as a hobbyist, i appreciate this entry (and comments) because it really helps to confirm (and/or crush) my nagging suspicions and doubts about what linq is and isn’t.

Leave a Reply