Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EF4 - update [Table] set @p = 0 where

While going through SQL profiler, I noticed the following query generated by EF4.

exec sp_executesql N'declare @p int
update [dbo].[User]
set @p = 0
where (([UserID] = @0) and ([RowVersion] = @1))
select [RowVersion]
from [dbo].[User]
where @@ROWCOUNT > 0 and [UserID] = @0',N'@0 int,@1 binary(8)',@0=1,@1=0x000000000042DDCD

I am not sure why EF4 generates this while I am actually not updating any columns of the User table in that UnitOfWork. Running this query updates the RowVersion column (timestamp datatype) which leads to OptimisticConcurrencyException in the next UnitOfWork.

A quick googling led me to this link, which confirms that others have also run into this scenario without finding a solution yet.

Would greatly appreciate any pointers.

Edit: A sample code to replicate the issue.

enter image description here

User and Session tables have a foreign key relationship. Also, in EF4 I have set the "Concurrency Mode" property of RowVersion columns of both entities to Fixed.

Below is a sample method to replicate the scenario.

 private static void UpdateSession()
    {
        using (var context = new TestEntities())
        {
            context.ContextOptions.ProxyCreationEnabled = false;

            var session = context.Users.Include("Sessions").First().Sessions.First();
            session.LastActivityTime = DateTime.Now;

            context.ApplyCurrentValues("Sessions", session);

            context.SaveChanges();
        }
    }

I see from Sql profiler the following queries being genrated by EF4.

exec sp_executesql N'update [dbo].[Session]
set [LastActivityTime] = @0
where (([SessionID] = @1) and ([RowVersion] = @2))
select [RowVersion]
from [dbo].[Session]
where @@ROWCOUNT > 0 and [SessionID] = @1',N'@0 datetime2(7),@1 int,@2 binary(8)',@0='2011-06-20 09:43:30.6919628',@1=1,@2=0x00000000000007D7

And the next query is weird.

    exec sp_executesql N'declare @p int
update [dbo].[User]
set @p = 0
where (([UserID] = @0) and ([RowVersion] = @1))
select [RowVersion]
from [dbo].[User]
where @@ROWCOUNT > 0 and [UserID] = @0',N'@0 int,@1 binary(8)',@0=1,@1=0x00000000000007D3
like image 957
muruge Avatar asked Jun 03 '11 20:06

muruge


Video Answer


1 Answers

Not sure if this is still problem for you but here is the hotfix by MS http://support.microsoft.com/kb/2390624

like image 117
Kris Ivanov Avatar answered Oct 27 '22 09:10

Kris Ivanov