Beginning LINQ for DBAs and database folks

Seems simple enough, but not sure when I would use it

First go at LINQ

Over the past year or so, I’ve read about Linq and have heard some of the .Net developers on the teams that I’ve worked on talk about it, but I’ve never dabbled in it myself. At first hearing about the technology, I immediately thought to myself “why”? Seemed klugey to me; as a DBA admin and dev, why would we revert back to the idiotic practice of placing dynamic code again all over the place? Why can’t we simply use TSQL and XML as we have been to fetch data? Why do I need to learn another language? Unfortunately, this line of thinking is old school, since everything these days, including NHibernate for .Net, the Entity Framework, and several other Object-relational (ORM) mapping tools, leverage the concept of data mapping and building strings, and the use sp_executesql to kick them off. And to tell the truth, we’re really not reverting; compiled code in vb.Net or c# are orders of magnitude better than the old stuff, and this is an acceptable method for dynamically generating and executing TSQL.

Data Mapping 101

A friend told me that NHibernate couldn’t automatically map a stored procedure. Developers linked tables from the database and then used the entities and mappings and their methods. My first reaction was, “Ok, so how can you leverage the great new TSQL, such as MERGE, PIVOT, UNPIVOT, CTE, etc”? His response was that you had to manually map the attributes of a stored procedure.

In order to set this up, create a simple c# console application. As you can see below, I have a simple c# console application project, and then added a new item called LINQ to SQL Classes item. In Linq, mapping a table is easy. You simply drag it to the .dbml workspace.

Here, I have dragged/dropped the orders table from the Northwind database. This created an Orders Data Class, so if you go into the .designer.cs file, you will see all of the mapping for your new class. Awesome! However, my goal here is to be able to use this technology and the nice new TSQL features noted above via a stored procedure. Doing this doesn’t necessarily use LINQ in the way it was probably intended, as you will notice that by dropping the table into the designer, the IDE immediately gens up all of the GET/SET accessors, and all of the methods as well, where mapping a stored procedure doesn’t do all of this. (Actually it does create a SET, but i’m not sure how this could be used).

Anyway, remove Orders and drag and drop the CustOrderHist stored procedure. Uh oh, nothing shows up in the designer. WTF? Actually, if you open the .designer.cs file, the mapping and code has been created, but there isn’t a nice visual that I can see. Don’t drag it again thinking that something went awry or you’ll get a CustOrderHist 1 set of code that you don’t really need.

Code

Here’s what the code might look like for firing off this procedure. Remember that this procedure needs a parameter of type NCHAR (5), which is the CustomerID. I’ve sent along my favorite ALFKI:

using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;

class Program
{
    static void Main(string[] args)
    {
        LinqConsole.LinqClassesDataContext Context
            = new LinqConsole.LinqClassesDataContext();

        var myQuery = from c in Context.CustOrderHist("ALFKI")
                      select c;

        foreach (var c in myQuery)
        {
            Console.WriteLine(c.ProductName.ToString() + " " + c.Total.ToString());
        }
    }
}

Conclusion?

<Yawn> So where are we at this point? Well, I’m not quite sure. As a database guy I don’t see the immediate use for LINQ on the projects that I am currently working. As database folks, in my opinion there is a definite need for us to understand the Entity Framework, NHibernate, LINQ, ORM, and other data mapping tools that are touching the databases that we administer. To me, this makes a lot of sense. If we don’t know about the latest and greatest tools on the application side of things, how can we understand what a developer needs, anticipate what he or she might require in the future, and be proactive to administering the database with respect to this type of data access?

I think the LINQ stuff and entity mapping is very cool, and I hope to learn more about it in the future, but for now this is definitely a tool for the .Net guys and not us data geeks in my opinion. I opened an SSIS project, added a Script task, and started looking for the LINQ class to implement this data access method, only to find in disappointment that apparently it is not allowed as a reference in this tool. Bummer. I’ll have to continue to investigate if we can use this inside of Integration Services. If it is possible, this could be a nice shortcut creating SQL connections.

Lee

---------------------------------------