EF 4.3 added a new extension method for DbSet called AddOrUpdate.
It is meant for use with seeding data during migrations.
It looks like a nice method to add into your apps but that’s not it’s purpose.
The job of AddOrUpdate is to ensure that you don’t create duplicates when you seed data during development.
First, it will execute a query in your database looking for a record where whatever you supplied as a key (first parameter) matches the mapped column value (or values) supplied in the AddOrUpdate. So this is a little loosey-goosey for matching but perfectly fine for seeding design time data.
More importantly, if a match is found then the update will update all and null out any that weren’t in your AddOrUpdate.
For example, if your type is:
Person
- Id
- Name
- UserName
- CreateDate
- Bio
And there is a row in the database with
5, GiantPuppy, Sampson, 1/1/2011, I drool, [email protected]
And your AddOrUpdate has
context.Aliases.AddOrUpdate(
a => a.Name,
new Alias { Name = "GiantPuppy", UserName = "Sampson", CreateDate = DateTime.Now},
new Alias { Name = "JulieLerman", UserName = "Julie", CreateDate = DateTime.Now}
);
The method will look for a row where whatever column maps to the Name property has the value GiantPuppy. It will then update all of the mapped properties.
- Name: GiantPuppy
- UserName: Sampson
- CreateDate: whatever the time is
- Bio: null (because it wasn’t provided)
- Email: null (because it wasn’t provided)
This is about the same as doing an update in a disconnected app where you attach an object, mark it as modified and call SaveChanges. I see people do this in MVC apps all the time. If there are properties that were left null in the object because you didn’t care about them in this particular case, the database values will get overwritten with null. Except this is worse because it’s not leaning on your entity key (e.g. Id mapped to primary key).
So be careful out there. Me? I’m planning to use AddOrUpdate only for seeding migrations!
Update: I thought it would be useful to answer Livingston’s question here in the main post.
The update will update all of the properties it sees as changed.
It queries for the match (name==GiantPuppy), compares the values in the AddOrUpdate command to the results and updates anything that’s different.
So the results of the query would return the Id field for Giantpuppy. The migration will see that GiantPuppy’s seed Bioi(null) is different than the database Bio (“I drool”), that the email (null) is different than the database email and that the createdate value is also different.
Here is the update command it sends to the database:
exec sp_executesql N’update [dbo].[Aliases]
set [Email] = null, [Bio] = null, [CreateDate] = @0
where ([Id] = @1)
from [dbo].[Aliases]
where @@ROWCOUNT > 0 and [Id] = @1′,
N’@0 datetime2(7),@1 int’,@0=’2012-02-29 20:57:23.2779868′,@1=3
It’s updating everything that’s different…. email, bio and CreateDate.
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!
So it will update the CreateDate, but not the Name or userName??
It is an example of a bad (as in unintuitive) API naming. This method should be called differently such as Migrate. Better yet, it should not be directly under DbContext but under a descriptive property such DbContext.Migrations.AddOrUpdate(obj).
To make things worse, NHibernate has a method called SaveOrUpdate that does exactly what users would expect.
@Livingston – updated post to answer your question
@Dmitry – I was thinking the same thing today, although I don’t knwo what it should be called. AddOrReplaceButOnlyWhenMigrating(). I’m going to dig into the internal code again to see if somehow it does protect us from using outside of migrations.
@dmitry … looking in reflector I can’t see that it’s preventing it. But do note their description:
"Adds or updates entities by key when SaveChanges is called. Equivalent to an "upsert" operation from database terminology. This method can useful when seeding data using Migrations. "
Perhaps my concern is more than it needs to be, but I can’t help worrying about how easily this can be accidentally misused.
I just started using this method and it’s working fine for my purpose. Specifically, I’m pulling data from an external API and then adding or updating it in my local DB. The data in my DB is never modified, it is always a mirror of the external data.
The method uses the Id column just fine and I’ve seen no other issues.
@Brian, that sounds safe. As long as you know what to expect from the method!
What do you supply for the expression if your key is a composite key? For example, I have a table called "Aliases" and it has columns Username, Name, & Value. Username and Name together are a composite key. There can be multiple records with the same name and multiple with the same username, but no duplicate records with the same username AND name.
Also, what happens if you don’t supply that first expression and use the first overload of AddOrUpate?
Yes – good question, Alex Ford. I would like to know how can I use this method when I have composite key?
P.S. Good course at pluralsight, Julie!
Well, after all, this method *is* couched away in the System.Data.Entity.Migrations namespace. This is not an accident.
@GooDVim: stackoverflow.com/…/how-to-seed-dat
@Dmitry:
I do not agree at all: As Ryan remarks, the AddOrUpdate is inside the Migrations namespace and is therefore logically coupled to this feature subset.
Thanks for the heads up. It does what you would expect for seeding data but AddOrReplace would have been a better method name.
Julie,
Would you post an example of how you would see a database that has a foreign key constraint?
For example, I have a product that has a categoryID to a foreign key to another table for the category.
The only problem i’ve found with AddOrUpdate, is that it doesn’t update Navigation Properties
Lets say you have a Category and a Post object (and you didn’t create the Foreign Key yourself)
class Post { public Category Cat… }
if you then want to change category for the Post, you can’t use AddOrUpdate !!
If I implement join index in EF6, and how can I use the AddOrUpdate(p=>{return p.c1 ,p.c2}) , or some fashion like this?