SQLProvider does not support PublishSingleFile switch in .NET 5

[pascallapradebrite4]

Describe the bug Executables produced with .NET 5 SDK RC 1 crash when built with the PublishSingleFile switch. The stacktrace produced is the following:

Unhandled exception. System.Exception: Unable to resolve assemblies. One of MySql.Data.dll, MySqlConnector.dll (e.g. from Nuget package MySql.Data) must exist in the paths: 
 /home/vsts/work/1/s/Database
/home/vsts/.nuget/packages/mysqlconnector/1.0.0/lib/net5.0
/home/pascallaprade 
Details: 
resolutionPath directory doesn't exist:/home/vsts/work/1/s/Database
   at Microsoft.FSharp.Core.PrintfModule.PrintFormatToStringThenFail@1433.Invoke(String message)
   at System.Lazy`1.ViaFactory(LazyThreadSafetyMode mode)
--- End of stack trace from previous location ---
   at System.Lazy`1.CreateValue()
   at FSharp.Data.Sql.Providers.MySql.createConnection[a](a connectionString)
   at FSharp.Data.Sql.Providers.MySqlProvider.FSharp-Data-Sql-Common-ISqlProvider-CreateConnection(String connectionString)
   at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.addCache@37-1.Invoke(Unit unitVar)
   at System.Lazy`1.ViaFactory(LazyThreadSafetyMode mode)
   at System.Lazy`1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor)
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.get_Value()
   at FSharp.Data.Sql.Runtime.SqlDataContext..ctor(String typeName, String connectionString, DatabaseProviderTypes providerType, String resolutionPath, String[] referencedAssemblies, String runtimeAssembly, String owner, CaseSensitivityChange caseSensitivity, String tableNames, String contextSchemaPath, OdbcQuoteCharacter odbcquote, SQLiteLibrary sqliteLibrary, TransactionOptions transactionOptions, FSharpOption`1 commandTimeout, SelectOperations sqlOperationsInSelect)
   at MachineComputerManager.Database.Context.getContext(String connectionString)
   at MachineComputerManager.Database.Database.getContext(ConfigType config)
   at MachineComputerManager.Database.Database.getComputers@20.Invoke(ConfigType x)
   at MachineComputerManager.Commands.GetComputers.doGetComputers(ConfigType config, Parameters parameters)
   at MachineComputerManager.Commands.GetComputers.exec[a](ConfigType config, FSharpMap`2 args)
   at Program.commands@16-2.Invoke(ConfigType config, FSharpMap`2 args)
   at Program.main(String[] argv)
Aborted (core dumped)

To Reproduce Steps to reproduce the behavior:

1. dotnet new console --language=f# -o UnableToResolveAssemblies
2. In the project folder, dotnet add package MySqlConnector and dotnet add package SqlProvider
3. Replace the contents of Program.fs with the following:

   
   open FSharp.Data.Sql

[] let ConnectionString = // Change Database, User and Password for compile-time type safety checks. "Server=localhost;Database=database;User=user;Password=password;"

type internal DbProvider = SqlDataProvider< Common.DatabaseProviderTypes.MYSQL, ConnectionString

let getConnectionString = sprintf "Server=%s;Database=%s;User=%s;Password=%s"

[] let main argv = match argv with | [|server; database; user; password|] -> // Provide the server, database, user and password at runtime if it differs // between your build machine and your target machine. DbProvider.GetDataContext (getConnectionString server database user password) |> ignore | _ -> DbProvider.GetDataContext () |> ignore 0

4. Publish the project using .NET 5 SDK RC 1 (change the -r switch according to the machine on which you'll execute the build): `dotnet publish -c Release -r linux-x64 /p:PublishSingleFile=true /p:IncludeNativeLibrariesInSingleFile=true`
5. Run the executable on a machine that does not have the MySql.Data.dll or MySqlConnector.dll installed.
6. You should see an error similar to that shown in the description.

**Expected behavior**
The application should work on .NET 5 even if I am using the `PublishSingleFile` switch. We use this instead of containers to run multiple .NET executables without them conflicting.

**Desktop (please complete the following information):**
 - OS: built on MacOS Catalina 10.15.6 and on Azure DevOps Ubuntu 18.04 agent, executed on an Ubuntu 20.04 machine.
 - .NET SDK version: 5.0.100-rc.1.20452.10

**Additional context**
We produced builds that were working fine up to the .NET 5 SDK Preview 7, and builds started failing starting with Preview 8. We opened an issue in the .NET repo (https://github.com/dotnet/runtime/issues/42766), thinking it was a regression in the SDK previews, but it seems that it is actually the expected behavior of the single-file mode that is causing the crash.

My understanding is that the .NET Core 3.1 version of the single file mode was simply extracting its content on the filesystem, which meant that relying on assembly paths was still working. However, it seems like assemblies are now instead loaded from memory, which breaks path access.

The person that helped on the ticket pointed to these lines in SQLProvider, saying that this is indeed not supported in single file mode: https://github.com/fsprojects/SQLProvider/blob/e9549c7fc6b7a0bfb8dd2815554221138f6c1d78/src/SQLProvider/Utils.fs#L407-L414

[Thorium]

You have to add ResolutionPath static parameter to point the folder of the MySqlConnector driver.

[pascallapradebrite4]

@Thorium Thanks for your support!

However, our goal is to have a single binary file that we can put on any computer and just run it, not to have to go and install MySqlConnector drivers in a predetermined folder that we can then hardcode statically. I can understand then if this goal is simply not supported by the project.

What I know, simply, is that we were able to have a single executable deployed for months, without providing the ResolutionPath, and that worked fine, so even without us providing the path, the .NET extraction method for single executables made everything work. Now that assemblies are instead loaded in memory, this behavior of SQLProvider+MySqlConnector+PublishSingleFile is broken, and ResolutionPath is not a solution for us (though at least there will be a workaround on the .NET side if this cannot be fixed here).

[Thorium]

The ResolutionPath is also used on compilation time, and for intellisense in Visual Studio (or whatever IDE you are using). So that has to be there for the compiler.

On runtime, I guess we could just add the probing of the driver to the bundled-files inside the single file, however that is done... Do you know how? PRs accepted. :-)

[pascallapradebrite4]

@Thorium Interestingly, we realized that the ResolutionPath didn't need to be specified at compile time, at least with the .NET 5 SDK. We haven't tried with .NET Core 3.1, so we do not know if this is a change in how the compiler resolves dependencies or something else. We do, however, specify it in the code for developers, to get IDE support as you mentioned. We simply use a compiler directive to not give the ResolutionPath when we're actually building, since that allowed us to use the single file until recently.

I'm not familiar with how the new single file works with .NET 5, but if it's simply loading all the assemblies in memory, I'm pretty sure there's a way to get it without too much changes being needed in the current resolution code, simply trying to load it from there instead than from a path.

I'll try to work on this on my personal time to see if I can figure out how to get it working! Thanks again!

[Thorium]

Thanks, released in NuGet package 1.1.93

[pascallapradebrite4]

And I can confirm the fix works with our app. Thanks!