KeyTable being updated when running linq query

Hello,

I have written the following code. I am using sqlserver 2005 connection with a key table sequence. When I execute the code below is generates the following sql. My question is why is a query updating the key table? Thanks.

 code

Dim query As IEnumerable(Of ContentTab) = uow.ContentTabs.Where(Function(tab) tab.TabGroup = "default").OrderBy(Function(tab) tab.Seq)

For Each dt As ContentTab In query

Next

 End Using

That shouldn't be happening and I can't reproduce it with the code you've shown.  The KeyTable should not be accessed unless an entity is being added to the unit of work (and sometimes not even then).

My guess is that either something else during the page cycle is doing an Add, either to the unit of work or to a child collection of ContentTab, or the ContentTab constructor (or some other method) is internally adding something to a child collection of the ContentTab.

Could you try stepping over the code in a debugger and let us know on which line the KeyTable access occurs?  If it happens on the For Each line, could you do a Step Into and see if that helps pin it down?  Thanks!

 I have stepped through the code and it appears to be happening at the foreach line. This is being executed a project that will become the framework for a new applications so this is the only lightspeed data access code present in the project. I tried stepping into the foreach statement but it did not do anything. Below is the debug output for the run. Thanks.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Program Files (x86)\Common Files\Microsoft Shared\DevServer\9.0\WebDev.WebServer.exe', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_32\WebDev.WebHost\9.0.0.0__b03f5f7f11d50a3a\WebDev.WebHost.dll'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Web.RegularExpressions\2.0.0.0__b03f5f7f11d50a3a\System.Web.RegularExpressions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Design\2.0.0.0__b03f5f7f11d50a3a\System.Design.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.JScript\8.0.0.0__b03f5f7f11d50a3a\Microsoft.JScript.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_32\VJSharpCodeProvider\2.0.0.0__b03f5f7f11d50a3a\VJSharpCodeProvider.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\CppCodeProvider\8.0.0.0__b03f5f7f11d50a3a\CppCodeProvider.dll'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Web.Mobile\2.0.0.0__b03f5f7f11d50a3a\System.Web.Mobile.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\0a8d2c44\abadeb20_9951ca01\Coalesys.PanelBar.DLL'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\f053040a\d5e3309b_2c58ca01\Flairdocs.Configuration.DLL', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\711ccc85\47d07384_d058ca01\Flairdocs.Data.DLL', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\bfbe07fe\5b39d90b_f558ca01\Flairdocs.DLL', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\690052f9\c5878394_2e58ca01\Flairdocs.Framework.DLL', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\80a0f83e\204632fd_d151ca01\Flairdocs.WebControls.DLL'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\46fe6e36\763408d5_d650ca01\log4net.DLL'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\c051bc2c\4ea40d19_b14dca01\Mindscape.LightSpeed.DLL'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\8205f46f\6ec81419_b14dca01\Mindscape.LightSpeed.Linq.DLL'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\assembly\dl3\edf54277\004f858d_c4f9c901\Telerik.Web.UI.DLL'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Runtime.Serialization\3.0.0.0__b77a5c561934e089\System.Runtime.Serialization.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.IdentityModel\3.0.0.0__b77a5c561934e089\System.IdentityModel.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.ServiceModel\3.0.0.0__b77a5c561934e089\System.ServiceModel.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.ServiceModel.Web\3.5.0.0__31bf3856ad364e35\System.ServiceModel.Web.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.WorkflowServices\3.5.0.0__31bf3856ad364e35\System.WorkflowServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Core\3.5.0.0__b77a5c561934e089\System.Core.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Web.Extensions\3.5.0.0__31bf3856ad364e35\System.Web.Extensions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Web.Abstractions\3.5.0.0__31bf3856ad364e35\System.Web.Abstractions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Data.DataSetExtensions\3.5.0.0__b77a5c561934e089\System.Data.DataSetExtensions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Xml.Linq\3.5.0.0__b77a5c561934e089\System.Xml.Linq.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Drawing.Design\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.Design.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Web.Extensions.Design\3.5.0.0__31bf3856ad364e35\System.Web.Extensions.Design.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\App_global.asax.s40xhcat.dll', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\SMDiagnostics\3.0.0.0__b77a5c561934e089\SMDiagnostics.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'A_35af696d_48bb_49a5_a2a6_72c91d29c97e'
'WebDev.WebServer.EXE' (Managed): Loaded 'M_35af696d_48bb_49a5_a2a6_72c91d29c97e'
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\App_Web_header.ascx.8f6a0b35.warwweqc.dll', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\App_Web_o_8kjzpb.dll', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\App_Web_nmdzzvf8.dll', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\App_Web_ehr1w1yw.dll', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebDev.WebServer.EXE' (Managed): Loaded 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\8ea7268e\9dd3b4aa\App_Web_eurrym9i.dll', Symbols loaded.
'WebDev.WebServer.EXE' (Managed): Loaded 'ActiveSharp.OffsetFinder.Dynamic'
'WebDev.WebServer.EXE' (Managed): Loaded 'ActiveSharp.OffsetFinder.Dynamic.dll'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Linq.LinqQuery<Flairdocs.Data.Models.ContentTab>.System.Collections.Generic.IEnumerable<T>.GetEnumerator'
SELECT
  ContentTabs.Id,
  ContentTabs.ContentUrl,
  ContentTabs.DisplayName,
  ContentTabs.HelpUrl,
  ContentTabs.Seq,
  ContentTabs.TabGroup,
  ContentTabs.Value
FROM
  ContentTabs
ORDER BY
  ContentTabs.TabGroup,
  ContentTabs.Seq

--> Time:   1 ms


Step into: Stepping over non-user code 'Flairdocs.Data.Models.ContentTab.ContentTab'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Model.TypeModel.'
Step into: Stepping over non-user code '..'
SELECT
  KeyTable.NextId
FROM
  KeyTable
WITH (UPDLOCK)

--> Time:   0 ms


UPDATE
  KeyTable
SET
  NextId = 61

--> Time:   0 ms


Step into: Stepping over non-user code '..'
Step into: Stepping over non-user code '..'
Step into: Stepping over non-user code '..'
Step into: Stepping over non-user code '..'
Step into: Stepping over non-user code '..'
Step into: Stepping over non-user code '..'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.UnitOfWork.Find'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.UnitOfWorkBase.Find<Flairdocs.Data.Models.ContentTab>'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Linq.ProjectionStrategy.ExecuteEntityQuery'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Linq.ProjectionStrategy.Execute'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Linq.LinqQueryExecutionPlan.Execute'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Linq.LinqQueryTranslator.Translate'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute'
Step into: Stepping over non-user code 'Mindscape.LightSpeed.Linq.LinqQuery<Flairdocs.Data.Models.ContentTab>.System.Collections.Generic.IEnumerable<T>.GetEnumerator'
The thread 0xfec has exited with code 0 (0x0).
The program '[4544] iexplore.exe: Script program' has exited with code 0 (0x0).
The program '[3940] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).
 

One more quick update. This does not happen if I call the same code from postback. The ContextFactory property creates the context on the first call and stores it in the session. Subsequent calls use the cache context. It appears that the keytable update is only being run when the context is first created and the first linq operation is performed. Subsequent operations do not have this problem. Maybe this will help shed some light on why this is happening. Thanks.

Hello Greg,

We have been unable to reproduce this in our environment and we don't really have any ideas what is causing it.  We are pretty sure something must be being added to the unit of work because a read should never cause a KeyTable update, but it's hard to know where (possibly as part of user session setup, e.g. assigning a shopping cart to the user?).  Is it possible for you to send us a stripped-down but complete project that reproduces the behaviour?  You can attach a zip file via the Options tab.  Thanks!

The code to reproduce the problem is attached. Thanks.

I was able to reproduce your problem only if the ContentTabs table contained an item with ID 0.  Is that the case in your application?  LightSpeed generally reserves 0 as a placeholder for "ID not allocated yet," and we recommend not using 0 IDs (and indeed this scenario was not supported in 2.2 RTM).

If you have existing data that requires you to support entities with ID 0, this is now supported (and the UPDATE goes away) in the nightly builds, which you can get from the Download tab (please see the warning about uninstalling RTM first).

If you do not already have data in the application, just start your identities at 1 rather than 0, and you shouldn't see the problem at all.

If you don't have rows with 0 IDs, let us know and we'll investigate further (we'll probably need you to send us some sample data in this case).

I did have an entry that had an id with zero. Since this is a new appliation framework I don't anticipate this being a problem. Thanks for all your help.

-Greg