log in

SQL 2005: using LINQ in SQLCLR

Luke Breuer
2009-12-03 23:12 UTC

(Note that I have only tried LINQ to objects, not LINQ to SQL.)

If LINQ functionality is used in a SQL CLR assembly, deploying that assembly to SQL Server 2005 from Visual Studio might result in this error:
Assembly 'system.core, version=, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog.

It is possible to use LINQ in SQL CLR code. More generally, it is possible to deploy assemblies targeting .NET 3.5 to SQL Server. The issue is that only a small list of assemblies are available in SQL Server 2005 by default. System.Core is not on this list. The best way to rectify this situation is to ensure that .NET 3.5 is installed on the SQL Server (redistributable installer, requires restart) and then properly register it.

Some of System.Core's contents:
  • the System.Linq namespace, with all of the LINQ operators
  • ExtensionAttribute which supports extension methods
  • HashSet<T>
Registering System.Core
If System.Core.dll is not installed, the instant a method is JITted (typically when it is first called), the error message above will present itself. Because of the JITting process, this error will not result when an assembly is deployed to a SQL Server or even when it is first run — the error only results if code runs that needs access to System.Core.

Registering the assembly produces the following warning. There's not much that can be done about this as far as I know.
Warning: The Microsoft .Net frameworks assembly 'system.core, version=, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

To register System.Core.dll:
create assembly [System.Core]
authorization [username]
from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.core.dll'
with permission_set = unsafe

The authorization [username] part exists because it the assembly has to be authorized by the same person who deploys [manually or from Visual Studio] any assemblies requiring System.Core. If this is not done, the following error messages presents:
CREATE ASSEMBLY failed because the assembly references assembly 'System.Core.dll', which is owned by another user.

Apparently this is by design, according to the CREATE ASSEMBLY documentation:
User must be the owner of any assemblies that are referenced by the assembly that are to be uploaded if the assemblies already exist in the database. To upload an assembly by using a file path, the current user must be a Windows authenticated login or a member of the sysadmin fixed server role. The Windows login of the user that executes CREATE ASSEMBLY must have read permission on the share and the files being loaded in the statement.
The language above is a bit hard to parse and I believe there is a grammar mistake; here's what I think the first sentence means:
The user deploying an assembly must be the owner of any deployed assemblies that are referenced by the assembly being deployed.

Even if you deploy System.Core as a non-sa account, you cannot deploy depending assemblies with sa. It's really quite annoying!

Granting all of the possible permissions to the System.Core assembly did not work.

If you attempt to register a different System.Core.dll when a version exists in the GAC, you will receive the following error message:
System.Core, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)