CODEWITHSUNDEEP
sql-serverentity-framework-4
Jonas Sourlier
Jonas Sourlier

Reputation: 14465

Weird SQL statement issued by EF4

I am profiling an Entity Framework v4.0 application and noticed the following SQL:

exec sp_executesql N'declare @p int
  update [dbo].[Table]
  set @p = 0
  where ((([Id] = @0) and ([Property1] = @1)) and ([Property2] = @2))
  ',N'@0 uniqueidentifier,@1 int,@2 int',@0='[some-guid]',@1=0,@2='0'

I really wonder what this piece of code really does. I mean, I don't understand the SQLish sense in it. It does not really update the table, it just sets the @p parameter to 0, if the table has at least one row that matches the where clause. But @p is not used elsewhere. Why would EF do this?

Thank you for any insights!

Edit:

The statement gets issued among other statements in a

Context.SaveChanges();

call. The effort to isolate it would be considerable.

Maybe it is important to mention that the thing happens inside a transaction with its isolation level set to 'SERIALIZABLE'.

Upvotes: 3

Views: 114

Answers (2)

aledeniz
aledeniz

Reputation: 463

Just out of my mind, I have a couple of possible conjectures:

  • It is trying to count the number of records affected by an update statement, and based on that, apply one of many different strategies. You may prove or disprove this conjecture increasing or decreasing the number of affected records, and seeing if there is any change in the statements after this.
  • In serializable isolation level, you get read and write locks in place nonetheless, but perhaps this is a way to avoid phantom reads in other scenarios. The table at the end of this document says that in SS 2008 phantom reads should be impossible in serializable isolation level, but perhaps the EF guys are using the same code for other isolation levels where phantom reads can happen (RU, RC and RR).

The latter is harder to disprove, and both may well end up being a bug, you may have to contact someone at the EF dev team if it is causing issues in your scenario (performance degradation with big datasets, I'd imagine).

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453658

It does seem pointless.

Assuming the query has any rationale for being sent at all the only thing I can think is that it is looking to get the "x rows affected" message.

However issuing an update statement to get this rather than just doing a straight forward SELECT COUNT() is sub optimal in terms of locking and logging I would have thought.

Upvotes: 3

Related Questions