Kratos Kratos - 1 month ago 11
C# Question

Why do I get different results in Entity Framework and SQL Server transaction?

In my EF code and SQL Server, I try to insert a user data while this user is not exist, but EF inserted it 1000 times, just 1 time in SQL Server.

EF code:

static void Main(string[] args)
{
using (var db = new MyDbContext())
{
for (var i = 0; i < 1000; i++)
{
var count = db.Users.Count(f => f.Name == "Test");
if (count > 0) continue;

db.Users.Add(new User
{
Name = "Test",
Gender = "Male",
Phone = "1111111111",
CreateTime = DateTime.Now
});
}

try
{
db.SaveChanges();
//1000 rows effected
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

Console.ReadKey();
}


T-SQL code:

declare @i int = 0
begin tran

while(@i < 1000)
begin
if not exists (select 1 from [dbo].[User] where Name = 'Test')
insert into [dbo].[User] values('Test','Male','1111111111',getdate())
set @i = @i + 1
end

if(@@ERROR > 0)
rollback tran
else
commit tran


Even if I use a transaction wrap the EF code (but call
SaveChanges
once), the result is same as 1000 rows affected.

But when I use this code, the result is the same as in SQL Server:

using (var db = new MyDbContext())
{
using (var trans = db.Database.BeginTransaction(ReadCommitted))
{
for (var i = 0; i < 1000; i++)
{
var count = db.Users.Count(f => f.Name == "Test");
if (count > 0) continue;

db.Users.Add(new User
{
Name = "Test",
Gender = "Male",
Phone = "1111111111",
CreateTime = DateTime.Now
});

//1 row effected as sql does
db.SaveChanges();
}

try
{
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Console.WriteLine(ex.Message);
}
}
}


Please tell me why transaction is different between EF and SQL

usr usr
Answer

db.Users.Add does not insert. It makes for insertion when SaveChanges is called (what did you think SaveChanges did or why it was required if not for writing out changes that were previously not written?). That's why db.Users.Count(f => f.Name == "Test") always returns zero.

This has nothing to do with transactions.

(Btw, you probably should use Any instead of Count here).