pekspro / ef-migrations-script-generator-task Goto Github PK

I've been using this nice tool for a year and it has worked fine. But, last Friday it broke on every single pipeline and still can't get it to work. What changed? Getting this error:

Starting: Generate Migration Scripts
Task : Entity Framework Core Migrations Script Generator
Description : Tool for projects that use Entity Framework Core Code-First. Generates migration scripts which can be used to update a database (for instance with the task ‘Azure SQL Database Deployment’).
Version : 1.0.2
Author : PEK’s Productions
Help : [More Information](https://github.com/pekspro/EF-Migrations-Script-Generator-Task)
Project path: D:\a\1\s\DLR.k12PlanningTool.Data
Start-up project path: D:\a\1\s\DLR.k12PlanningTool.UI
Target folder: D:\a\1\a/migrations
Number of database contexts: 1
All migrations will be used.
Checking of dotnet-ef is installed.
“C:\Program Files\dotnet\dotnet.exe” tool list --global
Package Id Version Commands
nbgv 3.4.255 nbgv
Parsing output: "Package Id Version Commands
nbgv 3.4.255 nbgv
"
Installing version undefined of dotnet-ef as global tool.
Will use NuGet configuration file: D:\a\1\s\nuget.config
“C:\Program Files\dotnet\dotnet.exe” tool install --global dotnet-ef --configfile D:\a\1\s\nuget.config
C:\Program Files\dotnet\sdk\6.0.201\NuGet.targets(130,5): error : Failed to retrieve information about ‘dotnet-ef’ from remote source ‘https://pkgs.dev.azure.com/dlrgroupdevops/f5a818e9-e793-450c-8e53-531c8c5623cd/_packaging/d9e143aa-e075-4ada-92b1-cddd6ccf16a0/nuget/v3/flat2/dotnet-ef/index.json’. [C:\Users\VssAdministrator\AppData\Local\Temp\fu3wdffo.stt\restore.csproj]
C:\Program Files\dotnet\sdk\6.0.201\NuGet.targets(130,5): error : Response status code does not indicate success: 401 (Unauthorized). [C:\Users\VssAdministrator\AppData\Local\Temp\fu3wdffo.stt\restore.csproj]
The tool package could not be restored.
Tool ‘dotnet-ef’ failed to install. This failure may have been caused by:

You are attempting to install a preview release and did not use the --version option to specify the version.
A package by this name was found, but it was not a .NET tool.
The required NuGet feed cannot be accessed, perhaps because of an Internet connection problem.
You mistyped the name of the tool.
For more reasons, including package naming enforcement, visit https://aka.ms/failure-installing-tool
##[error]The process ‘C:\Program Files\dotnet\dotnet.exe’ failed with exit code 1
Finishing: Generate Migration Scripts
[Screen Shot 2022-03-22 at 11.01.34 AM.png](https://aka.ms/dc/file?name=B25ae712cdcd3493cb4aa6da729325869637835617721111721_Screen_Shot_2022-03-22_at_11.01.34_AM.png&tid=25ae712cdcd3493cb4aa6da729325869637835617721111721)

I have used this task for about 6+ months now without any issue. I updated my EF project to 3.1.4 and for some reason, it keeps telling me
Specify which project file to use because this 'D:\a\1\s' contains more than one project file.
I can't figure out what is going on with it. I have no had 10 times across 11 pipeline runs.
I have tried many configurations in the Main Project Path and Startup project path. Including removing the startup project, and just pointing to the folder instead of the actual csproj file.
I have specifically selected 3.1.4 and install dependencies for .Net Core 3.
I have also tried adding Use .NET Core SDK specific version for 3.1.4.
Nothing helps. I keep getting the same error.

If I run the command directly on my dev machine I can get it to work just fine. Any help would be greatly appreciated. Thank you

2020-06-20T15:20:45.9252301Z ##[section]Starting: Generate Migration Scripts
2020-06-20T15:20:45.9452811Z ==============================================================================
2020-06-20T15:20:45.9453940Z Task : Entity Framework Core Migrations Script Generator
2020-06-20T15:20:45.9454497Z Description : Tool for projects that use Entity Framework Core Code-First. Generates migration scripts which can be used to update a database (for instance with the task 'Azure SQL Database Deployment').
2020-06-20T15:20:45.9454984Z Version : 0.0.42
2020-06-20T15:20:45.9455194Z Author : PEK's Productions
2020-06-20T15:20:45.9455547Z Help : More Information
2020-06-20T15:20:45.9455919Z ==============================================================================
2020-06-20T15:20:46.6466272Z Project path: D:\a\1\s\src\YNP.EntityFrameworkCore\YNP.EntityFrameworkCore.csproj
2020-06-20T15:20:46.6467667Z Start-up project path: D:\a\1\s\src\YNP.Web.Host\YNP.Web.Host.csproj
2020-06-20T15:20:46.6468840Z Target folder: D:\a\1\a/migrations
2020-06-20T15:20:46.6469758Z Number of database contexts: 1
2020-06-20T15:20:46.6470269Z Checking of dotnet-ef is installed.
2020-06-20T15:20:46.6535538Z [command]C:\hostedtoolcache\windows\dotnet\dotnet.exe tool list --global
2020-06-20T15:20:46.9799698Z Package Id Version Commands
2020-06-20T15:20:46.9809003Z -------------------------------------
2020-06-20T15:20:46.9884738Z
2020-06-20T15:20:46.9957249Z Parsing output: "Package Id Version Commands
2020-06-20T15:20:46.9958672Z -------------------------------------
2020-06-20T15:20:46.9959025Z "
2020-06-20T15:20:46.9959343Z Installing latest version of dotnet-ef as global tool.
2020-06-20T15:20:46.9991935Z [command]C:\hostedtoolcache\windows\dotnet\dotnet.exe tool install --global dotnet-ef --version 3.1.4
2020-06-20T15:20:49.9888996Z You can invoke the tool using the following command: dotnet-ef
2020-06-20T15:20:49.9889758Z Tool 'dotnet-ef' (version '3.1.4') was successfully installed.
2020-06-20T15:20:49.9938257Z
2020-06-20T15:20:50.0111372Z Generating migration script for YNPDbContext in project D:\a\1\s\src\YNP.EntityFrameworkCore\YNP.EntityFrameworkCore.csproj
2020-06-20T15:20:50.0165242Z [command]C:\hostedtoolcache\windows\dotnet\dotnet.exe ef migrations script --idempotent --project D:\a\1\s\src\YNP.EntityFrameworkCore\YNP.EntityFrameworkCore.csproj --startup-project D:\a\1\s\src\YNP.Web.Host\YNP.Web.Host.csproj --output D:\a\1\a/migrations/YNPDbContext.sql --context YNPDbContext --verbose
2020-06-20T15:20:50.2057794Z Specify which project file to use because this 'D:\a\1\s' contains more than one project file.
2020-06-20T15:20:50.2108101Z
2020-06-20T15:20:50.2284311Z ##[error]The process 'C:\hostedtoolcache\windows\dotnet\dotnet.exe' failed with exit code 1
2020-06-20T15:20:50.2366753Z ##[section]Finishing: Generate Migration Scripts

I find that the Code generator will generate scripts for migration
but can it also generate scirpt for DBcontext
It seems the Script Generator doesn't generate Script for DBContext Seeding?

https://docs.microsoft.com/en-us/ef/core/modeling/data-seeding

Please advise. Thanks a lot

I tried to replace something like this
"IDPClientSettings/Scopes":["openid","profile","offline_access","api"],
it returned this

Transformation found: IDPClientSettings/Scopes: openid profile offline_access api

How can I fix that?

Thanks

Is it in the plan to support selective migrations?

Example:

- task: efcore-migration-script-generator-task@0
  inputs:
    projectpath:        "./path"
    databasecontexts:   "Context"
    startupprojectpath: "./startup"
    targetfolder:       "./target"
    from: "20210405144817_Migration10"     <- here
    to:   "20210405204343_Migration12"     <- here

The dotnet tool has already support for this: https://docs.microsoft.com/en-us/ef/core/cli/dotnet#dotnet-ef-migrations-script

Trying to install the necessary tools will fail because the tooling is just available after a session restart.

Starting: Generate Migration Script
==============================================================================
Task         : Entity Framework Core Migrations Script Generator
Description  : Tool for projects that use Entity Framework Core Code-First. Generates migration scripts which can be used to update a database (for instance with the task 'Azure SQL Database Deployment').
Version      : 0.0.30
Author       : PEK's Productions
Help         : [More Information](https://github.com/pekspro/EF-Migrations-Script-Generator-Task)
==============================================================================
Project path: /home/vsts/work/1/s/MyProject.Model
Start-up project path: /home/vsts/work/1/s/MyProject/MyProject.WebUI
Target folder: /home/vsts/work/1/a/migrations
Number of database contexts: 1
Checking of dotnet-ef is installed.
/usr/bin/dotnet tool list --global
Package Id      Version      Commands
-------------------------------------

Installing dotnet-ef as global tool.
/usr/bin/dotnet tool install --global dotnet-ef
Since you just installed the .NET Core SDK, you will need to logout or restart your session before running the tool you installed.
You can invoke the tool using the following command: dotnet-ef
Tool 'dotnet-ef' (version '3.0.0') was successfully installed.

Generating migration script for DbCon in project /home/vsts/work/1/s/MyProject/MyProject.Model
/usr/bin/dotnet ef migrations script --idempotent --project /home/vsts/work/1/s/MyProject/MyProject.Model --startup-project /home/vsts/work/1/s/MyProject/MyProject.WebUI --output /home/vsts/work/1/a/migrations/DbCon.sql --context DbCon --verbose
Could not execute because the specified command or file was not found.
Possible reasons for this include:
  * You misspelled a built-in dotnet command.
  * You intended to execute a .NET Core program, but dotnet-ef does not exist.
  * You intended to run a global tool, but a dotnet-prefixed executable with this name could not be found on the PATH.

##[error]The process '/usr/bin/dotnet' failed with exit code 1
Finishing: Generate Migration Script

I've had trouble in the past running idempotent scripts because even though half of the scripts might work, if there is an error the migrations still get marked as ran. The issue with this is that if I try to re-run the script it won't fail anymore even if it should because it completely skips the failed migration.

I sometimes receive a "Microsoft.EntityFrameworkCore.Tools.CommandException: Build failed." when running this task. I'm building using 3.1.2. Any ideas why?

Every other step in my build process succeeds.

Hi.
In a description it says that this script is idempotent. I saw that you are running migration with idempotent flag, which is not really idempotent according to this: Ef core issue.

Are there any plans to support the --no-build flags when using that task?

My builds are failing now since the latest Task release it seems. Not just one project but all the projects that are using this Task Generator are failing.
My log is:

Will not try to install dotnet-ef. If you are using .NET Core 3 you could enable 'Install dependencies for .NET Core 3' to do this automatically.
Generating migration script for DatabaseContext in project /home/vsts/work/1/s/ProductSearch/MasterProductDatabase/MasterProductDatabase.csproj
/usr/bin/dotnet ef migrations script --idempotent --project /home/vsts/work/1/s/ProductSearch/MasterProductDatabase/MasterProductDatabase.csproj --startup-project /home/vsts/work/1/s/ProductSearch/ProductConfigurator/ProductConfigurator.csproj --output /home/vsts/work/1/a/migrations/DatabaseContext.sql --context DatabaseContext --verbose
Could not execute because the specified command or file was not found.
Possible reasons for this include:

• You misspelled a built-in dotnet command.
• You intended to execute a .NET Core program, but dotnet-ef does not exist.
• You intended to run a global tool, but a dotnet-prefixed executable with this name could not be found on the PATH.

##[error]The process '/usr/bin/dotnet' failed with exit code 1
Finishing: efcoremigrationscriptgeneratortask

When deploying stored procedures via a SQL script, the Script Generator puts the SQL used to create the Stored Procedure inside of a SQL IF BEGIN/END block. The issue with this is that this causes an error in SQL server in that you can't create/update a stored procedure inside of a IF block.

please add a separate input to specify the startup project.

The task runs dotnet msbuild but in my case it fails because I have a conditional import in my csproj based on the current configuration :

<Import Project="C:\dev\proj\BuildResources\Nuget\nugetpacker.props" Condition="'$(Configuration)' == 'Debug'" />

Since the configuration when building on the server is Release this import should be ignored. But for that, I must specify the current configuration in the msbuild parameters, which is not possible here.

Any way this could be added ?

Fuller details on Stack Overflow, where I posted my problem and my workaround.

tl;dr - When the migration uses a raw sql command in a migrationBuilder.SQL() instruction (in my case, that's how I do data transforms), the EF-Migrations-Script-Generator-Task converts that to a real SQL string. Normally that's fine, but if a later update has modified the schema of the database, the whole deployment script will fail to run because that raw SQL doesn't validate ("Invalid Column" errors).

My workaround was to go back through my transform instructions and wrap the SQL command string in a sql EXEC() command. This means the EXEC command is the raw SQL, and the payload instruction remains a string literal and is not subject to parsing unless the migration is being applied.

Suggestion: Wrap all SQL strings found in migrationBuilder.Sql() instructions in sql EXEC() instructions (and escape every literal inside the SQL string - single quote becomes 2x single quotes).

As far as I'm aware it would be that simple. I'm not aware of any SQL instruction that can't be escaped and wrapped that way, but other folks might be able to offer further advice on that.

Says it cant find the command to run the migration.
I tried ticking the new .NET Core 3 checkbox - but failed also. That did not surprise me as I am using .NET Core 2.2.

Under the hood it seems you're using dotnet ef migrations script. Can you add the -v switch to output more detail. Or make this an option through the UI?

Hi,

I like to request a new feature. At the moment it's not feasible to use your task if the NuGet.config contains at least one private repository with authentication. Due to some misconception in the design of the whole NuGet tooling, NuGet cannot handle anything if one entry is not available even it's not involved in the action.

I request to provide an --fileconfig argument which can handle an alternative configuration file.
dotnet tool install -g --fileconfig MyNuGet.config dotnet-ef.

Thank you and cheers

Hi! Getting this warning now in our build pipeline.

##[warning]This task uses Node 6 execution handler, which will be deprecated soon. If you are the developer of the task - please consider the migration guideline to Node 10 handler - https://aka.ms/migrateTaskNode10. If you are the user - feel free to reach out to the owners of this task to proceed on migration.

The linked migration guide seems pretty simple. Do you think this is something you'll have time to fix, or should I try to find time to open a PR?

Or it might need to be a newer version of the task, but I'm not familiar with how ADO task creation works, honestly. I just know you can pick a version when configuring a task.

If I have a pipeline consisting of a step that builds the project agains --runtime win-x64 and the efcore-migration-script-generator-task task with input build: false then the script fails. MS introduced a --runtime parameter that can be passed to the migration step.

The referenced PR will handle this issue.

Hi,

I've been using the task for a while now without an issue. I just added it to a new pipeline and it is failing with the post title description.

The only difference is that I am attempting to generate from a .Net Core 3 Class Library file - which also happens to be the startup in the solution, because the solution is made up of library projects.

Finds the data context fine, but just cant get over the line. The project file is in the correct location confirmed by the build action.

I did read where you state to include a path to a project using a library as a startup? That doesn't quite make sense to me - are you saying that I need to add a new executable project to the solution, referencing the library, just to generate a script?

Thanks for any assistance here.

I'm running .NET Core 5.0 and trying to get our DB changes included in our Azure DevOps configuration.

I added in the script, specifying startup project, output location, etc. But the output file that gets generated ONLY contains the insert statement for the EFMigrations table.

Where is the sql for the actual changes to the DB [schema] that need to occur in this build/release process?

Here's the YAML for the script:

- task: efcore-migration-script-generator-task@0 inputs: projectpath: 'src/My.Data.Project/My.Data.Project.csproj' databasecontexts: 'MyDbContext' startupprojectpath: 'src/My.Project.AdminApi/My.Project.AdminApi.csproj' targetfolder: '$(build.artifactstagingdirectory)/migrations' installdependencies: true

Hi

wil you plan update the Project up to .net 5 ?

Best Regards

I'm trying to use ef migration script in an Azure pipeline and am also receiving the dotnet-ef migrations script does not exist error. yaml code as follows:

- task: UseDotNet@2
      displayName: "Install .NET SDK"
      inputs:
        packageType: 'sdk'
        version: '3.1.x'
    
    - task: DotNetCoreCLI@2
      displayName: "Install dotnet-ef"
      inputs:
        command: 'custom'
        custom: 'tool'
        arguments: 'install --global dotnet-ef'

    - task: DotNetCoreCLI@2
      displayName: "Create Migration Script (Idempotent)"
      inputs:
        command: 'custom'
        projects: '$(System.DefaultWorkingDirectory)/EFDatabase/*.csproj'
        custom: 'ef migrations script'
        arguments: '--context $(dbContext) --idempotent --output $(Build.ArtifactStagingDirectory)\migration.sql'

On azure DevOps Server 2020 RC2 I get the following issue when trying to install v0.0.51

Extension validation error
Task definition input 'installdependencies' and Task definition input 'nugetconfiguration' must belong to same group as they are dependent inputs for Task with ID 'acdefe1b-3f7e-474e-b545-5816baa90334'.

Indeed there is no "groupName" on any task, but not being familiar with VSTS addon creation I don't want to mess things more myself so just reporting it.

For my application I am looking to let the build server also generate the down migrations, but that SQL isn't generated. Is there a way to make that happen?

 // if(output.indexOf("\ndotnet-ef ") < 0) {
            if(/\sdotnet-ef\s/gm.test(output) === false) {                

                if(eftoolversion) {
                    console.log("Installing latest version of dotnet-ef as global tool.");
                }
                else {
                    console.log("Installing version " + eftoolversion + " of dotnet-ef as global tool.");
                }

                let cmdPath = tl.which('dotnet');
                tool = tl.tool(cmdPath)
                            .arg('tool')
                            .arg('install')
                            .arg('--global')
                            .arg('dotnet-ef');

                if(eftoolversion) {
                    tool = tool.arg('--version')
                                .arg(eftoolversion)
                }

                await tool.exec();
            }
            else {
                console.log("dotnet-ef is already installed.");
            }

same like shell :

targetEfToolVersion='3.0.0'

eftoolversion=`dotnet tool list --global`

str_sub=`echo $eftoolversion | grep -Po '(?<=dotnet-ef ).*(?= dotnet-ef)'`

if [ ! $str_sub ]
then
echo 'dotnet-ef is empty'
elif [ $targetEfToolVersion == $str_sub ]
then
echo 'find and do not exec anything'
else
dotnet tool uninstall dotnet-ef --global
echo 'dotnet-ef is uninstall'
fi


#dotnet tool install --global dotnet-ef --version 3.0.0

Is there a way to pass the connectionString to the task? I am getting the value from KeyVault in a previous task in AzureDevOps and want to pass it to the script generator.

Any ideas?

Add support for --no-transactions option

https://learn.microsoft.com/en-us/ef/core/cli/dotnet#dotnet-ef-migrations-script

When an eftoolversion is supplied, the console message is Installing latest version of dotnet-ef as global tool.
When an eftoolversion is not supplied, the console message is Installing version undefined of dotnet-ef as global tool.
These should be the other way around?
The eftoolversion is respected and supplied to the --version argument, so this is mostly cosmetic.