DivideByZeroException with SQL Server CE 4 when deleting large numbers of rows - 【StackMirror】|.net|linq-to-sql|sql-server-ce

I have an application using LINQ-to-SQL with SQL Server CE 4. I know that this isn't officially supported, but we have made it work, with the following exception. On occasion, we get an error with a row in the database when trying to update it.

We use a DataContext to select a row from the database, update a few columns (a boolean and a byte column), then we call SubmitChanges(). When we submit the changes, we are getting a DivideByZeroException with the following stack trace:

System.DivideByZeroException: Attempted to divide by zero.
at System.Data.SqlServerCe.NativeMethodsHelper.CompileQueryPlan(IntPtr pQpCommand, String pwszCommandText, ResultSetOptions options, IntPtr[] pParamNames, IntPtr prgBinding, Int32 cDbBinding, IntPtr& pQpPlan, IntPtr pError)
at System.Data.SqlServerCe.NativeMethods.CompileQueryPlan(IntPtr pQpCommand, String pwszCommandText, ResultSetOptions options, IntPtr[] pParamNames, IntPtr prgBinding, Int32 cDbBinding, IntPtr& pQpPlan, IntPtr pError)
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicUpdate(TrackedObject item)
at System.Data.Linq.ChangeDirector.StandardChangeDirector.Update(TrackedObject item)
at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges()

EDIT:

Upon profiling, the problem appears to be generated on a delete statement, similar to this:

DELETE FROM [WorkItemUid] WHERE ([Oid] = 24151 /* @p0 /) AND ([WorkItemOid] = 745 / @p1 /) AND ([SeriesInstanceUid] = '1.3.12.2.1107.5.1.4.54023.30000004101914490887500000063' / @p2 /) AND ([SopInstanceUid] = '1.3.12.2.1107.5.1.4.54023.30000004101913521221800001089' / @p3 /) AND ([Complete] = 1) AND ([FailureCount] = 0 / @p4 */) AND ([File] IS NULL) AND (NOT ([Failed] = 1))

The error was happening in a statement where I was attempting to individually delete ~1500 foreign key records. When I changed the code to delete rows individually, I narrowed it down to about 45 rows that couldn't be deleted. I then used SQL Compact Query Analyzer to attempt to execute the delete on the lines themselves, and it did fail there also, with the divide by zero exception.

So, it appears to not be a Linq-to-Sql error, but rather something with SQL Server Compact CE 4.0 itself deleting these rows. I've attempted to delete and start with a clean database, and replicated the populating and then deleting from the database, and the problem happens again. It might be an issue when there's > 1500 foreign key records that something happens when deleting.

A bunch of google searches were mostly empty, but I did find a reference to a DivideByZeroException with SQL Server CE 3.5 at this reference. I have confirmed I'm using the SQL Server CE 4.0 assemblies, so this does not seem to be the issue. Any ideas on if the above bug still applies to SQL Server CE 4.0? It appears after further review it is the same problem.

2012-04-04 18:37
by Steve Wranovsky

How do you make LINQ to SQL work with 4.0? - ErikEJ 2012-04-05 07:31

We create our schema in a 3.5 database, generate the dbml file, then run against a 4.0 database with the same exact schema. It seems to work for the most part. We're running a multi-threaded app against the database which does a fair a mount of updates and inserts. There were instability issues with 3.5, which are better with 4.0, but I have this one exception that's repeatable with one specific update that's occurring. It seems to be data specific - Steve Wranovsky 2012-04-05 12:00

Have you tried executing the SQL statement directly against the database? Have you tried 4.0 SP1 CTP - ErikEJ 2012-04-05 12:21

@ErikEJ, I was able to execute the same statement in SQL Compact Query Analyzer, and the error happened there, also, on some records. I'm starting to think there's an error when there's more then 1500 foreign key records, but that's not proven yet - Steve Wranovsky 2012-04-05 15:18

I'm upvoting this just for the comment on getting LINQ to SQL working in CE 4. You should write an article for Code Project - Dour High Arch 2012-04-05 17:14

Steve: Great to know, so it is not LINQ to SQL related. Looking forward to your blog post/article on getting LINQ to SQL to work with 4.0 - ErikEJ 2012-04-06 08:32

Try 4.0 SP1 please, and also try adding a rowversion column to the table - ErikEJ 2012-04-06 15:53

Upgrading to SQL Server Compact CTP1 appears to have resolved the problem. I am no longer seeing the exceptions when deleting rows as reported in the question. The problem appeared to be related to deleting rows from a table where there were > 1500 rows in a foreign key relationship.

2012-04-09 19:53
by Steve Wranovsky