Query: 'SUM' is not a recognized built-in function name when using coalese

[alexsxx]

PROBLEM FOUND: NULLABLE FIELD

Hello, i'm trying to sum the values of a query but i get this error.
Basically the query is this: db.mytable.where(x=> x.id < 5).sum(x=> x.decimalfield ?? 0)
Can someone help me?
Details below.

Thanks

Exception message: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in Microsoft.EntityFrameworkCore.dll
Stack trace:
in System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) in System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
in System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
in System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
in System.Data.SqlClient.SqlDataReader.get_MetaData()
in System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
in System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) in System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) in System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in System.Data.Common.DbCommand.ExecuteReader() in Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean closeConnection)
in Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) in Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer) in Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func2 operation, Func2 verifySucceeded, TState state) in Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func2 operation, TState state)
in Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
in Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.GetResult[TResult](IEnumerable1 valueBuffers) in lambda_method(Closure , QueryContext ) in Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass20_01.b__0(QueryContext qc)
in Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
in Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
in System.Linq.Queryable.Sum[TSource](IQueryable1 source, Expression1 selector)
in EFCoreDemo.Program.Main(String[] args) in c:\users\user\documents\visual studio 2015\Projects\EFCoreDemo\EFCoreDemo\Program.cs:riga 14
in System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
in System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
in Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
in System.Threading.ThreadHelper.ThreadStart_Context(Object state)
in System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
in System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
in System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
in System.Threading.ThreadHelper.ThreadStart()

Further technical details

EF Core version: 1.1
Operating system: Win10 - SqlServer2014
Visual Studio version: 2015

[rowanmiller]

Can you please share the SQL that is run against the database.

[alexsxx]

I can't, visual studio crash when i try to use quickwatch

[rowanmiller]

You can use SQL Profiler in SQL Management Studio as an easy way to have a profiler running in the background.

[alexsxx]

Thanks for the tip, anyway:

SELECT SUM(COALESCE([x].[myfield], 0.0) AS [Coalesce])
FROM [mytable] AS [x]

[rowanmiller]

We should verify if this is a regression and move to 1.2.0 if it is not.

[smitpatel]

This did not work in 1.0.1 release too. (same exception). Not a regression. Moving to milestone 1.2.0

[smitpatel]

db.mytable.where(x=> x.id < 5).sum(x=> x.decimalfield ?? 0)

As a work-around:
Why not write query like this: db.mytable.where(x => x.id < 5).sum(x => x.decimalfield) ? Sum function in T-Sql ignores null fields anyway.

[alexsxx]

As a work-around:
Why not write query like this: db.mytable.where(x => x.id < 5).sum(x => x.decimalfield) ? Sum function in T-Sql ignores null fields anyway.

I know but if i select an object in this way (example below) i can't.

db.mytable
.where(x=> x.id <5)
.select(x=> new myobj() {
myfield: x.mydecimalnullable ?? 0
}).sum(x=> x.myfield);