I have an issue with EntityFramework and "set transaction isolation level read uncommitted"
We have changed EF to call a base class (by not using the edmx , this is possible) In the base class we have overridden the createQuery method to include a call to a SSDL function
<Function Name="Uncommitted" IsComposable="false">
<CommandText>
set transaction isolation level read uncommitted
</CommandText>
</Function>
The reason we do this is so emulate (nolocks) on our sql and thereby help performance of our ASP Web Pages.
This however has brough up a problem.
we have an admin function that has a problem. (DeliveryMethods)
I have a attribute table which is keyed by deliveryMethod and attribute type.
Say I have RowA on that table.
I change and save a value on RowA using an enitiy object in the entityframework.
I then move off to another web page and copy RowA, again using an entity object in entityframework. The problem is the copy of RowA contains the original values and not the changed ones.
If I do an IIS reset between the amendments and the copy then the new RowA has the changed values so it works ok.
looking at the SQL profiler - The update to the original RowA is exec sp_executesql N'update [dbo].[DeliveryMethodsRuleAttributes] set [IntAttribute] = @0, [LastUpdated] = @1, [LastUpdatedBy] = @2 where ([Id] = @3) ',N'@0 int,@1 datetime,@2 nvarchar(4),@3 uniqueidentifier',@0=40000,@1='2009-08-10 17:39:20:383',@2=N'mken',@3='2B60D845-411B-4337-A887-B6BB0F9B9309'
I changed @0 from 30000 to 40000.
The copy process does not issue an SQL Request , so there is no SQL read. The Copy insert is.
exec sp_executesql N'insert [dbo].[DeliveryMethodsRuleAttributes]([Id], [DeliveryMethodRuleId], [DeliveryMethodId], [AttributeName], [DateTimeAttribute], [IntAttribute], [DecimalAttribute], [TextAttribute], [Created], [CreatedBy], [LastUpdated], [LastUpdatedBy]) values (@0, @1, @2, @3, null, @4, null, null, @5, @6, @7, @8) ',N'@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier,@3 nvarchar(14),@4 int,@5 datetime,@6 nvarchar(4),@7 datetime,@8 nvarchar(4)',@0='0264FECD-5EE4-49D3-9CE0-72BAAC11DAA6',@1='722456F0-ACE1-44B0-9568-A03CDD4453C5',@2='053C32B3-808C-4375-9826-02BDC55A8791',@3=N'PriceInCentsTo',@4=30000,@5='2009-08-10 17:42:03:780',@6=N'mken',@7='2009-08-10 17:42:03:780',@8=N'mken'
@4 is set to 30000, which was the original RowA value.
If I deactivate the "set transaction" then this problem does not happen. Displosing and Closing the EF after the update of RowA has no effect.
Based on the SQL Profile, my theory is that the "uncommited" value is being held in the entityframework and/or underlying ado libraries and this cache is "feeding" the copy of RowA.
My Question is - How do I reset this cache after an update so that the next read uses the SQL DB?