Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – Creating an EF migration for a SQL script

I have created a SQL script file with a bunch of insert’s into a table if the values do not already exist.

It’s made up with a series of:

INSERT INTO Departments (Number, [Name])
    SELECT 3, 'TOPS'
    WHERE NOT EXISTS (SELECT Number FROM Departments
                      WHERE Number = 3)

I have been asked if I can create a migration script in Visual Studio Entity Framework so that other devs can run it using EF code-first and calling the update-database command.

I have no idea how to create a migration script to run this. Any pointers appreciated.

Tags:

3

Answers


  1. 0

    You should probably use the built in data seeding functions. Add a .HasData call to your dataContext file, i.e.

    modelBuilder.Entity<Departments>().HasData(new Department{Number = 3, Name = "TOPS"});

    and use the regular Add-Migration command to generate a migration. You could also add an empty migration and manually insert migrationBuilder.Sql(...) to run arbitrary sql code.

    See Migrations overview for a general overview of the migration concept

    Login or Signup to reply.
  2. 0

    Based on your comment on the question, it appears that you want to be able to use already-prepared SQL code specifically. In that case, you can simply generate an empty migration and then execute some arbitrary SQL. Here’s an example migration from a current project of mine:

    using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace SI.Gbrmpa.Eotr.Model.Migrations
{
    public partial class MergeFluorescingBleachingSeverityIntoPaleInReefHealthAndImpactSurveyCoralBleachingObservation : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            DatabaseUpdater.DataManager.MergeFluorescingBleachingSeverityIntoPaleInReefHealthAndImpactSurveyCoralBleachingObservation(migrationBuilder);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // Changes cannot be rolled back.
        }
    }
}

    and here’s the DatabaseUpdater.DataManager class:

    public static class DataManager
{
    public static void MergeFluorescingBleachingSeverityIntoPaleInReefHealthAndImpactSurveyCoralBleachingObservation(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(Properties.Resources.MergeFluorescingBleachingSeverityIntoPaleInReefHealthAndImpactSurveyCoralBleachingObservation);
    }
}

    That resource is a string containing SQL code.

    You can do basically the same thing, with or without the additional class, and the SQL can he hard-coded or whatever. You’d obviously also want to provide a rollback mechanism if at all possible. This one required manual intervention but we have others that downgrade automatically.

    Login or Signup to reply.
  3. 0

    You can create an empty migrations

    Add-Migration "My INSERT INTO"

    public partial class Mynewemptymigration : DbMigration
{
    public override void Up()
    {
        @"INSERT INTO Departments (Number, [Name])
        SELECT 3, 'TOPS'
        WHERE NOT EXISTS (SELECT Number FROM Departments
                  WHERE Number = 3)"
    }

    public override void Down()
    {
         //Rollback here
    }
}

    Update-Database –your options

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search