Casting int to long in a Sum using Dynamic.linq throws an exception

Question

When fetching the sum of a couple integer columns in dynamic linq (https://dynamic-linq.net/) I get an Arithmetic overflow error

Unhandled exception. System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. System.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.

I know I need to cast the result of the sum to a bigger type like a long but I fail to create something that compiles.

I've created a fiddle with an abstraction of the problem. The fiddle contains a list of employees accessible via context.Employees

To simulate the problem I set the variable Test = int.MaxValue of each employee. There are 3 employees in the list.

I try to fetch the object (2 sums) context.Employees.GroupBy(w=>true).Select("new(SUM(Test) as Test,SUM(Id) as Id)").ToDynamicList().FirstOrDefault();

This throws the exception because sum of test is bigger than the maxValue an integer can store. Fiddle:https://dotnetfiddle.net/S48hu0

I tried a couple things already like: new(SUM(Convert(decimal, Test)) as Test,SUM(Id) as Id) new(SUM(CAST(Test,bigint)) as Test,SUM(Id) as Id) And some more but I never got it working.

I suspect it's a bug inside of dynamicLinq but I want some feedback before I file a Issue report.

Thanks in advance.

I've read the docs and based on that I tried a couple of things like new(SUM(Convert(decimal, Test)) as Test,SUM(Id) as Id) new(SUM(CAST(Test,bigint)) as Test,SUM(Id) as Id) And some more but I never got it working.

I would expect that Cast would work because its SQL already and that convert would work because its in the docs. I don't use .Sum() but instead use .Select because I need the sums of all columns therefore .Sum() won't work for me.

Answer 1

When using a normal LINQ GroupBy and Sum:

var countX = context.Employees.GroupBy(w=>true).Select(x => new { Test = x.Sum(e => e.Test), Id = x.Sum(e => e.Id)  }).ToList().FirstOrDefault();
Console.WriteLine($"count {countX}");

This also returns the same error:

Unhandled exception. System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details.
 ---> System.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.StoreRead()
ClientConnectionId:240a1a89-baaa-4888-a3a6-1f474d2c974e
Error Number:8115,State:2,Class:16
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.HandleReaderException(Exception e)
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.StoreRead()
   at System.Data.Entity.Core.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.Main()
Command terminated by signal 6

So it seems that this is not related to System.Linq.Dynamic.Core.