How to update unique keys in Entity Framework

Question

In a previous question I presented these models:

public class Calendar 
{
    public int ID { get; set; }
    public ICollection<Day> Days { get; set; }
}

public class Day
{
    public int ID { get; set; }
    public DateTime Date { get; set; }
    public int CalendarID { get; set; }
}

There is a uniqueness constraint so that you can't have more than one Day with the same Date and CalendarID.

My question now is what if I want to move all days one day into the future (or whatever). The easiest code is just a for loop like

for(Day day in days) {
    day.Date = day.Date.AddDays(1);
    db.Entry(day).State = EntityState.Modified;
}
await db.SaveChangesAsync();

This will fail, however, because it thinks you are creating duplicates of some of the dates, even though once they're all updated it will work out.

Calling SaveChangesAsync after each day (assuming you process the days in the correct order) would work, but seems massively inefficient for this basic task.

An alternative to updating the Date would be to transfer all the other data of each day to the next one, but this also seems inefficient and could in some cases be undesirable because it means that data is dissociated from the Day's primary key value.

Is there a way to update all the dates while keeping the uniqueness constraint?

Can you just update them in reverse, so that you essentially create a gap between them, rather than overwriting the next one in the sequence and violating the constraint? — Bob G, Dec 14 '17 at 16:28
set day 01 to lastDay + 1, same result, 1 update, fastest mode. — Celso Lívero, Dec 14 '17 at 16:30
I tried updating them in reverse order. It makes no difference, unless you call `SaveChangesAsync` during every loop — Matthew, Dec 14 '17 at 16:31
@CelsoLívero The models are merely illustrative. In real world there's other properties on the days so making day 1 the last day doesn't work. Need to preserve the order, just move them all ahead one day. — Matthew, Dec 14 '17 at 16:33
I know, it was just a joke, not very good I know. The best thing to do in this case (and performance terms) would be to create a store procedure in the database to perform all that is desired, all the functions that are necessary related to that change and would make the EF execute this procedure, that would transfer all work for the server that was probably scaled to perform this type of work — Celso Lívero, Dec 14 '17 at 16:45

score 1 · Accepted Answer · answered Dec 14 '17 at 20:44

The number of SQL UPDATE statements won't change if you call SaveChanges() for each record instead of calling it only once, but at least you'll get the correct order. There's some overhead because of state cleaning and connection management but it's not massively inefficient.

If date shifting is an isolated business transaction you could use a simpler solution instead of fighting with ORM - call a stored procedure or execute SQL directly with something similar to:

var sql = "UPDATE d SET Date = DATEADD(d, 1, Date) FROM (SELECT * FROM Day WHERE CalendarID=@calendarId ORDER BY Date DESC) d";
var updateCnt = db.Database.ExecuteSqlCommand(sql, new SqlParameter("@calendarId", calendar.Id);
if (updateCnt != days.Count)
{
    //oops
}

score 0 · Answer 2 · answered Dec 14 '17 at 19:36

One of the many possible solutions is removing all the records before you do the update.

You can first get your days, store them in memory.

var days = db.Day.Tolist();

Truncate the table, so they won't collide with the new list coming:

db.ExecuteCommand("TRUNCATE TABLE Day");

Do your stuff:

foreach(var day in days)
{
    day.Date=day.Date.AddDays(1);
}

Insert your new list. Now you should be able to save it:

db.SaveChanges();

This should be efficient enough since the quickest way to wipe data is to truncate, and your day objects are child objects.

HOWEVER

If a property is changing a lot, probably it's not a good idea to make it a primary key.

If you find yourself in a conflict with fundamentals, it's quite possible that you made an architectural mistake.

I strongly recommend you to change your primary key to something else, you can even roll a uniqueidentifier column to store Id.

How to update unique keys in Entity Framework

2 Answers2