Expression evaluation caused an overflow

[SomPuttivara]

Background: I'm working on porting a legacy Linq2Sql solution to EFCore with @optiks

I'm getting an exception when having the following Linq query:

var employee =
                    (from emp in dc.Employee
                    join dev in dc.EmployeeDevice
                        on emp.Id equals dev.EmployeeId
                    select new EmployeeInfo
                    {
                        EmployeeId = emp.Id,
                        EmployeeName = emp.Name,
                        Device = dev.Device,
                        **AverageSharePerYear = emp.YearsOfService == 0 ? 0 : (decimal)emp.NumberOfShare / (decimal)emp.YearsOfService**
                    }).ToList();`

Exception message: System.Data.SqlServerCe.SqlCeException: 'Expression evaluation caused an overflow. [ Name of function (if known) =  ]
Stack trace: System.Data.SqlServerCe.SqlCeException
  HResult=0x80004005
  Message=Expression evaluation caused an overflow. [ Name of function (if known) =  ]
  Source=SQL Server Compact ADO.NET Data Provider
  StackTrace:
   at System.Data.SqlServerCe.Accessor.get_Value()
   at System.Data.SqlServerCe.SqlCeDataReader.FetchValue(Int32 index)
   at System.Data.SqlServerCe.SqlCeDataReader.IsDBNull(Int32 ordinal)
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at TestHost.Program.Main(String[] args) in C:\Personal\EF Core\ExpressionOverflow\Program.cs:line 30

Steps to reproduce

Completed code can be found here:
https://github.com/avinash-phaniraj-readify/EFCoreTestBed/tree/ExpressionOverflow

While this is the behaviour we see with Sql CE, the SQL Server run has issues as well
On Sql Server not getting exception but the result is int. (returning 1 instead of 1.66666666666666666666) which is incorrect.

I found workaround to do this instead:
i.e use convert functions instead of casts.

AverageSharePerYear = emp.YearsOfService == 0 ? 0 : Convert.ToDecimal(emp.NumberOfShare) / Convert.ToDecimal(emp.YearsOfService)

Further technical details

EF Core version: 2.1.2
Database Provider: EntityFrameworkCore.SqlServerCompact35 2.1.0.1
Operating system: Windows 10
IDE: Visual Studio 2017 15.8

[smitpatel]

For SqlServer, it looks like we are eating up Convert node through RemoveConvert. We may/should introduce explicit cast.

cc: @ErikEJ for SqlCE, Any leads based on exception message?

[ErikEJ]

@smitpatel I have improved type mapping of parameters on the latest SQL CE provider build, wonder if that would solve this?

@SomPuttivara Could you share the generated SQL? And try 2.1.0.5 ?

[SomPuttivara]

@ErikEJ Here is the generated SQL

SELECT [emp].[Id] AS [EmployeeId], [emp].[Name] AS [EmployeeName], [dev].[Device], CASE
    WHEN [emp].[YearsOfService] = 0
    THEN 0.0 ELSE [emp].[NumberOfShare] / [emp].[YearsOfService]
END AS [AverageSharePerYear]
FROM [Employee] AS [emp]
INNER JOIN [EmployeeDevice] AS [dev] ON [emp].[Id] = [dev].[EmployeeId]

This gets 'Expression evaluation caused an overflow. [ Name of function (if known) = ]' exception.

I tried updating 0 to decimal:
AverageSharePerYear = emp.YearsOfService == 0 ? (decimal)emp.NumberOfShare : (decimal)emp.NumberOfShare / (decimal)emp.YearsOfService

It generates:

SELECT [emp].[Id] AS [EmployeeId], [emp].[Name] AS [EmployeeName], [dev].[Device], CASE
    WHEN [emp].[YearsOfService] = 0
    THEN [emp].[NumberOfShare] ELSE [emp].[NumberOfShare] / [emp].[YearsOfService]
END AS [AverageSharePerYear]
FROM [Employee] AS [emp]
INNER JOIN [EmployeeDevice] AS [dev] ON [emp].[Id] = [dev].[EmployeeId]

I'm getting different exception: 'An exception occurred while reading a database value. The expected type was 'System.Decimal' but the actual value was of type 'System.Int32'.'

Same happens when upgrading to 2.1.0.5

[ajcvickers]

Discuss in triage 🇦🇺

[ajcvickers]

@smitpatel @maumar @ErikEJ to look for workarounds

[smitpatel]

Work-around

var employee =
                    (from emp in dc.Employee
                    join dev in dc.EmployeeDevice
                        on emp.Id equals dev.EmployeeId
                    select new EmployeeInfo
                    {
                        EmployeeId = emp.Id,
                        EmployeeName = emp.Name,
                        Device = dev.Device,
                        AverageSharePerYear = emp.YearsOfService == 0 ? 0 : (1.0m * emp.NumberOfShare / emp.YearsOfService)
                    }).ToList();`

[smitpatel]

The issue in SqlServer, eating up the cast to generate wrong result has been fixed in 3.0 release.
The overflow error is related to SqlCE and that is not something we can fix in EF Core. (The new generated query in EF Core may avoid the error but EF Core 3.0 does not work with SqlCE due to netstandard2.1 tfm)

Closing this issue as external.