SQL-Query with Parameters using DbContext's connection to SQL CE causes overflow when Int64-Parameter is near Int64-MaxValue

Question

I have an application using Entity Framework 6.1 and SQL CE.

Consider following SQL script, which is executed using the DbContext's Connection:

SELECT CAST((table2.Int64No + 1) AS bigint) AS Int64No
FROM
  (SELECT MAX(table1.Int64No) AS Int64No
   FROM
     (SELECT MAX(Int64No) AS Int64No
      FROM TestEntities
      WHERE (Int64No > (@end - @start))
        AND (Int64No <= @end)
      UNION SELECT (@end - @start) AS Int64No) AS table1) AS table2
WHERE (table2.Int64No <= (@end - @start))

The idea is to find the maximum value existing and return the next available, but still return a value if no entries are found at all. @end and @start are Int64 values too.

Everything works fine, until @start reaches value near Int64.MaxValue. Specifically, I have created a testing application invoking the SQL script with @start being 1 and @end being increased each loop. Funny enough, when @end reaches 9223372036854774273 (which is Int64.MaxValue - 1534), the query throws an Exception:

{System.Data.SqlServerCe.SqlCeException (0x80004005): Expression evaluation caused an overflow. [ Name of function (if known) = ]
at System.Data.SqlServerCe.Accessor.get_Value() at System.Data.SqlServerCe.SqlCeDataReader.FetchValue(Int32 index) at System.Data.SqlServerCe.SqlCeDataReader.IsDBNull(Int32 ordinal) at System.Data.SqlServerCe.SqlCeDataReader.GetValue(Int32 ordinal) at System.Data.SqlServerCe.SqlCeCommand.ExecuteScalar() at DbParameterTest.MainWindow.ButtonBase_OnClick(Object sender, RoutedEventArgs e) in c:\Users\pfch\Documents\Visual Studio 2013\Projects\DbParameterTest\DbParameterTest\MainWindow.xaml.cs:line 93}

When I refactor the calls and insert the parameters into the SQL directly (i.e. do not use a parametrized SQL query), things are fine.

Any explanations for that?

Answer 1

A bug in the SQL Compact engine - do you use the most recent build of SQL CE?