LINQ on a DataTable IN a CLR Stored Procedure

Question

does anybody know if it's possible (and how) to use LINQ on a DataTable inside a CLR Stored Procedure?

I have no problems using LINQ on a DataTable in a standard WinForms proj however; I'm using a Database Project in VS2010 on SQL2005 and creating a Stored Procedure:

(simplified listing)

[Microsoft.SqlServer.Server.SqlProcedure]
public static void ProcessReOrder()
{       
    using (SqlConnection conn = new SqlConnection("context connection=true")) { 
        SqlDataAdapter adapter = new SqlDataAdapter("database.dbo.OtherSp", conn);  
        DataTable table = new DataTable("ReOrder");
        adapter.Fill(table);

        var query = from t in table.AsEnumerable() 
                    where t.Field<int>("Id") > 1000 select t.Field<int>("Id");

    etc.....
}

The problem is, AsEnumerable() is an extension method and although I've got the using reference to LINQ; it still isn't found.

Searching Google, has lead me to believe this is to do with using an LINQ Entity reference (DLL) buried in the framework however, as this is a DB Project, we don't get to add references to any DLL on the system; the add reference box is restricted to a select few assemblies and there isn't a browse... button.

Regardless of performance issues etc, I don't want to hand roll a lot of ADO/T-SQL inside my .NET Stored proc; i prefer LINQ and the ability to move the code to different layers/DB Platforms. Is LINQ on a DataTable inside a Stored Proc supported (i guessing not) or am I completely off base here and must code with T-SQL?

Answer 1

I'm not sure this will get you all the way there, however this will get System.Data.DataSetExtensions installed into SQL Server 2008 so you can reference it from your SQL CLR project. Note that I was logged in as a member of the sysadmin fixed server role.

First, I had to get the database prepared to load the unsafe assembly. I executed ALTER DATABASE dbname SET TRUSTWORTHY ON; then I set the owner of the database to sa with ALTER AUTHORIZATION ON database::dbname TO sa;.

Next, I extracted v3.5 of the assembly from the GAC on my workstation by copying the file to a holding directory (say c:\temp) from C:\Windows\assembly\GAC_MSIL\System.Data.DataSetExtensions\3.5.0.0__b77a5c561934e089.

I was then able to install the assembly with the statement CREATE ASSEMBLY [System.Data.DataSetExtensions] FROM 'C:\temp\System.Data.DataSetExtensions.dll' WITH PERMISSION_SET = UNSAFE;.

You'll receive this friendly reminder when the statement completes: Warning: The Microsoft .NET Framework assembly 'system.data.datasetextensions, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

At this point, I was then able to reference System.Data.DataSetExtensions in my SQL CLR project, and I confirmed that AsEnumerable() showed up on the DataTable instance.