Mike Taulty's Blog
Bits and Bytes from Microsoft UK


Mike Taulty's Blog



One of the requests/questions that I heard a few times at TechEd Europe was "When can we use LINQ inside of SQL Server?".

I must admit that I hadn't really thought about this very much because I tend to think of writing SQL Server procedures, functions, etc. as largely being done in T-SQL. I know that there are cases when CLR makes sense but I think of it as happening a lot less frequently than the T-SQL case.

So...whether these questions arose from an "interesting technology" point of view or whether they arose from a "I must have this to do my job" point of view, I'm not sure.

AFAIK, there aren't plans to make any of this work in SQL Server 2008 but that's just "AFAIK" - i.e. I could be wrong.

In terms of making it work, it'd involve loading a bunch of .NET V3.5 assemblies inside of SQL Server. In order to use assemblies inside of SQL Server, they have to be on the "approved" list and, again, AFAIK that list doesn't include V3.5 assemblies today.

There are no doubt a bunch of aspects around this for the various different LINQable things.

  1. C# V3.0/VB9.0 Language Features
    1. Remembering that the language features such as Lambdas are "compiler features" in C# V3.0 and VB9.0 there should be no problem in using these inside of SQL Server as there's no way for SQL Server to know whether the assembly that you load comes from (say) C# V2.0 or C# V3.0.
  2. LINQ to Objects (i.e. Query Expressions, methods called Where() and so on)
    1. This would require that you load up System.Core.dll inside of SQL Server. In my current build, I tried to do just that and I got a "not found" exception around it. I copied System.Core.dll to the same folder as my own assembly and it failed SQL's verification around "SAFE" code so it doesn't load today. I'm not aware of any plans to make it load either.
  3. LINQ to XML
    1. This would require that you load up System.Core.dll and System.Xml.Linq.dll inside of SQL Server so (for the reasons encountered in (2) above) this won't work at the moment and it would take more work to make work than (2) above. Again, not aware of plans. It'd be interesting to think about integration between LINQ to XML and the XML data type in SQL Server.
  4. LINQ to SQL/LINQ to Entities
    1. These are different to the other two. These would involve using a new data access API (albeit one that's based on ADO.NET) inside of SQL Server and that's quite a different (more complex) position than any of the previous 3 entries. Again, not aware of plans.

So, AFAIK this won't happen in SQL Server 2008 and if it were to happen beyond that then there's a spectrum from 1 to 4 above which seems to progress in terms of "how hard it might be to implement".

Update! I am reliably informed that both System.Core.dll and System.Xml.Linq.dll are going to be assemblies that you can load inside of SQL Server 2008 so you should be able to do 1,2,3 above with SQL 2008 :-) I'll have a play with LINQ to XML inside of SQL 2008 at a future point and post my findings.

Posted Tue, Nov 13 2007 6:03 AM by mtaulty


DotNetKicks.com wrote LINQ and SQLCLR
on Wed, Nov 14 2007 1:51 AM
You've been kicked (a good thing) - Trackback from DotNetKicks.com