Query: Sum fails with "specified cast is not valid" when single/double mismatch

[mojtabakaviani]

var schools = db.Students.Select(p => new { 
      p.Id, 
      p.Name, 
      Score = p.Scores.Sum(s =>  s.Score) 
});

generated script work perfectly but raise exception when casting:

fail: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[1]
      An exception occurred in the database while iterating the results of a query.
      System.InvalidCastException: Specified cast is not valid.
         at lambda_method(Closure , TransparentIdentifier`2 )
         at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
System.InvalidCastException: Specified cast is not valid.
   at lambda_method(Closure , TransparentIdentifier`2 )
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

[maumar]

Try casting explicitly to nullable type:

var schools = db.Students.Select(p => new {
p.Id,
p.Name,
Score = p.Scores.Sum(s => (int?)s.Score)
});

[mojtabakaviani]

I use casting or db.Scores.Sum(s => s.Score) but not work. I test Count, Average, Min and Max and work perfectly. just Sum have exception.

[maumar]

Does it happen if all the students have non-empty Scores collection?

db.Students.Where(s=>s.Scores.Any()).Select(p => new { p.Id, p.Name, Score = p.Scores.Sum(s=>s.Score)});

[mojtabakaviani]

Not solved. I convert result to list but have exception yet:

System.InvalidCastException: Specified cast is not valid.
   at lambda_method(Closure , TransparentIdentifier`2 )
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.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)

[maumar]

This works in current bits. We return 0 for students without scores, and produce the following sql:

SELECT [p].[Id], [p].[Name], (
                SELECT SUM([s].[Score])
                FROM [Scores] AS [s]
                WHERE [p].[Id] = [s].[StudentId]
            )
            FROM [Students] AS [p]

[mojtabakaviani]

I testing with latest dev build and not solved.

fail: Microsoft.EntityFrameworkCore.Query[1]
      An exception occurred in the database while iterating the results of a query.
      System.InvalidCastException: Specified cast is not valid.
         at lambda_method(Closure , QueryContext , TransparentIdentifier`2 )
         at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer valueBuffer)
         at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
System.InvalidCastException: Specified cast is not valid.
   at lambda_method(Closure , QueryContext , TransparentIdentifier`2 )
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

[maumar]

@mojtabakaviani what is the data type of the "Score" column?

[mojtabakaviani]

sql: real
dotnet: float

[smitpatel]

@mojtabakaviani - How did you test with latest dev? what is the version number of EFCore packages?

[maumar]

@smitpatel I was able to reproduce this on our current bits, working on the fix now...

[maumar]

Fixed in f41fcc9

[mojtabakaviani]

Thank you very much. you solving very important bug that must solved very early (ef core 1).