I’m writing some code that will be in a .NET 3.5 assembly running in SQL Server 2005. Since I’ll need to retrieve data from the database, I figured I might as well try out LINQ to SQL running inside SQL Server while I’m at it. The good news is that it will work, however you will have to add a couple more assemblies into SQL Server than just your assembly.
This post won’t go through the basics of SQLCLR integration. You can read about SQLCLR in this super long MSDN article or these 2 short and succinct articles instead (TIP: take the 2 short articles 
).
So to get LINQ to SQL working inside SQL Server 2005, you’ll need to add the following assemblies first (shown in hierarchical dependence order):
- System.Core
- System.Data.Linq
-
- System.Runtime.Serialization
-
- SMDiagnostics
Run the following script to register these assemblies (assuming default installation locations):
CREATE ASSEMBLY [SMDiagnostics]
AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll'
WITH permission_set = unsafe
CREATE ASSEMBLY [System.Runtime.Serialization]
AUTHORIZATION dbo
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.Runtime.Serialization.dll'
WITH permission_set = unsafe
CREATE ASSEMBLY [System.Data.Linq]
AUTHORIZATION dbo
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Data.Linq.dll'
WITH permission_set = unsafe
CREATE ASSEMBLY [System.Core]
AUTHORIZATION dbo
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.core.dll'
WITH permission_set = unsafe
Once these assemblies are in SQL Server, you’ll be able to add your LINQ-ified assembly into SQL Server.
With all those assembly registrations, is it worth it? I don’t think so. After I tested it out, I got rid of all those assemblies and replaced my LINQ code with a few simple calls to SqlCommand to get my small piece of data. This just goes to show you: the newest technology is not necessarily the best technology. Don’t just blindly follow whatever is the latest that Microsoft pitches to you!