If you're a .NET user, working with the REPL and F# scripts have always been a great choice for working with SQL - especially with Type Providers. However, with the advent of .NET Core, two changes have occurred that can present some difficulties for F# scripts on .NET Core 3.1:
- Both of the most popular SQL providers (SqlClient and SqlProvider) have not yet been fully upgraded.
- Meanwhile, the core SQL library in .NET Core has also changed.
Note: The issues presented in this article only apply to F# scripts running in FSI. Full blown .NET Core F# applications work fine.
No SQL Client out of the box
You can't simply open up System.Data.SqlClient as it's no longer part of the base .NET Core installation. Instead, this has been moved to a relatively new package, Microsoft.Data.SqlClient, and you'll need to pull that down separately. You can do this using Paket's generate load script feature.
"This platform is not supported"
Unfortunately simply referencing the Microsoft.Data.SqlClient package isn't sufficient: If you use the DLL referenced by nuget, it won't work in a script:
#load "../../../.paket/load/netcoreapp3.1/microsoft.data.sqlclient.fsx"
let conn = new Microsoft.Data.SqlClient.SqlConnection(@"Server=(localdb)\ProjectsV13;Database=my-database;Integrated Security=True")
(*
System.PlatformNotSupportedException: Microsoft.Data.SqlClient is not supported on this platform.
at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString) in H:\tsaagent2\_work\s\artifacts\Project\obj\Release.AnyCPU\Microsoft.Data.SqlClient\netcore\netcoreapp3.1\Microsoft.Data.SqlClient.notsupported.cs:line 370
at <StartupCode$FSI_0005>.$FSI_0005.main@()
Stopped due to error
*)This is because we're referencing the standard netcoreapp3.1 DLL which - for some reason or other - doesn't work out of the box with F# scripts. Here's the offending reference:
#r "C:\\Users\\Isaac\\.nuget\\packages\\microsoft.data.sqlclient\.0.0\\lib\\netcoreapp3.1\\Microsoft.Data.SqlClient.dll"
To work around this, you'll need to manually reference the specific DLL for Windows that comes with the nuget package before calling the Paket-generated script:
#r @"C:\Users/Isaac/.nuget/packages/microsoft.data.sqlclient/2.0.0/runtimes/win/lib/netcoreapp3.1/Microsoft.Data.SqlClient.dll" // add this line
#load "../../../.paket/load/netcoreapp3.1/microsoft.data.sqlclient.fsx"Obviously this path is hard-coded to my environment - not an ideal situation.
3. Using Managed Networking
Now you'll be able to successfully create a connection to SQL using the code sample above. However, trying to open that connection will give another error:
conn.Open()
(*
System.DllNotFoundException: Unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)
at Microsoft.Data.SqlClient.SNINativeMethodWrapper.UnmanagedIsTokenRestricted(IntPtr token, Boolean& isRestricted)
at Microsoft.Data.Win32NativeMethods.IsTokenRestrictedWrapper(IntPtr token) in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Interop\SNINativeMethodWrapper.Windows.cs:line 483
at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrentNative() in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPoolIdentity.Windows.cs:line 31
at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrent() in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPoolIdentity.Windows.cs:line 17
at Microsoft.Data.ProviderBase.DbConnectionPoolGroup.GetConnectionPool(DbConnectionFactory connectionFactory) in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionPoolGroup.cs:line 160
at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPool(DbConnection owningObject, DbConnectionPoolGroup connectionPoolGroup) in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 211
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 41
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionInternal.cs:line 338
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionClosed.cs:line 39
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1611
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1115
at Microsoft.Data.SqlClient.SqlConnection.Open() in H:\tsaagent2\_work\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1095
at <StartupCode$FSI_0003>.$FSI_0003.main@()
Stopped due to error
*)Ugh! To get around this issue with SNI, what you don't need to do is reference the SNI package / DLL, or start playing around with environment path settings. Instead, you need to issue the following somewhat-cryptic command:
System.AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true)I have no idea what it does, but I have it on good authority that this is indeed a kosher, officially approved command.
4. Success!
Your final script should look like this and should successfully be able to connect to the database:
#r @"C:\Users/Isaac/.nuget/packages/microsoft.data.sqlclient/2.0.0/runtimes/win/lib/netcoreapp3.1/Microsoft.Data.SqlClient.dll"
#load "../../.paket/load/netcoreapp3.1/microsoft.data.sqlclient.fsx"
System.AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true)
let conn = new Microsoft.Data.SqlClient.SqlConnection(@"Server=(localdb)\ProjectsV13;Database=my-database;Integrated Security=True")
conn.Open()You should also be able to use the same technique when working with e.g. Dapper or RepoDB etc.
Summary
As we mentioned in our recent white paper, data access and F# are currently going through something of a transition, and SQL data access is one such area that historical has been exceptionally powerful in F# but currently is (at least within the script world) relatively weak.
This is not a great state of affairs, but I'm confident that by the time .NET 5 comes around later this year that things will be looking much rosier. Until then, this guide should be enough to unblock you from using F# scripts for data-oriented workloads on SQL.
Happy coding.
Isaac
