I am working in a company where a project has been brought in house because the external team tasks with building the system have not done a great job and thus been fired.
An issue that I have is that we have an existing database, where some tables where seed data should have been done through a migrationBuilder looks to have just been inserted via SSMS \ SQL Server Insert scripts.
as a result I get an error like this when adding seeding scripts so that when we spin up an new isntance of the database this works, but on an existing environment such as dev, test and staging it does not.
Violation of PRIMARY KEY constraint 'PK_xxxx'. Cannot insert duplicate key in object 'forms.AnswerTypes'. The duplicate key value is (1)
The only potential way I have found around this is from this link here
But hope that there are better ways that this can be acheived as I cannot delete the data as part of the migration because its already used and being referenced by other tables so the ripple effect is wide ranging.
An example of the sort of data that I am trying to seed is like this;
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.InsertData(
schema: "forms",
table: "Rules",
columns: new[] { "RuleId", "Rules" },
values: new object[] { 1, "Any" });
migrationBuilder.InsertData(
schema: "forms",
table: "Rules",
columns: new[] { "RuleId", "Rules" },
values: new object[] { 2, "All" });
}
So, the question is, is it possible with migrationBuilder to check is data exists prior to inserting?
You can write custom SQL and stuff and add it to your migration script;
https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/operations
There's also .Sql():
migrationBuilder.Sql(
@"
UPDATE Customer
SET Name = FirstName + ' ' + LastName;
");
Which you could use. My team and i use EF6 still but we use the same principle. Our migrationscript sometimes have additional SQL statements to move any data around or generate default data when adding a column etc.