在 SQL CLR 中使用 Entity Framework 6 / EF Core

Question

微软Azure技术支持已确认，在SQL Server托管实例CLR中支持所有版本的.Net 4.XX，包括4.7.2。

我们正在使用4.7.2和最新的EF 6.2转换部分业务层。 在CLR存储过程中使用Entity Framework

https://patrickdesjardins.com/blog/how-to-use-third-party-dll-reference-in-a-sql-clr-function

因此，为了利用已经经过充分测试的代码的现有投资，我们希望将某些业务层移动到数据库中。

然而，当发布时我们遇到了问题。

创建[System.Dynamic]...

警告：Microsoft .NET Framework程序集'system.dynamic, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.'的注册未在SQL Server托管环境中进行充分测试，因此不受支持。将来，如果您升级或服务此程序集或.NET Framework，则您的CLR集成例程可能会停止工作。请参阅SQL Server Books Online以获取更多详细信息。

(47,1)：SQL72014：.Net SqlClient数据提供程序：

Msg 6218，Level 16，State 2，Line 1
为程序集'System.Dynamic'创建程序集失败，因为程序集'System.Dynamic'无法验证。检查所引用的程序集是否已经最新并且受信任（对于external_access或unsafe）在数据库中执行。
如果有任何CLR验证器错误消息，将在此消息之后跟随此消息[:
System.Dynamic.ArgBuilder::MarshalToRef][mdToken=0x6000002][offset 0x00000000]代码大小为零。[： System.Dynamic.ArgBuilder::UnmarshalFromRef][mdToken=0x6000003][offset

P.S.（在我看来，无论是使用EF Core还是EF6.2都没有关系，尽管如此，我们正在尝试使用EF6.2）

编辑： 已经给所有我所引用的程序集授予了UNSAFE权限： 以下是所有依赖项：

<ItemGroup>
    <Reference Include="Microsoft.CSharp">
      <HintPath>..\packages2019\dotnet 4.7.2\Microsoft.CSharp.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
    <Reference Include="System.Dynamic">
      <HintPath>..\packages2019\dotnet 4.7.2\System.Dynamic.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
    <Reference Include="System.Runtime.Serialization">
      <HintPath>..\packages2019\dotnet 4.7.2\System.Runtime.Serialization.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
    <Reference Include="SMDiagnostics">
      <HintPath>..\packages2019\v4.0.30319\SMDiagnostics.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
    <Reference Include="System.ServiceModel.Internals">
      <HintPath>..\packages2019\v4.0.30319\System.ServiceModel.Internals.dll</HintPath>
                <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
      <IsModelAware>True</IsModelAware>
      <SpecificVersion>True</SpecificVersion>
    </Reference>
      <Reference Include="EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL">
      <HintPath>..\packages\EntityFramework.6.2.0\lib\net45\EntityFramework.dll</HintPath>
            <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <SpecificVersion>True</SpecificVersion>
      <IsModelAware>True</IsModelAware>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
    </Reference>
    <Reference Include="EntityFramework.SqlServer, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL">
      <HintPath>..\packages\EntityFramework.6.2.0\lib\net45\EntityFramework.SqlServer.dll</HintPath>
            <SqlPermissionSet>UNSAFE</SqlPermissionSet>
      <SpecificVersion>True</SpecificVersion>
      <IsModelAware>True</IsModelAware>
      <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
    </Reference>

EDIT 3: Azure SQL Server MI中存在不安全的代码 EDIT 4:

- 致命问题是：System.RunTime.Serialization

CREATE ASSEMBLY [System.Runtime.Serialization]
    AUTHORIZATION [dbo]
    FROM 0x4D5...
    WITH PERMISSION_SET = UNSAFE;

我无法创建将Entity Framework UNSAFE程序集插入到数据库中的程序。我们能否绕过system.Runtime.Serialization？

GO
CREATE ASSEMBLY [EntityFramework]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90...
    WITH PERMISSION_SET = UNSAFE;


GO
PRINT N'Creating [EntityFramework.SqlServer]...';


GO
CREATE ASSEMBLY [EntityFramework.SqlServer]
    AUTHORIZATION [dbo]
    FROM 0x4...
    WITH PERMISSION_SET = UNSAFE;

警告：您正在注册的Microsoft .NET Framework程序集 “system.runtime.serialization,版本=4.0.0.0,culture=neutral, publickeytoken=b77a5c561934e089.”未在SQL Server托管环境中进行完全测试，因此不受支持。将来，如果您升级或为此程序集或.NET Framework提供服务，则您的CLR集成例程可能会停止工作。有关详细信息，请参见SQL Server图书在线。Msg 6218，级别16，状态2，行11创建用于程序集'System.Runtime.Serialization'失败，因为程序集'System.Runtime.Serialization' 验证失败。检查所引用的程序集是否已经更新且被信任（对于external_access或unsafe）以在数据库中执行。如果有CLR验证器错误消息，则会跟随此消息[：System.AppContextDefaultValues :: PopulateDefaultValues] [mdToken = 0x6000001] [偏移量0x00000000]代码大小为零。

- Abhijeet

---

好的，技术支持并不是100%正确的，因为某些来自.NET框架的程序集不能在SQLCLR中使用。我不知道这里是否是这种情况；您是如何使用EF创建程序集的？ - Niels Berglund

@NielsBerglund，谢谢。使用Nuget将EF下载到另一个项目中，然后在数据库项目中添加对Entity Framework DLL的引用。 - Abhijeet

实体使用SQL Server DLL，而不同版本的SQL Server DLL是不同的。 因此，如果您正在使用不同版本的SQL Server，请确保使用Clean强制整个项目重新编译为不同版本的SQL Server。另外，如果您修改数据库表/列，请确保更新C#应用程序与数据库之间的映射关系。 - jdweng

@Abhijeet。好的，我会查看您上面链接的第一篇文章，并将其中的每个程序集：smdiagnistics.dll等，作为独立程序集在数据库中创建（不安全），然后看看会发生什么。我有一个困扰我的怀疑，其中一个或多个程序集无法部署到SQL Server。 - Niels Berglund

感谢@NielsBerglund，请参见上面的编辑-3。虽然能够加载它们，但似乎无法在SQL MI上创建它们。 - Abhijeet

Answer 1

SQL Server的CLR宿主将使用系统上安装的最高版本的.NET Framework作为其链接的CLR版本。 SQL Server 2005 - 2008 R2链接到CLR版本2.0，因此这些版本将使用.NET Framework版本2.0、3.0和3.5。SQL Server 2012及更高版本均链接到CLR版本4.0，因此将使用.NET Framework版本4.x。

话虽如此，某些框架库是内置的，不需要手动添加。这些在此处列出：

支持的.NET Framework库

如果您需要的库未列入其中，您可以自己添加，但是这并不意味着您可以添加任何库。SQL Server仅允许纯MSIL库，而不是混合模式（混合=包含托管和非托管代码）。如果您需要的库是混合模式，则无法将其加载到SQL Server中。此外，请记住，即使某个库现在是纯MSIL，也并不意味着它不能在将来的Framework更新中转换为混合模式（是的，这种情况已经发生过）。

考虑到所有这些问题，在错误消息中的以下项：

代码大小为零。

很可能表明您正在尝试加载参考库。您需要加载实际库，而不是其引用版本。

我在SQL Server 2017上尝试了以下操作，能够全部加载，尽管并非所有库都需要显式加载。其中一些自动加载其他库，因为它们在同一个文件夹中:

CREATE ASSEMBLY [Microsoft.CSharp]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.CSharp.dll'
WITH PERMISSION_SET = UNSAFE;
-- includes System.Dynamic


CREATE ASSEMBLY [System.Runtime.Serialization]
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'
WITH PERMISSION_SET = UNSAFE;
-- includes SMDiagnostics, System.ServiceModel.Internals

然后使用以下方式进行检查：

SELECT * FROM sys.assemblies;
/*
Microsoft.CSharp
System.Dynamic
System.Runtime.Serialization
System.ServiceModel.Internals
SMDiagnostics
*/

请注意，目前我将数据库设置为TRUSTWORTHY ON。这不是我通常推荐的做法，也可能并不必要，但在我完成有关如何适当且容易地处理此问题的帖子之前，TRUSTWORTHY将足够使用。

我没有加载EntityFramework库，但您得到的错误是在第一个库上，我发布的语句没有出现该错误。如果您能够按顺序执行这两个语句以加载所有5个库，然后在EntityFramework上出现错误，那么我们将查看具体的错误消息。

然而，即使您能够加载所有这些DLL，包括用于EntityFramework的两个DLL，也不能保证您实际上能够使用EF。可能会阻止EF在SQLCLR中运行。我记不清楚EF的情况，但我知道SMO包括检测代码，以检测它是否在SQL Server中运行，如果是，则会引发异常，说明不允许在SQL Server中运行。

- Solomon Rutzky

---

谢谢。不幸的是，我正在使用 Azure SQL 服务器托管实例，该实例不支持从文件加载程序集。Entity Framework DLL 需要相应的 system.runtime.. 显然我们无法在那里重写我们的版本。 - Abhijeet

我可能没有尝试在服务器上运行EF，但这个链接来自于声誉很高的Microsoft的@david... https://stackoverflow.com/a/48290693/1431250 - Abhijeet

嗯，那个答案的意图是说服您不要尝试在SQL CLR中运行EF。 - David Browne - Microsoft

@Abhijeet 好的，我忘记了使用“FROM 'file'”的限制，但整个问题是一样的：某种方式你没有加载正确的DLL版本。尝试将框架库的源路径更改为我使用的路径，并保留..\packages\EntityFramework.6.2.0路径用于EF DLL。当然，我刚刚注意到EF DLL是.NET 4.5的，而你正在尝试在服务器上使用4.7.2，_可能_这是问题的一部分。无论如何，你需要在Azure服务器上使用存在的相同.NET版本的框架库。 - Solomon Rutzky

@Abhijeet，你的“编辑3”并没有真正显示任何内容。你在评论中提到无法加载System.Runtime.Serialization。错误信息是什么？你确定加载的是实际的DLL而不是引用程序集吗？我没有看到前两个DLL被加载：Microsoft.CSharp和System.Dynamic。你可能需要先加载这些。 - Solomon Rutzky

我看到了，谢谢。但是错误还是一样的：“代码大小为零”。你确定你正在尝试加载正确版本的程序集吗？DLL是否在C:\Windows\Microsoft.NET\Framework64\v4.0.30319中找到？那前两个程序集的加载呢？ - Solomon Rutzky

Answer 2

微软Azure技术支持已确认，包括4.7.2在内的所有.NET 4.XX版本都支持SQL Server Managed Instance CLR。

Managed Instance使用最新版本的.NET Framework来托管SQL CLR程序集。但这并不意味着它支持加载未经测试的.NET Framework程序集。请参见此处的支持声明。

此外，Managed Instance不允许使用UNSAFE CLR程序集，因为这些程序集将使您能够运行任意代码并直接访问服务器资源。

即使您可以将所有这些.NET Framework程序集加载到数据库中，这也不是Managed Instance中可支持的解决方案。正如上面的支持声明所明确的那样，您必须保持数据库中加载的.NET Framework程序集的副本与服务器上的.NET Framework版本同步。服务器上的.NET Framework会在Windows更新中更新。当您管理服务器时，将您的程序集与Windows版本保持同步已经很困难了。但当Microsoft对服务器进行修补时，您无法知道需要更新您的程序集。

在 Azure VM 上运行时，您将能够安装 EF 所需的所有程序集。 VM 中唯一的限制是您无法加载混合模式程序集，但我认为 EF（目前）并不依赖于这些程序集。如果您使用 .NET Framework 程序集构建的解决方案进入生产环境，则应该实现一个启动存储过程或定期任务，每次 SQL Server 启动时从 Windows .NET Framework 文件夹刷新数据库程序集。

总之，这是一个 PowerShell 脚本，我能够将 EF6 及其依赖项加载到 SQL Server 数据库中。但请记住，仅因为您可以加载程序集，并不意味着它会正确工作。您必须进行广泛的测试，以确定您的 EF 代码是否实际有效。

但是，在 SQL Server 上运行 .NET 代码是不寻常的，通常也不是一个好主意。它非常接近您的数据，但同一 VNet 上的单独 VM 也是如此。它使您的 SQL Server 更难管理，并且通常有更简单的方法来实现您希望通过在 SQL Server 上运行代码获得的任何收益。

此段话的大意是：另外，如果您在 SQL Server 上本地运行代码，很可能没有必要使用 SQL CLR。您可以将代码放在控制台应用程序中，然后使用 xp_cmdshell 或 SQL Agent 作业来触发它。无论如何，以下是注册程序集所需的 PowerShell。

$constr = "server=localhost;database=clrtest;integrated security=true"
$folder = "C:\Users\dbrowne\Source\Repos\SqlClrTest\ClassLibrary1\bin\Debug"
$netfx = "C:\Windows\Microsoft.NET\Framework64\v4.0.30319"

$dlls = @(
    "system.dynamic",
    "microsoft.csharp",
    "system.componentmodel.dataannotations",
    "smdiagnostics",
    "system.servicemodel.internals",
    "system.runtime.serialization", 
    "entityframework",
    "entityframework.sqlserver",
    "YourClassLibrary",
    "YourSqlClrProject"
    )


[System.Data.SqlClient.SqlConnection]$con = New-Object System.Data.SqlClient.SqlConnection $constr
$con.Open()

[System.Data.SqlClient.SqlCommand] $cmd = $con.CreateCommand()
$cmd.CommandText = @"

    if cast(serverproperty('ProductMajorVersion') as int) >= 14
    begin


        DECLARE @hash varbinary(64);

        SELECT @hash = HASHBYTES('SHA2_512', @assemblyFile);

        declare @description nvarchar(4000) = @name

        if not exists (select * from sys.trusted_assemblies where hash = @hash)
        begin
          EXEC sys.sp_add_trusted_assembly @hash = @hash,
                                           @description = @description;
          print 'trusted assembly added'
        end

    end

   declare @sql nvarchar(max) 

   if exists (select * from sys.assemblies where name = @name)
   begin

        set @sql =  concat('
        alter assembly ',quotename(@name),'
        FROM @assemblyFile
        WITH PERMISSION_SET = UNSAFE;  
        ')
       EXECUTE sp_executesql @sql, N'@assemblyFile varbinary(max)', @assemblyFile = @assemblyFile;
       print 'updated assembly ' + @name
   end
   else
   begin

        set @sql =  concat('
        create assembly ',quotename(@name),'
        AUTHORIZATION [dbo]
        FROM @assemblyFile
        WITH PERMISSION_SET = UNSAFE;  
        ')

       EXECUTE sp_executesql @sql, N'@assemblyFile varbinary(max)', @assemblyFile = @assemblyFile;
       print 'added assembly ' + @name

   end


"@

$pName = $cmd.Parameters.Add("@name", [System.Data.SqlDbType]::NVarChar, 1000)
$pAssemblyFile = $cmd.Parameters.Add("@assemblyFile", [System.Data.SqlDbType]::VarBinary, -1)



foreach ($targetDll in $dlls)
{
    try
    {
       $pName.Value = $targetDll
       if ([System.IO.File]::Exists("$folder\$targetDll.dll"))
       {
          $pAssemblyFile.Value = [System.IO.File]::ReadAllBytes("$folder\$targetDll.dll")
       }
       else
       {
          $pAssemblyFile.Value = [System.IO.File]::ReadAllBytes("$netfx\$targetDll.dll")

       }

       $result = $cmd.ExecuteNonQuery()

    }
    catch [System.Data.SqlClient.SqlException]
    {
       [System.Data.SqlClient.SqlException] $ex = $_.Exception

       write-host "$($ex.Class) $($ex.Number) $($ex.Message) "

       write-host ""

       continue;
    }
}
$con.Close()

- David Browne - Microsoft

---

嗨，David，谢谢。对我来说，当务之急是让Entity Framework运行起来，然后我们将重构我们的代码。我们已经能够在Azure SQL CLR MI中加载不安全的程序集。我们还缺少什么，请参见上面的编辑-3。 - Abhijeet

即使您从“您的”计算机安装了一些.NET Framework程序集到数据库中，它们仍然需要与服务器上的版本匹配，而这是您无法控制的。这可能不起作用，并且在SQL托管实例中肯定不受支持。 - David Browne - Microsoft

好的，因此我将在虚拟机上配置 SQL Server，并暂时禁用更新。您预见到 EF/LINQ 方面会有什么问题吗？ - Abhijeet

是的。这从未尝试过，而且有很多方法可能会“不起作用”。当您在虚拟机上运行时，可以将CLR代码编译为控制台应用程序，并使用xp_cmdshell或SQL Agent作业调用它。这是受支持的，并且实际上会起作用。但是，在虚拟机上，您可以尝试此操作，并且我正在添加一个PowerShell脚本来帮助您。 - David Browne - Microsoft

谢谢。另一个选择是使用代理版，我认为这将和将Web服务器和数据库服务器移动到同一虚拟机中一样好。我们越来越倾向于走这条路（只是为了验证延迟）。在我看来，我们的业务层在一台机器上会比在同一地区的两个独立服务器上更快。 - Abhijeet

是的。在同一服务器上运行IIS比SQL CLR或代理更易管理。但请记住，网络只是应用程序代码和数据库服务器之间延迟的一个组成部分。来回复制数据仍然不是免费的。提交事务也可能会有所贡献。将应用程序部署到单个服务器可能有所帮助，但改善应用程序性能通常需要更改应用程序代码。 - David Browne - Microsoft