Conditionally inserting data in a code-first migration with Entity Framework

Question

I am trying my 2nd migration using the Entity Framework, and so far I generated code like this

namespace Entity.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class LedgerDisplayOrder : DbMigration
    {
        public override void Up()
        {
            AddColumn("Ledgers", "DisplayOrder", c => c.Int());
            // Hic sunt leones
        }

        public override void Down()
        {
            DropColumn("Ledgers", "DisplayOrder");
        }
    }
}

The logic how to fill this column is actually pretty simple, in SQL it would be something like the following:

ALTER TABLE [Ledgers] ADD [DisplayOrder] INT NULL;

UPDATE [Ledgers] 
SET DisplayOrder = 10 
WHERE [Id] = 26 OR [Id] = 27 OR [Id] = 23; 

UPDATE [Ledgers] 
SET DisplayOrder = 20 
WHERE [Id] = 29 OR [Id] = 9; 

UPDATE [Ledgers] 
SET DisplayOrder = 30 
WHERE [Id] = 28 OR [Id] = 23;

In other words, the migration should automatically be populated during the migration, but I am not sure what to put at // hic sunt leones to achieve just that. I read that I should use the migrationBuilder class, probably something like

migrationBuilder.InsertData(table: "ledger", column: "DisplayOrder", value: "10");

but how to implement the WHERE clause? And how do I load that class? My Visual Studio's Quick Actions and Refactoring keeps suggesting to implement a new class, so do I have to install something first?

Any help is appreciated!

References: all I found so far is about setting default values for a new column. That's not what I am after.

Miamy · Accepted Answer · 2020-01-20 13:29:33Z

2

You can use migrationBuilder.Sql command to execute pure SQL-expressions during migration:

Instead of InsertData you could use

Sql("UPDATE [Ledgers] 
     SET DisplayOrder = 10 
     WHERE [Id] = 26 OR [Id] = 27 OR [Id] = 23; ");

This is short for migrationBuilder.Sql which can be loaded via

using Microsoft.EntityFrameworkCore.Migrations;

if you are using EF Core or (in your case) by

using System.Data.Entity.Migrations;

if you are using EntityFramework.

edited Jan 20, 2020 at 13:29

answered Jan 20, 2020 at 12:25

Miamy

2,2973 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Miamy Over a year ago

You should add using Microsoft.EntityFrameworkCore.Migrations; to your header if you are using EF Core or using System.Data.Entity.Migrations; if you are using EF.

Miamy Over a year ago

Yes, I took my example from EFCore project, where migrationBuilder presents as an parameter in Up()/Down() functions. Please sorry for my inaccuracy.

Collectives™ on Stack Overflow

Conditionally inserting data in a code-first migration with Entity Framework

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related