Description:
When using EF6 6.9.8, the SQL generated by the DbSet<T>.AddOrUpdate method is invalid.
Consider you have a set of 5 things you want to ensure are in a table - like some seed data.
Consider you have a class that has the following
class Thing {
...
[ConcurrencyCheck]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public DateTime Version { get;set; }
...
public string Name { get;set; }
}
Now, consider you are in the Configuration.Seed(...) method with the following
var thing1 = new Thing { Name = "ABC" };
context.Things.AddOrUpdate(e => e.Name, thing1);
context.SaveChanges();//succeeds the very first time! Fails after that.
From the above, you'll find the following SQL was generated via the context.Database.Log delegate.
-- @gp1: '3/1/2016 5:17:11 PM' (Type = DateTime, IsNullable = false)
-- @gp2: '3/1/2016 5:17:11 PM' (Type = DateTime, IsNullable = false)
SET @gp1 = '3/1/2016 5:17:11 PM';
SET @gp2 = '3/1/2016 5:17:11 PM';
UPDATE `Things` SET WHERE (`ID` = 1) AND (`Version` = @gp1);
SELECT
`Version`
FROM `Things`
WHERE row_count() > 0 and (`ID` = 1) AND (`Version` = @gp2)
Notice the UPDATE statement has a SET that is empty and then a WHERE clause - ... SET WHERE ....
It is expected either
1) The UPDATE SET is populated correctly when there are changes
2) The UPDATE is skipped for no changes.
How to repeat:
Please see attached solution for something ready to go.
1) Create new CLI project targeting .net 4.6.1
2) Add/update Nuget Package MySql.Data.Entity v6.9.8, EntityFramework v6.1.3
2a) Setup connection string for your context
3) Add new class Thing
4) Add DbSet<Thing> to a new context pointing at your connection string
PM> Enable-Migrations
5) In Configuration Seed method, try to AddOrUpdate your things and also setup the MySQL bits
6)
PM> add-migration init
PM> update-database
7) Works
8)
PM> update-database
9) Fails
Suggested fix:
Given the AddOrUpdate method did in-fact select each item, skip the UPDATE when the SET clause is empty or there were no changes detected for the entity.
Description: When using EF6 6.9.8, the SQL generated by the DbSet<T>.AddOrUpdate method is invalid. Consider you have a set of 5 things you want to ensure are in a table - like some seed data. Consider you have a class that has the following class Thing { ... [ConcurrencyCheck] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public DateTime Version { get;set; } ... public string Name { get;set; } } Now, consider you are in the Configuration.Seed(...) method with the following var thing1 = new Thing { Name = "ABC" }; context.Things.AddOrUpdate(e => e.Name, thing1); context.SaveChanges();//succeeds the very first time! Fails after that. From the above, you'll find the following SQL was generated via the context.Database.Log delegate. -- @gp1: '3/1/2016 5:17:11 PM' (Type = DateTime, IsNullable = false) -- @gp2: '3/1/2016 5:17:11 PM' (Type = DateTime, IsNullable = false) SET @gp1 = '3/1/2016 5:17:11 PM'; SET @gp2 = '3/1/2016 5:17:11 PM'; UPDATE `Things` SET WHERE (`ID` = 1) AND (`Version` = @gp1); SELECT `Version` FROM `Things` WHERE row_count() > 0 and (`ID` = 1) AND (`Version` = @gp2) Notice the UPDATE statement has a SET that is empty and then a WHERE clause - ... SET WHERE .... It is expected either 1) The UPDATE SET is populated correctly when there are changes 2) The UPDATE is skipped for no changes. How to repeat: Please see attached solution for something ready to go. 1) Create new CLI project targeting .net 4.6.1 2) Add/update Nuget Package MySql.Data.Entity v6.9.8, EntityFramework v6.1.3 2a) Setup connection string for your context 3) Add new class Thing 4) Add DbSet<Thing> to a new context pointing at your connection string PM> Enable-Migrations 5) In Configuration Seed method, try to AddOrUpdate your things and also setup the MySQL bits 6) PM> add-migration init PM> update-database 7) Works 8) PM> update-database 9) Fails Suggested fix: Given the AddOrUpdate method did in-fact select each item, skip the UPDATE when the SET clause is empty or there were no changes detected for the entity.