CODEWITHSUNDEEP
c#entity-framework
Matthew
Matthew

Reputation: 4339

How to update unique keys in Entity Framework

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?

Upvotes: 1

Views: 1704

Answers (2)

grudolf
grudolf

Reputation: 1794

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
}

Upvotes: 1

Mithgroth
Mithgroth

Reputation: 1212

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.

Upvotes: 0

Related Questions