mercredi 2 décembre 2020

UWP Entity framework update your DB schema with a new column [UWP][C#][Database]

The point of this article is to go over the different steps that were needed to add a new column to one of my existing table.  This table was already used by my users using my application called My Stocks Alerts & Charts.

To add a new column or table to your existing DB you are going to need to create 3 new files.  The first new class will be called MyAlertQuotesModelSnapshot which inherit from ModelSnapshot and have the annotation of DbContext, this will hold the new table schema model of our updated table.

Next, we will create a class called MyFirstMigration this will also hold you new table schema model, this class will not inherit from anything but will have the annotations of DbContext and Migration.

Lastly the most important part we are going to create the class that will add the column to our table this will be called MyFirstMigration will inherit from Migration and will allow us to add our new column to our table by using migrationBuilder.AddColumn as follows:

migrationBuilder.AddColumn<string>(
                            name: "ExtraColumn",
                            table: "MyAlertQuotes",
                            nullable: true);

If you had wanted to add a new table that was called MyAlertQuotes we would have used migrationBuilder.CreateTable as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
protected override void Up(MigrationBuilder migrationBuilder)
{         

	migrationBuilder.CreateTable(
		name: "MyAlertQuotes",
		columns: table => new
		{
			Id = table.Column<Guid>(nullable: false),
			SymbolId = table.Column<string>(nullable: false),
			FullName = table.Column<string>(nullable: false),
			Currency = table.Column<string>(nullable: false),
			ExtraColumn = table.Column<string>(nullable: false),
			 
		},
		constraints: table =>
		{
			table.PrimaryKey("PK_MyAlertQuotes", x => x.Id);
		});
}

Which would have added a new table to our DbSchema. 

One last step, you will also have a class that inherits from DbContext, make you that you add check on initialization to make sure your users have migrated to the new Db schema

1
2
3
4
5
6
7
 public static void CheckMigrations()
{
	using (var db = new LocalStorageContext())
	{
		db.Database.Migrate();
	}
}

Personally in my App.xaml.cs I check to make sure that users have updated to my new Db Schema.

Here are my full classes:

20201101_MyFirstMigration.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using MyPersonalStocks.Helpers;
using System;

namespace MyPersonalStocks.Common.DataBase.Migrations
{
    public partial class MyFirstMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {

            migrationBuilder.AddColumn<string>(
                            name: "ExtraColumn",
                            table: "MyAlertQuotes",
                            nullable: true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            //migrationBuilder.DropColumn(
            //               name: "ExtraColumn",
            //               table: "MyAlertQuotes"
            //               );
        }
    }
}


20201101_MyFirstMigration.design.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using MyPersonalStocks.Helpers;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyPersonalStocks.Common.DataBase.Migrations
{
    [DbContext(typeof(LocalStorageContext))]
    [Migration("20201101_MyFirstMigration")]
    partial class MyFirstMigration
    {
        protected override void BuildTargetModel(ModelBuilder modelBuilder)
        {
            modelBuilder
                .HasAnnotation("ProductVersion", "1.0.4");

            modelBuilder.Entity("MyPersonalStocks.Model.DbModel.AlertQuotesDb", b =>
            {
                b.Property<Guid>("Id");

                b.Property<string>("SymbolId");
                b.Property<string>("FullName");
                b.Property<string>("Currency");
                b.Property<string>("ExtraColumn");

                b.HasKey("Id");

                b.ToTable("MyAlertQuotes");
            });
        }
    }
}


MyAlertQuotesModelSnapshot.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using MyPersonalStocks.Helpers;
using MyPersonalStocks.Model.DbModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyPersonalStocks.Common.DataBase.Migrations
{


    [DbContext(typeof(LocalStorageContext))]
    partial class MyAlertQuotesModelSnapshot : ModelSnapshot
    {
        protected override void BuildModel(ModelBuilder modelBuilder)
        {
            modelBuilder
                .HasAnnotation("ProductVersion", "1.0.4");

            modelBuilder.Entity("MyPersonalStocks.Model.DbModel.AlertQuotesDb", b =>
            {
                b.Property<Guid>("Id");

                b.Property<string>("SymbolId");
                b.Property<string>("FullName");
                b.Property<string>("Currency");
                b.Property<string>("ExtraColumn");
                b.HasKey("Id");

                b.ToTable("MyAlertQuotes");
            });
        }
    }

}