Not able to generate SQL with dotnet-fm CLI

[rballonline]

Describe the bug
I'm trying to execute SQL that FM can then use to make a migration script. To do that I've included System.Data.SqlClient through nuget to grab data from the database. Upon running the CLI I'm receiving this error that says it can't find the System.Data.SqlClient.

Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.SqlClient, Version=4.6.1.2, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.
   at FluentMigratorWithExternalDlls.TestMigration.Up()
   at FluentMigrator.MigrationBase.GetUpExpressions(IMigrationContext context) in D:\a\1\s\src\FluentMigrator\MigrationBase.cs:line 83
   at FluentMigrator.Runner.MigrationRunner.ExecuteMigration(IMigration migration, Action`2 getExpressions) in D:\a\1\s\src\FluentMigrator.Runner\MigrationRunner.cs:line 837
   at FluentMigrator.Runner.MigrationRunner.ApplyMigrationUp(IMigrationInfo migrationInfo, Boolean useTransaction) in D:\a\1\s\src\FluentMigrator.Runner\MigrationRunner.cs:line 607
   at FluentMigrator.Runner.MigrationRunner.MigrateUp(Int64 targetVersion, Boolean useAutomaticTransactionManagement) in D:\a\1\s\src\FluentMigrator.Runner\MigrationRunner.cs:line 427
   at FluentMigrator.Runner.Initialization.TaskExecutor.Execute() in D:\a\1\s\src\FluentMigrator.Runner\Initialization\TaskExecutor.cs:line 165
   at FluentMigrator.DotNet.Cli.Commands.BaseCommand.ExecuteMigrations(MigratorOptions options, IConsole console) in D:\a\1\s\src\FluentMigrator.DotNet.Cli\Commands\BaseCommand.cs:line 32
   at FluentMigrator.DotNet.Cli.Commands.Migrate.OnExecute(IConsole console) in D:\a\1\s\src\FluentMigrator.DotNet.Cli\Commands\Migrate.cs:line 31
--- End of stack trace from previous location where exception was thrown ---
   at McMaster.Extensions.CommandLineUtils.Conventions.ExecuteMethodConvention.Invoke(MethodInfo method, Object instance, Object[] arguments)
   at McMaster.Extensions.CommandLineUtils.Conventions.ExecuteMethodConvention.OnExecute(ConventionContext context)
   at McMaster.Extensions.CommandLineUtils.Conventions.ExecuteMethodConvention.<>c__DisplayClass0_0.<<Apply>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at McMaster.Extensions.CommandLineUtils.CommandLineApplication.<>c__DisplayClass142_0.<OnExecute>b__0()
   at McMaster.Extensions.CommandLineUtils.CommandLineApplication.Execute[TApp](CommandLineContext context)
   at McMaster.Extensions.CommandLineUtils.CommandLineApplication.Execute[TApp](IConsole console, String[] args)
   at FluentMigrator.DotNet.Cli.Program.Main(String[] args) in D:\a\1\s\src\FluentMigrator.DotNet.Cli\Program.cs:line 35

To Reproduce
https://github.com/wtilton/FluentMigratorWithExternalDllsBug
Run dotnet-fm migrate -p SqlServer2016 --no-connection --preview -o -a bin\Debug\netcoreapp3.1\FluentMigratorWithExternalDlls.dll --start-version 0

Expected behavior
A SQL script to be produced

Information (please complete the following information):

• OS: Windows 10
• Platform .NET Core 3.1
• FluentMigrator version 3.2.9
• FluentMigrator runner FluentMigrator.DotNet.Cli
• Database Management System All I'd think but SqlServer2016 is what I'm using
• Database Management System Version SQL Server 2016 Developer Edition

Additional context
I've also tried to execute the CLI command in the publish directory after running dotnet publish with the same result.

[jzabroski]

What version of dotnet-fm do you have installed?

Please provide 👍

dotnet tool list

[rballonline]

dotnet tool list -g got it.

fluentmigrator.dotnet.cli 3.2.9 dotnet-fm

[jzabroski]

Got it. I think the problem is FluentMigrator uses nuget package 4.4.3

fluentmigrator/src/FluentMigrator.Runner.SqlServer/FluentMigrator.Runner.SqlServer.csproj

Line 20 in 2bb058d

<Version>4.4.3</Version>

I am less clear on why it is looking for System.Data.SqlClient, Version=4.6.1.2, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

I am going to try a few things this week.

[jzabroski]

I think, as a workaround, you can PROBABLY use any package version of this dependency that targets .netcoreapp2.0 but not any higher version. e.g.: https://www.nuget.org/packages/System.Data.SqlClient/4.4.3

I would not be surprised if this works too: https://www.nuget.org/packages/System.Data.SqlClient/4.6.1 - i bet that what is happening is RollForward is engaging and getting the latest .netcoreapp2.0 version of this dependency that isn't also a .netcoreapp3.1 dependency, but I'm not sure how to confirm that hypothesis.

[rballonline]

Got it. I think the problem is FluentMigrator uses nuget package 4.4.3

Do I need to use a different project type? Should I target .NET Core 2.1? I'll try that.

I am less clear on why it is looking for System.Data.SqlClient, Version=4.6.1.2, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a

I hadn't caught that but that is indeed strange. The project references 4.8.2.

[rballonline]

I think, as a workaround, you can PROBABLY use any package version of this dependency that targets .netcoreapp2.0 but not any higher version. e.g.: https://www.nuget.org/packages/System.Data.SqlClient/4.4.3

Worth a shot, makes sense, I'll try that.

I would not be surprised if this works too: https://www.nuget.org/packages/System.Data.SqlClient/4.6.1 - i bet that what is happening is RollForward is engaging and getting the latest .netcoreapp2.0 version of this dependency that isn't also a .netcoreapp3.1 dependency, but I'm not sure how to confirm that hypothesis.

Seems a tad easier. I'll try that as well. Thanks for some ideas.

[rballonline]

Ok, tried targeting 4.6.1, same error, couldn't find that particular version even still. Changed the project to .netcore2.0, new error wants 4.5.1, changed that. Now it says it can't find 4.0.0. Lol, wtf. I think I might be in some sort of DLL hell.

[rballonline]

It looks like FluentMigrator.Runner.SqlServer references a particular version of System.Data.SqlClient depending on the version. I'm going to remove this reference and see if that sorts things out.

[jzabroski]

Technically, you shouldn't need to include a reference to that package if FluentMigrator references it. You should at most reference FluentMigrator.Runner.SqlServer as that contains the contracts for advanced Sql server features like identity inserts.

[rballonline]

Technically, you shouldn't need to include a reference to that package if FluentMigrator references it. You should at most reference FluentMigrator.Runner.SqlServer as that contains the contracts for advanced Sql server features like identity inserts.

Yeah, I don't think I actually need references to any of them to get it running.

I keep reverting the version and it's wanting 4.0.0 now which nuget doesn't have. I feel like I have to just keep playing around with version numbers until something works. I would think I would need to match the version that my dotnet fm tool wants, not sure why it's jumping around. I wonder if using a .NETStandard project would be different.

[jzabroski]

I think the confusion is caused by the fact nuget package versions dont align with assembly version info numbers, unfortuantely.

[rballonline]

I've looked up how to change the nuget package manager version, but all I got was how to update to the latest.

Past that I'm going to see if I can get a local sourced copy of the CLI working, so I can start going through that specifically and see if I can load the dependent DLLs through the service locator. Not sure if that's entirely correct, but have been reading up on dotnet tools, and MEF, maybe I can get something working.

My goal though is to just generate a SQL script. If I could do just that I'd feel a ton better. Any ideas on that front?

1. Was trying to see if there was a way to call SQL outside of the migrations without any dependencies.
2. Was looking into if it was possible to generate scripts through the in-process runner. Was even thinking of just opening SQL profiler up and copying the commands but holy moly that'd be some work.

[rballonline]

Oh, I wonder if I can package the source that I have on my computer and then use my version of nuget to pack and deploy it to my own machine. That might be another idea.

[jzabroski]

Looking more carefully at your solution: Rather than use the following:
https://github.com/wtilton/FluentMigratorWithExternalDllsBug/blob/master/TestMigration.cs#L12-L14

Instead use Execute.WithConnection and don't use the provider specific System.Data.SqlClient namespace. Just use System.Data.

[rballonline]

Instead use Execute.WithConnection and don't use the provider specific System.Data.SqlClient namespace. Just use System.Data.

I'll try this now, got an example someplace? I think at some point to execute SQL I'm going to need System.Data.SqlClient

[rballonline]

Doesn't seem to work: https://github.com/wtilton/FluentMigratorWithExternalDllsBug/tree/WithoutSqlData

Migration should spit out select 1, has select 0.

[jzabroski]

This is probably one of the only gotchas with using FluentMigrator - Execute.WithConnection is a promise to execute something, not the execution itself. So, it captures a lambda. When the following statement runs, its not in the enclosed scope you think it is.

[jzabroski]

I think I should add some xmldocs to Execute.WithConnection, as this is probably the third time in the two years somebody gets tripped up by this.

[rballonline]

This is probably one of the only gotchas with using FluentMigrator - Execute.WithConnection is a promise to execute something, not the execution itself. So, it captures a lambda. When the following statement runs, its not in the enclosed scope you think it is.

Makes sense. I was seeing this behavior and was splitting up migrations based off of that. What I'm trying to do though is transfer data from one database to the other so I need the data. I might have to go deep into my SQL-fu to see if I can do all of this directly in SQL.

[jzabroski]

I got it. Well, the simplest workaround that will let you use a SqlConnection is to create your own In-process runner (effectively, without going into magical details, when you copy-paste the FluentMigrator.DotNet.Cli.csproj code, MSBuild does some magic to create binding redirects for you that avoid the above exceptions). I realize that's not desirable, but it will get you from point A to B.

I wonder how often this comes up, where someone wants to move data from one connection to another? We could have a --second-connection-string concept. Most often, I download data from a web service and import it into the database, or I have CSV files as embedded resources that I load with the help of CsvHelper. If your source database is read-only(-ish), you could dump a CSV and just load it in FluentMigrator using Execute.WithConnection and only one connection, which would also likely be more stable if you need to repeat it across dev, alpha, beta and prod environments.

[rballonline]

I think I can get around it in some parts, but in others, not so sure. I'm using a regex here to parse out individual comments from a comments text. Not sure how to do that in sql, maybe insert something into a temp table, problem I think is how do I get the darn data first lol.

                var legacyComments =
                    cnx.Query<LegacyComment>(
                        "select Id, comments from LegacyComments");


                var dateMatch =
                    new Regex(
                        @"((\d{1,2})(/|-)((\d{1,2})|([A-Za-z]{3}))(/|-)(\d{4}|\d{2}):)|(\d{2}(/|-)\d{4}:)|(\d{1,2}\s\D{3})\s\d{4}\s-",
                        RegexOptions.Compiled);

                foreach (var legacyComment in legacyComments)

[rballonline]

I got it. Well, the simplest workaround that will let you use a SqlConnection is to create your own In-process runner (effectively, without going into magical details, when you copy-paste the FluentMigrator.DotNet.Cli.csproj code, MSBuild does some magic to create binding redirects for you that avoid the above exceptions). I realize that's not desirable, but it will get you from point A to B.

Do you mean recreate the FluentMigrator.DotNet.Cli project? Because if so, I did already do that. Same issue. I was thinking of also making a copy or update the FluentMigrator.Runner.SqlServer to use my same version.

I wonder how often this comes up, where someone wants to move data from one connection to another? We could have a --second-connection-string concept. Most often, I download data from a web service and import it into the database, or I have CSV files as embedded resources that I load with the help of CsvHelper. If your source database is read-only(-ish), you could dump a CSV and just load it in FluentMigrator using Execute.WithConnection and only one connection, which would also likely be more stable if you need to repeat it across dev, alpha, beta and prod environments.

I've been using FM probably since it has come out and don't think I've had this requirement before. Works perfectly through the in-process runner. I'm actually moving data from 2 other sources. I'm not sure how CSV's would help, I have full access to the database so that isn't an issue. I believe my issue is more that I need the data while I'm doing in the migration. If I can load it from a CSV and not get an error I'll try that.

[jzabroski]

Hmm... Ok. I need to look more deeply into this. I feel like I am missing something Thanks for all your details!

[rballonline]

Hmm... Ok. I need to look more deeply into this. I feel like I am missing something Thanks for all your details!

Thank you for being along for the ride on this one. If there's anything I can do to help the project evolve I'm down to do it.

[jzabroski]

@wtilton That would be awesome. The .netcoreapp3.1 code is stuck in my github.com/jzabroski/FluentMigrator and the remaining TODOs are listed out in #1178 . I have a bunch of stuff I did to factor out the SAP SQL Anywhere stuff still not checked in in my local. I probably should have just bit the bullet and deleted SQL Anywhere, but the reason I kept at it is that long-term, some customers want to decouple the runner from the concrete database drivers.

If you want, we can do a zoom call and discuss what has to be done.

Separately, under https://github.com/fluentmigrator/fluentmigrator/tree/master/adr I started writing down some "architecture decision record" documents to help people understand the direction of the product and where the gaps are that need to be filled. It's a pretty mature project but I think it can be a lot more parsimonious and better. I plan to write up two more ADRs, one on lessons learned supporting pluggable .NET Core applications where the core logic is injected as DLL plugins, and another on possibly moving the code-base towards a different way of doing auto-completion called Tagless Final Interpretation.

[rballonline]

My biggest thing lately has been time. I've read a little of the docs that you put up, looks great, I just have a big lack of understanding of the code base. It's gotten better as I've gone through trying to tackle this bug, but I'm still not even understanding what the issues are with the existing codebase to really give you anything constructive. Do you have the buy in with the rest of the team? I even wonder if you put that amount of effort in, should you make it commercial? Do a re-write and grab the good bits of what's here? You got me :P

I guess what I'm trying to say is, if you had some straightforward tasks that took an hour or two I could probably help out. Long term I have so many things on my plate right now it wouldn't be fair for me to join you on your efforts as I'd be flakey as heck :P

I'll close this out as I don't think it's possible to fix or at least I was unable to do so.

[jzabroski]

I use FluentMigrator almost daily in my consulting, so I think I benefit a lot from any improvements I put into it. In terms of fixing this issue, I will try to get to it soon. I've been thinking of just pushing #1178 over the goal line and dropping SAP SQL Anywhere support, as that is the main thing slowing me down, because they do not seem to be maintaining the library and providing a .NET Core library for it.

[rballonline]

I use FluentMigrator almost daily in my consulting, so I think I benefit a lot from any improvements I put into it. In terms of fixing this issue, I will try to get to it soon. I've been thinking of just pushing #1178 over the goal line and dropping SAP SQL Anywhere support, as that is the main thing slowing me down, because they do not seem to be maintaining the library and providing a .NET Core library for it.

Then that makes sense then. I use it maybe 2-3 times a month. This month we're just transferring all data to our new shiny DB.

If it was me, I would drop anything that doesn't have .NET Core support and move on. Devs using that can use older versions I'd think. The more I think about it though, some sort of plug-in system would make sense to me.