SqlClient is not usable via transitive reference because build scripts are not transitive.

[analogrelay]

The Microsoft.Data.SqlClient package, as of version 1.0.19189.1-Preview, carries MSBuild targets that need to run when building .NET Framework apps in order to copy sni.dll. However, these targets are not "transitive" which means if you reference SqlClient indirectly through a library, you don't get those build targets and end up with errors.

It's my understanding that this can be solved by using a buildTransitive/ folder instead of build/. See the NuGet spec on the subject for more info. This was released in NuGet 5.0, so it does mean the package will require NuGet 5.0 or higher if it uses this feature.

Users can work around this issue by adding a direct dependency to Microsoft.Data.SqlClient but this is not the standard or expected practice in projects using the modern <PackageReference> syntax, which we do support for .NET Framework packages.

Repro steps

Example repo: https://github.com/anurse/SqlClientNotTransitive

1. Clone the repo
2. dotnet restore
3. dotnet run --project ./Consumer

Detailed steps (from scratch instead of using the repo)

1. cd to some empty directory
2. dotnet new sln
3. dotnet new console --name Consumer
4. dotnet sln add ./Consumer
5. dotnet new classlib --name Library
6. dotnet sln add ./Library
7. Add a <PackageReference>to Microsoft.Data.SqlClient 1.0.19189.1-Preview in Library.
8. Add a <ProjectReference> to Library in Consumer
9. Change the Consumer.csproj to target net471
10. Add Connector.cs to Library:

using System;
using Microsoft.Data.SqlClient;

namespace Library
{
    public static class Connector
    {
        public static void Connect()
        {
            var connection = new SqlConnection(@"Server=(localdb)\MSSQLLocalDB;Database=CacheTestDb;Trusted_Connection=True;");
            connection.Open();
        }
    }
}

11. Change Program.cs in Consumer to:

using System;
using Library;

namespace Consumer
{
    class Program
    {
        static void Main(string[] args)
        {
            Connector.Connect();
        }
    }
}

12. dotnet run --project ./Consumer

Expected Behavior

Connecting
Connected

This presumes the connection string above is valid for your environment. Even if it isn't you should at least see a connection failure message instead of what you end up getting.

Actual Behavior

Connecting

Unhandled Exception: System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNINativeMethodWrapper' threw an exception. ---> System.ComponentModel.Win32Exception: Failed to load C:\Code\anurse\SqlClientNotTransitive\Consumer\bin\Debug\net471\x64\SNI.dll
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.UnmanagedIsTokenRestricted(IntPtr token, Boolean& isRestricted)
   at Microsoft.Data.Win32NativeMethods.IsTokenRestrictedWrapper(IntPtr token)
   at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrent()
   at Microsoft.Data.ProviderBase.DbConnectionPoolGroup.GetConnectionPool(DbConnectionFactory connectionFactory)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPool(DbConnection owningObject, DbConnectionPoolGroup connectionPoolGroup)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Library.Connector.Connect()
   at Consumer.Program.Main(String[] args)

The build output from Consumer does indeed show no sni.dll:

> dir .\Consumer\bin\Debug\net472


    Directory: C:\Code\anurse\SqlClientNotTransitive\Consumer\bin\Debug\net471

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----         7/10/2019  1:24 PM           4608 Consumer.exe
-a----         7/10/2019  1:18 PM           4264 Consumer.exe.config
-a----         7/10/2019  1:24 PM            468 Consumer.pdb
-a----         7/10/2019  1:24 PM           4096 Library.dll
-a----         7/10/2019  1:24 PM            524 Library.pdb
-a----          7/8/2019  4:59 PM        1880016 Microsoft.Data.SqlClient.dll
-a----        12/11/2017 10:10 AM         146616 System.Data.Common.dll
-a----        12/11/2017 10:10 AM          23264 System.Diagnostics.StackTrace.dll
-a----        12/11/2017 10:10 AM          31448 System.Diagnostics.Tracing.dll
-a----        12/11/2017 10:10 AM          24296 System.Globalization.Extensions.dll
-a----        12/11/2017 10:10 AM         110784 System.IO.Compression.dll
-a----        12/11/2017 10:10 AM         198464 System.Net.Http.dll
-a----        12/11/2017 10:10 AM          23224 System.Net.Sockets.dll
-a----        12/11/2017 10:10 AM          26888 System.Runtime.Serialization.Primitives.dll
-a----        12/11/2017 10:10 AM          44816 System.Security.Cryptography.Algorithms.dll
-a----        12/11/2017 10:10 AM          22240 System.Security.SecureString.dll
-a----        12/11/2017 10:10 AM          25816 System.Threading.Overlapped.dll
-a----        12/11/2017 10:10 AM          22744 System.Xml.XPath.XDocument.dll

[ajcvickers]

/cc @divega @bricelam

[cheenamalhotra]

Hi @anurse

The team is looking into it and we'll get back to you soon!

[karinazhou]

Hi @anurse ,
Thank you for reporting this. We have updated our nuget package generation to include the buildTransitive folder and the fix will be available in the next release. The transitive feature will require NuGet 5.0 or above.

[analogrelay]

Awesome, thanks!

[mfreim]

I'm still seeing this in 1.1.0-preview1.19275.1. Same repro steps as above, adding the NuGet package directly to the exe project resolves the errors.

Is this expected to be resolved in 1.1.0-preview1.19275.1 or a later release?

[yukiwongky]

@mfreim it should be fixed in 1.1.0-preview1.19275.1. What is your NuGet version?

[mfreim]

I'm running NuGet 5.3.0 and Visual Studio 2019 16.3.5

[yukiwongky]

@mfreim we tried to run the repro locally again and it's working fine on our end. When you say "adding the NuGet package directly to the exe project" I presume you're talking about the sni package. When you build your project, how does your bin/Release[or Debug] directory look like? Does it have the Microsoft.Data.SqlClient.dll in place? Does it have the x64 and x86 folders? And inside them do you have the SNI.dll?

[mfreim]

I did some more investigation and found that this has to do with PackageReferences set to PrivateAssets in the solution. If I disable this the issue goes away.

To reproduce, use the same steps as the original comment, except add a Directory.Build.props to the solution root with the following contents:

<Project>
  <ItemDefinitionGroup>
    <PackageReference>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
  </ItemDefinitionGroup>
</Project>

Result
Could not load file or assembly 'Microsoft.Data.SqlClient, Version=1.10.19275.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5' or one of its dependencies. The system cannot find the file specified.

I see the specific "Failed to load sni.dll" error when I do this for some unit tests.

Note that there's no build error here, this is a runtime error. As far as I can tell this setup with PrivateAssets should be supported. If helpful I can open a new issue since the reproduction steps are slightly different.

[David-Engel]

@mfreim The documentation for PrivateAssets is a bit confusing, but the way I read it is that for PrivateAssets "These assets will be consumed but won't flow to the parent project". Meaning you have disabled the mechanism the SqlClient SNI package uses to ensure that SNI dependency objects flow to parent projects. I don't think there is anything the SqlClient or SqlClient SNI packages can do to counter the directive you have defined in your project.

[stevefoo]

I cloned the repo: https://github.com/anurse/SqlClientNotTransitive
Then updated to Microsoft.Data.SqlClient 1.1.0 for the Library project.
I added a WPF net framework 4.7.2 project and reference the Library project and called the static method Connector.Connect() (from Library project) in my WPF project and it throws the missing file exception. This is the case when debugging and with clickonce publish.

The Exception message is "Error: Could not load file or assembly 'Microsoft.Data.SqlClient Version=1.10.19324.4....'

I'm expecting the consumer to have the dependency, am I missing something?

I'm on Visual Studio 2019 v16.1.3 with Nuget Package Manager 5.1

[cheenamalhotra]

@stevefoo

I verified this works in WPF .NET Core project and not with WPF .NET Framework, would recommend opening issue with WPF Team (dotnet/wpf).

Although they're .NET Core, I'm sure they can answer better on how to get it working with WPF .NET Framework too! From our side, it doesn't look like a limitation.

[stevefoo]

thank you. we will look into migrating to core then.     Sent: Thursday, December 12, 2019 at 6:02 PM From: "Cheena Malhotra" <notifications@github.com> To: dotnet/SqlClient <SqlClient@noreply.github.com> Cc: SteveFoo <codefoo@mail.com>, Mention <mention@noreply.github.com> Subject: Re: [dotnet/SqlClient] SqlClient is not usable via transitive reference because build scripts are not transitive. (#137) @stevefoo I verified this works in WPF .NET Core project and not with WPF .NET Framework, would recommend opening issue with WPF Team (dotnet/wpf). Although they're .NET Core, I'm sure they can answer better on how to get it working with WPF .NET Framework too! From our side, it doesn't look like a limitation. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[michaelmairegger]

@stevefoo @cheenamalhotra Any news with projects WPF .NET Framework?
I have the sni.dll issue since I migrated to .net core 3.1 and use it in a net461 project.

[michaelmairegger]

Furthermore I have to say that I am building a DesktopBridge WPF application. There, in the .appxupload package the sni.dll file is missing.