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, gp@notarealdomain.com
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.
