SqlFunctionAttribute in Microsoft.SqlServer.Server doesn't work for table-valued UDFs

[gfody]

Describe the bug

After porting a SQLCLR assembly to netstandard2.0, scalar-valued UDFs work but table-valued UDFs do not.

The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.

To reproduce

create a test SQLCLR assembly targeting netstandard2.0 using the Microsoft.SqlServer.Server package:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
    <CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Server" Version="1.0.0" />
    <PackageReference Include="ILRepack.Lib.MSBuild.Task" Version="2.0.40" PrivateAssets="All" />
  </ItemGroup>
  <Target Name="ILRepacker" AfterTargets="Build">
    <ItemGroup>
      <InputAssemblies Include="$(OutputPath)Microsoft.SqlServer.Server.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)netstandard.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.ComponentModel.Composition.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Data.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Data.Common.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Diagnostics.StackTrace.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Diagnostics.Tracing.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.IO.Compression.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.IO.Compression.Filesystem.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Net.Http.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Net.Sockets.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Resources.ResourceManager.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Runtime.InteropServices.RuntimeInformation.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Runtime.Serialization.Primitives.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Runtime.Serialization.Xml.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Security.Cryptography.Algorithms.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Security.SecureString.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Threading.Overlapped.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Globalization.Extensions.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.ValueTuple.dll" />
      <InputAssemblies Include="$(MSBuildFrameworkToolsPath64)System.Xml.XPath.XDocument.dll" />
      <InputAssemblies Include="$(OutputPath)*.dll" Exclude="$(OutputPath)Microsoft.SqlServer.Server.dll" />
    </ItemGroup>
    <ILRepack InputAssemblies="@(InputAssemblies)" OutputFile="$(OutputPath)$(AssemblyName)$(TargetExt)" />
  </Target>
</Project>

class w/a scalar-valued udf and a table-valued udf to test:

using Microsoft.SqlServer.Server;
using System.Collections;

public static class SqlClr
{
    [SqlFunction]
    public static string ScalarTest() => "testing";

    [SqlFunction(FillRowMethodName = nameof(TableTestFillRow), TableDefinition = "test nvarchar(max)")]
    public static IEnumerable TableTest()
    {
        yield return "test1";
        yield return "test2";
        yield return "test3";
    }

    public static void TableTestFillRow(object o, out string test) => test = (string)o;
}

deploy the assembly:

create assembly [System.EnterpriseServices]
from 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.EnterpriseServices.dll'
with permission_set = unsafe

create assembly TestClr from 'TestClr.dll' with permission_set = unsafe

scalar-valued UDF works as expected:

create function ScalarTest() returns nvarchar(max) as external name TestClr.SqlClr.ScalarTest

select dbo.ScalarTest()

> testing

table-valued UDF throws:

create function TableTest() returns table (test nvarchar(max)) as external name TestClr.SqlClr.TableTest

> The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.

Further technical details

Microsoft.SqlServer.Server version: 1.0.0
.NET target: netstandard2.0
SQL Server version: SQL Server 2022
Operating system: Windows Server 2022

[mdaigle]

Hi @gfody, I'm digging into this, but some additional context would be helpful. You mentioned that this issue popped up "after porting" to netstandard2.0. Does this mean you were previously targeting .net framework directly? If so, which version of .net framework were you targeting and did your examples work at that time?

Can you also help me understand your motivation for targeting .net standard as opposed to .net framework?

[gfody]

yeah our SQLCLR assemblies currently target net481. the motivation for targeting netstandard2.0 was mostly just to see if it was possible. for context we recently started supporting macOS as a development environment which entailed splitting the SQLCLR assemblies into separate projects (see gfody/SqlClrTest). I wanted to see if it was possible to just deploy the netstandard2.0 build if so I'd not bother dual targeting.

btw I think it's likely ILRepack is messing up the SqlFunctionAttribute somehow but I couldn't get the assembly deployed w/o it (hit issues similar to what's described in #2838)