Entity framework core, code first migration with data migration

c# ef-migrations entity-framework-core

Question

I am trying to use EF Core code first migrations to de-normalise an existing table.

I have en existing table LoginEvent which data looks like this:

╔═════════════════════════════════════════════════╗
â•‘                   LoginEvent                    â•‘
╠════╦══════════╦═══════════╦════════════╦════════╣
â•‘ Id â•‘ VenueRef â•‘ VenueName â•‘ OccurredAt â•‘ UserId â•‘
╠════╬══════════╬═══════════╬════════════╬════════╣
â•‘ 1  â•‘ ven01    â•‘ Venue 1   â•‘ 2018-01-29 â•‘ 5      â•‘
╠════╬══════════╬═══════════╬════════════╬════════╣
â•‘ 2  â•‘ ven02    â•‘ Venue 2   â•‘ 2018-01-30 â•‘ 7      â•‘
╠════╬══════════╬═══════════╬════════════╬════════╣
â•‘ 3  â•‘ ven01    â•‘ Venue 1   â•‘ 2018-02-01 â•‘ 9      â•‘
╚════╩══════════╩═══════════╩════════════╩════════╝
public class LoginEvent
{
    [Key]
    public int Id { get; set; }
    public string VenueRef { get; set; }
    public string VenueName { get; set; }
    public DateTime OccurredAt { get; set; }
    public User User { get; set; }
}

And I want to normalise this into two tables: LoginEvent and Venue, like so:

╔═════════════════════════════════════╗
â•‘             LoginEvent              â•‘
╠════╦══════════╦════════════╦════════╣
â•‘ Id â•‘ VenueRef â•‘ OccurredAt â•‘ UserId â•‘
╠════╬══════════╬════════════╬════════╣
â•‘ 1  â•‘ ven01    â•‘ 2018-01-29 â•‘ 5      â•‘
╠════╬══════════╬════════════╬════════╣
â•‘ 2  â•‘ ven02    â•‘ 2018-01-30 â•‘ 7      â•‘
╠════╬══════════╬════════════╬════════╣
â•‘ 3  â•‘ ven01    â•‘ 2018-02-01 â•‘ 9      â•‘
╚════╩══════════╩════════════╩════════╝
╔══════════════════════╗
â•‘        Venue         â•‘
╠══════════╦═══════════╣
â•‘ VenueRef â•‘ VenueName â•‘
╠══════════╬═══════════╣
â•‘ ven01    â•‘ Venue 1   â•‘
╠══════════╬═══════════╣
â•‘ ven02    â•‘ Venue 2   â•‘
╚══════════╩═══════════╝

Now, I have done this by adding a new Venue domain object and having the LoginEvent reference it, like so:

public class LoginEvent
{
    [Key]
    public int Id { get; set; }    
    public string VenueRef { get; set; }
    public DateTime OccurredAt { get; set; }
    public Venue Venue { get; set; }
    public User User { get; set; }
}
public class Venue
{
    [Key]
    public string VenueRef { get; set; }
    public string VenueName { get; set; }
}

I've then created a migration, which (correctly):

  1. Creates the new Venue table
  2. Drops the VenueName column
  3. Sets up the foreign key constraint between the two

However, what I need to be able to do is run a data migration between steps 1 and 2 so that the existing Venues are in the new table before dropping the column and before setting up the constraint (otherwise, I'll lose data and the constraint fails as I don't have associated Venues).

I'd like to run something like this as my data migration:

INSERT INTO Venue SELECT DISTINCT VenueRef, VenueName FROM LoginEvent

How should I do this?

1
7
2/5/2018 1:04:04 PM

Accepted Answer

You can execute any SQL using migrationBuilder.Sql(theSqlString).

In your case

migrationBuilder.Sql("INSERT INTO Venue SELECT DISTINCT VenueRef, VenueName FROM LoginEvent");

Run this in the migration after creating the new table Venue and before dropping the old column VenueName.

See also Custom Migrations Operations.

7
2/5/2018 1:34:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow