CODEWITHSUNDEEP
entity-frameworkasp.net-coreentity-framework-migrations
DreamingOfSleep
DreamingOfSleep

Reputation: 1458

EF Core - How do I add a non-nullable foreign key property?

ASP.NET ASP Core 2.2 and EF in VS2019.

I have a Donation class...

public class Donation {
  public int Id { get; set; }
  // other properties...
}

Up until now, all donations have been assumed to be in GBP. We want to add support for other currencies, and so want to add a Currency class (which would end up as a Currencies table), and have a foreign key link from Donation to Currency. I added the following code to Donation...

  public int CurrencyID { get; set; }

  [ForeignKey(nameof(CurrencyID))]
  public virtual Currency Currency { get; set; }

...and the following class...

public class Currency {
  public int Id { get; set; }
  public string Code { get; set; }
  public string Symbol { get; set; }
  public virtual IEnumerable<Donation> Donations { get; set; }
}

I also populated the Currencies table...

  builder.Entity<Currency>().HasData(new List<Currency> {
    new Currency { Id = (int)SupportedCurrencies.GBP, Code="GBP", Symbol = "£" },
    new Currency { Id = (int)SupportedCurrencies.EUR, Code="EUR", Symbol = "€" },
    new Currency { Id = (int)SupportedCurrencies.USD, Code="USD", Symbol = "$" },
    //...
  });

However, this gave an error when updating the database, as it was trying to set the value of CurrencyID to null.

I tried adding two migrations, one with a nullable CurrencyID property, and a second where it was non-null, hoping that the database update might run the code to populate the Currencies table in between, but this didn't work either. I got the same error about not being able to set the value of the CurrencyID property to null as it was non-nullable.

Pre-Core, I would have added a Currencies table, then in an SQL script, populated it, then added the column to the Donations table, setting the CurrencyID property to be the ID of GBP for all existing donations.

To clarify, the CurrencyID property should be non-nullable, as every donation must be in some currency.

How do I do this with EF Core migrations?

Someone asked the same question some years ago, but didn't get an answer.

Upvotes: 6

Views: 4320

Answers (2)

DreamingOfSleep
DreamingOfSleep

Reputation: 1458

Turns out that I had missed setting the default value for the CurrencyID property. Kind of obvious now I see it.

I added the following...

  builder.Entity<Donation>()
    .Property(p => p.CurrencyID)
    .HasDefaultValue(1);

...and the error went away.

On a side note, this gave me a different error about causing cycles or multiple cascade paths. I got around this by modifying the Up method in the migration, setting the onDelete to Restrict rather than the default Default...

        migrationBuilder.AddForeignKey(
            name: "FK_Donations_Currencies_CurrencyID",
            table: "Donations",
            column: "CurrencyID",
            principalTable: "Currencies",
            principalColumn: "Id",
            onDelete: ReferentialAction.Restrict);

Upvotes: 2

ESG
ESG

Reputation: 9435

There's a few different ways you could tackle it.

1)

  • Generate your migration with your property being non-nullable, and including the data as you are now, but modify the migration so it adds the column as a nullable, and pre-populate the data and alter it later to become non-nullable.

2)

  • Generate a migration with the property being nullable, and including the data as you are now
  • Make the property non-nullable and generate a second migration, but at the top, manually do your update via SQL.

Upvotes: 1

Related Questions