CODEWITHSUNDEEP
c#asp.net-coreazure-sql-database.net-5ef-core-3.1
Jimmy
Jimmy

Reputation: 135

EF Core/Azure SQL DB: How to run migrations without deleting current data in production

I have deployed a SQL database on Azure SQL database and published an API in .NET 5.0, however, EF Core/Database is an area I'm lacking a little knowledge in.

Right now I have a lot of data in the database I had to input manually and I don't want to have to input everything again because I didn't ask the "stupid question".

What I'm trying to do:

Be able to run migrations anytime whether I'm modifying a table or creating a new one without deleting data in production

Problem:

the Seed Data is in the DbContext ( in OnModelCreating ) and have been added to the first migration. Before I apply the new migration I've generated the Migration Script twice, once with the seed data uncommented and once commented.

THE NEW TABLE I'M TRYING TO ADD IS Sectors and there is no Seed Data for it.

AppDbContext.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<...>().HasData(new Device
        {
            // ...
        });
    //
    // All the seed data is added this way
    //
}

Uncommented:

Up: Adds new tables and doesn't do anything about seed data ( just some hours from StoreHours table, confusing )

Down: Drops new tables

using System;
using Microsoft.EntityFrameworkCore.Migrations;

namespace App.Persistence.Migrations
{
public partial class SeedDataUncommented: Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<Guid>(
            name: "SectorId",
            table: "Places",
            type: "uniqueidentifier",
            nullable: true);

        migrationBuilder.CreateTable(
            name: "Sectors",
            columns: table => new
            {
                SectorId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
                Name = table.Column<string>(type: "nvarchar(max)", nullable: false),
                City = table.Column<string>(type: "nvarchar(max)", nullable: false),
                CreatedDateTime = table.Column<DateTime>(type: "datetime2", nullable: false),
                CreatedBy = table.Column<string>(type: "nvarchar(max)", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Sectors", x => x.SectorId);
            });

        migrationBuilder.UpdateData(
            table: "StoreHours",
            keyColumn: "StoreHourId",
            keyValue: new Guid("25c68145-c5a3-438d-8cd4-acaec481384d"),
            columns: new[] { "CloseHour", "OpenHour" },
            values: new object[] { new DateTime(2022, 1, 22, 2, 0, 0, 0, DateTimeKind.Unspecified), new DateTime(2022, 1, 22, 17, 0, 0, 0, DateTimeKind.Unspecified) });

        migrationBuilder.UpdateData(
            table: "StoreHours",
            keyColumn: "StoreHourId",
            keyValue: new Guid("3ad512e7-1aca-4339-9341-9b4028bb0536"),
            columns: new[] { "CloseHour", "OpenHour" },
            values: new object[] { new DateTime(2022, 1, 22, 2, 0, 0, 0, DateTimeKind.Unspecified), new DateTime(2022, 1, 22, 17, 0, 0, 0, DateTimeKind.Unspecified) });

        migrationBuilder.UpdateData(
            table: "StoreHours",
            keyColumn: "StoreHourId",
            keyValue: new Guid("3f99ba19-f6e5-4e5d-afcf-d10f0ebc6009"),
            columns: new[] { "CloseHour", "OpenHour" },
            values: new object[] { new DateTime(2022, 1, 22, 22, 0, 0, 0, DateTimeKind.Unspecified), new DateTime(2022, 1, 22, 11, 30, 0, 0, DateTimeKind.Unspecified) });

        //
        // A bunch of other migrationBuilder.UpdateData( table: "StoreHours") I think because the Seed Data generates a new DateTime object everytime
        //

        migrationBuilder.CreateIndex(
            name: "IX_Places_SectorId",
            table: "Places",
            column: "SectorId");

        migrationBuilder.AddForeignKey(
            name: "FK_Places_Sectors_SectorId",
            table: "Places",
            column: "SectorId",
            principalTable: "Sectors",
            principalColumn: "SectorId",
            onDelete: ReferentialAction.Restrict);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Places_Sectors_SectorId",
            table: "Places");

        migrationBuilder.DropTable(
            name: "Sectors");

        migrationBuilder.DropIndex(
            name: "IX_Places_SectorId",
            table: "Places");

        migrationBuilder.DropColumn(
            name: "SectorId",
            table: "Places");

        migrationBuilder.UpdateData(
            table: "StoreHours",
            keyColumn: "StoreHourId",
            keyValue: new Guid("25c68145-c5a3-438d-8cd4-acaec481384d"),
            columns: new[] { "CloseHour", "OpenHour" },
            values: new object[] { new DateTime(2021, 11, 10, 2, 0, 0, 0, DateTimeKind.Unspecified), new DateTime(2021, 11, 10, 17, 0, 0, 0, DateTimeKind.Unspecified) });

        migrationBuilder.UpdateData(
            table: "StoreHours",
            keyColumn: "StoreHourId",
            keyValue: new Guid("3ad512e7-1aca-4339-9341-9b4028bb0536"),
            columns: new[] { "CloseHour", "OpenHour" },
            values: new object[] { new DateTime(2021, 11, 10, 2, 0, 0, 0, DateTimeKind.Unspecified), new DateTime(2021, 11, 10, 17, 0, 0, 0, DateTimeKind.Unspecified) });

        migrationBuilder.UpdateData(
            table: "StoreHours",
            keyColumn: "StoreHourId",
            keyValue: new Guid("3f99ba19-f6e5-4e5d-afcf-d10f0ebc6009"),
            columns: new[] { "CloseHour", "OpenHour" },
            values: new object[] { new DateTime(2021, 11, 10, 22, 0, 0, 0, DateTimeKind.Unspecified), new DateTime(2021, 11, 10, 11, 30, 0, 0, DateTimeKind.Unspecified) });

        //
        // A bunch of other migrationBuilder.UpdateData( table: "StoreHours") I think because the Seed Data generates a new DateTime object everytime
        //
    }
}

}

Commented:

Up: Deletes all the seed data

Down: Runs migrationBuilder.InsertData() for every table that was from the seed data.

using System;
using Microsoft.EntityFrameworkCore.Migrations;
using NetTopologySuite.Geometries;

namespace App.Persistence.Migrations
{
public partial class SeedDataCommented : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(
            table: "Addresses",
            keyColumn: "AddressId",
            keyValue: new Guid("16121702-bdf9-4444-a8b6-b08b33bb8eec"));

        migrationBuilder.DeleteData(
            table: "Addresses",
            keyColumn: "AddressId",
            keyValue: new Guid("2de4a3c8-a391-4f00-8d10-c06b5bdb7629"));

        migrationBuilder.DeleteData(
            table: "Categories",
            keyColumn: "CategoryId",
            keyValue: new Guid("066f36ea-bbbb-4243-e55d-08d91f53840e"));

        migrationBuilder.DeleteData(
            table: "Categories",
            keyColumn: "CategoryId",
            keyValue: new Guid("e53265e0-a52b-46f0-abb2-08d91f5485bc"));

        //
        // migrationBuilder.DeleteData() for every previously added Seed Data
        //

        migrationBuilder.AddColumn<Guid>(
            name: "SectorId",
            table: "Places",
            type: "uniqueidentifier",
            nullable: true);

        migrationBuilder.CreateTable(
            name: "Sectors",
            columns: table => new
            {
                SectorId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
                Name = table.Column<string>(type: "nvarchar(max)", nullable: false),
                City = table.Column<string>(type: "nvarchar(max)", nullable: false),
                CreatedDateTime = table.Column<DateTime>(type: "datetime2", nullable: false),
                CreatedBy = table.Column<string>(type: "nvarchar(max)", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Sectors", x => x.SectorId);
            });

        migrationBuilder.CreateIndex(
            name: "IX_Places_SectorId",
            table: "Places",
            column: "SectorId");

        migrationBuilder.AddForeignKey(
            name: "FK_Places_Sectors_SectorId",
            table: "Places",
            column: "SectorId",
            principalTable: "Sectors",
            principalColumn: "SectorId",
            onDelete: ReferentialAction.Restrict);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Places_Sectors_SectorId",
            table: "Places");

        migrationBuilder.DropTable(
            name: "Sectors");

        migrationBuilder.DropIndex(
            name: "IX_Places_SectorId",
            table: "Places");

        migrationBuilder.DropColumn(
            name: "SectorId",
            table: "Places");

        migrationBuilder.InsertData(
            table: "Categories",
            columns: new[] { "CategoryId", "CreatedBy", "CreatedDateTime", "ImageUrl", "Name" },
            values: new object[,]
            {
                // Seed Data
            });

        //
        // a bunch of other migrationBuilder.InsertData() for all the Seed Data
        //
    }
}

}

In both cases when I run Add-Migration it throws a warning message saying "may result in the loss of data"

How should I do it?

Upvotes: 0

Views: 1362

Answers (1)

Kiran Joshi
Kiran Joshi

Reputation: 1876

Yes definitely. The EF core has drawback in code first approach like whenever you change anything to the respestive table it will effected data too.

Suppose as an example.

I have table Test

TestId
TestName
TestAdd
Testxyz

I have data in table like below:

TestId TestName TestAdd Testxyz
1       a         b       c
2       b         c       d  
3       d         e       f
4       e         f       g

for above table if I remove the column from entity table so that will surely remove data also, but EF core will be give you a warning in Package manager console like There might be some changes in file will affected to database kindly review the migration file.

Upvotes: 1

Related Questions