SqlClient doesn't work with Native AOT on windows

[vonzshik]

Given a small hello world example:

await using var conn = new SqlConnection(ConnectionString);
await conn.OpenAsync();
await using var cmd = new SqlCommand("SELECT 'Hello World'", conn);
await using var reader = await cmd.ExecuteReaderAsync();
if (!await reader.ReadAsync())
	throw new Exception("Got nothing from the database");

var value = reader.GetFieldValue<string>(0);
if (value != "Hello World")
	throw new Exception($"Got {value} instead of the expected 'Hello World'");

throws on windows with Native AOT:

Unhandled Exception: System.TypeInitializationException: A type initializer threw an exception. To determine which type, inspect the InnerException's StackTrace property.
 ---> System.TypeInitializationException: A type initializer threw an exception. To determine which type, inspect the InnerException's StackTrace property.
 ---> System.DllNotFoundException: Unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll' or one of its dependencies: The specified module could not be found.
   at System.Runtime.InteropServices.NativeLibrary.LoadLibErrorTracker.Throw(String) + 0x79
   at Internal.Runtime.CompilerHelpers.InteropHelpers.FixupModuleCell(InteropHelpers.ModuleFixupCell*) + 0xdb
   at Internal.Runtime.CompilerHelpers.InteropHelpers.ResolvePInvokeSlow(InteropHelpers.MethodFixupCell*) + 0x2f
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(IntPtr) + 0x27
   at Microsoft.Data.SqlClient.SNILoadHandle..ctor() + 0x76
   at Microsoft.Data.SqlClient.SNILoadHandle..cctor() + 0x1c
   at System.Runtime.CompilerServices.ClassConstructorRunner.EnsureClassConstructorRun(StaticClassConstructionContext*) + 0xc6
   --- End of inner exception stack trace ---
   at System.Runtime.CompilerServices.ClassConstructorRunner.EnsureClassConstructorRun(StaticClassConstructionContext*) + 0x167
   at System.Runtime.CompilerServices.ClassConstructorRunner.CheckStaticClassConstructionReturnGCStaticBase(StaticClassConstructionContext*, Object) + 0xd
   at Microsoft.Data.SqlClient.TdsParserStateObjectFactory.get_EncryptionOptions() + 0x12
   at Microsoft.Data.SqlClient.TdsParser..cctor() + 0x1f
   at System.Runtime.CompilerServices.ClassConstructorRunner.EnsureClassConstructorRun(StaticClassConstructionContext*) + 0xc6
   --- End of inner exception stack trace ---
   at System.Runtime.CompilerServices.ClassConstructorRunner.EnsureClassConstructorRun(StaticClassConstructionContext*) + 0x167
   at System.Runtime.CompilerServices.ClassConstructorRunner.CheckStaticClassConstructionReturnNonGCStaticBase(StaticClassConstructionContext*, IntPtr) + 0xd
   at Microsoft.Data.SqlClient.TdsParser..ctor(Boolean, Boolean) + 0x11
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo, String, SecureString, Boolean, SqlConnectionString, SqlCredential, TimeoutTimer) + 0x16e
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer, SqlConnectionString, SqlCredential, String, SecureString, Boolean) + 0x1cb
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity, SqlConnectionString, SqlCredential, Object, String, SecureString, Boolean, SqlConnectionString, SessionData, Boolean, String, DbConnectionPool) + 0x386
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions, DbConnectionPoolKey, Object, DbConnectionPool, DbConnection, DbConnectionOptions) + 0x2f9
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool, DbConnection, DbConnectionOptions, DbConnectionPoolKey, DbConnectionOptions) + 0x40
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection, DbConnectionOptions, DbConnectionInternal) + 0x373
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection, DbConnectionOptions, DbConnectionInternal) + 0x5e
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection, UInt32, Boolean, Boolean, DbConnectionOptions, DbConnectionInternal&) + 0x4d1
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen() + 0x149
--- End of stack trace from previous location ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() + 0x20
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task) + 0xb6
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task) + 0x42
   at SqlClientAOT.Program.<Main>d__0.MoveNext() + 0x152
--- End of stack trace from previous location ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() + 0x20
   at SqlClientAOT.Program.<Main>d__0.MoveNext() + 0x8bd
--- End of stack trace from previous location ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() + 0x20
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task) + 0xb6
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task) + 0x42
   at SqlClientAOT.Program.<Main>(String[]) + 0x20
   at SqlClientAOT!<BaseAddress>+0xd0ecab

Works fine on linux (ubuntu wsl). Would have been nice if SqlClient supported Native AOT on windows.
Also, the resulting binary on linux is about 45mb, but that's a different issue altogether.

.NET 7.0.3, SqlClient 5.1.0.

[Wraith2]

I don't know if AOT support is a scenario that is on the roadmap. I'm sure it would be nice but it's quite a complex task.

[vonzshik]

I don't know if AOT support is a scenario that is on the roadmap. I'm sure it would be nice but it's quite a complex task.

Are we speaking of async/mars level of complexity (3+ years) or something a bit more optimistic?

[Wraith2]

Unknown. Without knowing what the blocking factors are I'd be guessing. The complexity that I can see comes from the way that you have to work out what can or cannot be trimmed, try it and see if it works. I also don't know if it's a goal for the MS team. While we can likely make it work from community contributions only the speed it limited.

[kant2002]

Doing following steps:

mkdir sqlclientaot
cd sqlclientaot
dotnet new console
dotnet add package Microsoft.Data.SqlClient
// Modify project by adding <PublishAot>true</PublishAot>

using this code

using Microsoft.Data.SqlClient;

var ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=master;Integrated Security=SSPI";
await using var conn = new SqlConnection(ConnectionString);
await conn.OpenAsync();
await using var cmd = new SqlCommand("SELECT 'Hello World'", conn);
await using var reader = await cmd.ExecuteReaderAsync();
if (!await reader.ReadAsync())
	throw new Exception("Got nothing from the database");

var value = reader.GetFieldValue<string>(0);
if (value != "Hello World")
	throw new Exception($"Got {value} instead of the expected 'Hello World'");
Console.WriteLine("All good");

then dotnet publish -c Release
and finally run bin\Release\net7.0\win-x64\publish\sqlclientaot.exe
Everything was good and working as expected.
By running dir bin\Release\net7.0\win-x64\publish I have following output

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a---           1/20/2023  8:13 PM         309416 clretwrc.dll
-a---           1/20/2023  8:13 PM         659624 clrgc.dll
-a---           1/20/2023  8:13 PM        1532072 clrjit.dll
-a---           1/20/2023  8:13 PM        5103272 coreclr.dll
-a---           1/20/2023  8:12 PM          61168 createdump.exe
-a---           1/13/2023  4:19 AM         506272 Microsoft.Data.SqlClient.SNI.dll
-a---           1/12/2023  9:11 PM        1830336 Microsoft.DiaSymReader.Native.amd64.dll
-a---           1/20/2023 12:00 AM        1315192 mscordaccore_amd64_amd64_7.0.323.6910.dll
-a---           1/20/2023 12:00 AM        1315192 mscordaccore.dll
-a---           1/20/2023 12:00 AM        1247096 mscordbi.dll
-a---           1/20/2023  8:13 PM         137384 mscorrc.dll
-a---           1/19/2023 11:58 PM         534416 msquic.dll
-a---            3/5/2023  9:00 AM       34578944 sqlclientaot.exe
-a---            3/5/2023  9:00 AM      126676992 sqlclientaot.pdb
-a---           1/20/2023  8:13 PM         828072 System.IO.Compression.Native.dll

Given that your error indicates that Microsoft.Data.SqlClient.SNI.dll is probably missign, it's possible that you copy only EXE file without native Microsoft.Data.SqlClient.SNI.dll

[vonzshik]

@kant2002 the resulting binary is in bin\Release\net7.0\win-x64\native.

[kant2002]

@vonzshik thats wrong location. Technically that’s executable but that’s not all what is needed for running NativeAOT application. You should look at bin\Release\net7.0\win-x64\publish folder

[vonzshik]

@kant2002 yeah, I just realized that. I was expecting a single binary, but I guess SqlClient is a bit different in this regard. I'm closing this issue since everything works and I just was looking at the wrong folder.

[Wraith2]

I'm closing this issue since everything works and I just was looking at the wrong folder.

Unless we've managed to run the entire test suite in AOT mode I would hesitate to say that it's aot compatible. There's a lot of code this example doesn't touch. Bulk Copy, AE, etc. If we want to be able to say that it's compatible we need to test exhaustively.

[vonzshik]

I'm closing this issue since everything works and I just was looking at the wrong folder.

Unless we've managed to run the entire test suite in AOT mode I would hesitate to say that it's aot compatible. There's a lot of code this example doesn't touch. Bulk Copy, AE, etc. If we want to be able to say that it's compatible we need to test exhaustively.

Oh, it's definitely not AOT compatible (there were quite a few trimming warnings and the resulting binary size leaves to be desired). Nevertheless, the basic scenario works (which is what this issue is about). Anything else I would have solved on case by case scenario, though that's up to the maintainers.