Dynamic LINQ to XML : Solving the OrderBy

With every new inovation lies new problems.  If you haven't checked out the awesome Dynamic library for LINQ to whatever,  you should.  The GU has a great post about it here.  I started using this and it worked great for LINQ to SQL, which is what I was using it for at the time.  Later I decided that I wanted to do some complex paging and sorting with a result set that was returning an XML document.  Based upon what I read in Scott's post it seemed like it supported LINQ to XML.  Well it sorta didn't.  You have to modify the Dynamic.cs file in order to get this to work.

The code that I was trying to execute looked something like this:



Click for larger image



This is the XML





The problem is that without any changes to the DynamicLibrary you get some error that either the Element is not a method of your object or your orderby selection is not a valid property. Ultimately it means that LINQ to XML doesn't support this right out of the box. Now I read another post that stated that the problem was that a few Objects were not in the predefinedTypes and so that got me thinking and I modified the Dynamic.vb file in my project...thats right V freakin B. I am sure the changes are quite similar in the Dynamic.cs file if you are programming in C#, but I was programming in VB.

Change your predefinedTypes to look like this and recompile and you should be able to do dynamic OrderBy with LINQ to XML:

 

SQL CLR Function

One of the most commonly used functions in SQL is the ability to take a delimited string and use it in a query.  Before the advent of .NET the only real way to solve this problem was to break the string apart by brute force using SQL and looping through the string looking for the delimiter, tokenizing out the strings into a return table.

Those days are over.  Not only was that very cludgey but wasn't all that effecient as well.  Now with .NET we can create a CLR function to do all the heavy lifting.

First you will need to create a Database Project.  For this post I will be documenting how I did it using Visual Studio 2010.  First thing you will do is open up your IDE and navigate to "New Project --> Database --> SQL Server".  Take special note on how you name this project.  I would name it something like {dbName}CLR as it will be the project where you will put all of your CLR Functions for a specific database. 

Next you will then "Add a New Item" and choose the type "Class".  This class file can be named anything, but I would keep it sort of generic as it will most likely be the same project that you add all of your functions to.

Next cut and paste the following code below into your class file:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "fnToList", FillRowMethodName = "FillRow", TableDefinition = "ID NVARCHAR(255)")]
    public static IEnumerable SqlArray(SqlString str, SqlChars delimiter)
    {
        if (delimiter.Length == 0)
            return new string[1] { str.Value };
        return str.Value.Split(delimiter[0]);
    }

    public static void FillRow(object row, out SqlString str)
    {
        str = new SqlString((string)row);
    }
};

Before you can deploy the class, you will need to enable clr on your SQL Server.  To do this you can execute the following commands:

sp_configure 'clr enabled', 1;
reconfigure with override;

After you compile the Class, which will hopefully be error-free, you can then Deploy the project. When you deploy the project it will push the Assembly to the database that you specified during the Project creation. Visual Studio will also create the user defined function for you.
If you did everything correctly you should see in SSMS in your Table-valued Functions a new function.

To test this, you can simply run the following:
nd it should produce the following output: