Provide a way to translate to Sum() over short (and other similar scenarios)

[mojtabakaviani]

File a bug

Failing to project query with sum of subquery that select short properties to get totals. with int properties work properly and not work id cast type.

public class Piece {
   public int Id { get; set; }
   public string Name { get; set; }
   public short Qty { get; set; }
}

public class Assembly {
   public int Id { get; set; }
   public string Name { get; set; }
   public short Qty { get; set; }
}

public class AssemblyPart {
   public int AssemblyId { get; set; }
   public int PieceId { get; set; }
   public short Qty { get; set; }
}

var query1 = await (from a in db.Assemblies
                   select new {
                        a.Id,
                        a.Name,
                        Parts = (from p in db.AssemblyParts
                                     where p.AssemplyId == a.Id
                                     select  p.Qty).Sum()
                    }).ToListAsync();

var query2 = await (from a in db.Assemblies
                   select new {
                        a.Id,
                        a.Name,
                        Parts = (from ap in db.AssemblyParts
                                     join p in db.Pieces on ap.PieceId equal p.Id
                                     where a.AssemplyId == a.Id
                                     select  ap.Qty * p.Qty).Sum()
                    }).ToListAsync();

Stack traces

System.Collections.Generic.KeyNotFoundException: The given key 'System.Int16' was not present in the dictionary.
         at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethods.GetSumWithoutSelector(Type type)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateAggregateWithSelector(ShapedQueryExpression source, LambdaExpression selectorLambda, Func`2 methodGenerator, Boolean throwWhenEmpty, Type resultType)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSum(ShapedQueryExpression source, LambdaExpression selector, Type resultType)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)

Provider and version information

EF Core version: 8.0.7 Database provider: Microsoft.EntityFrameworkCore.SqlServer,Npgsql.EntityFrameworkCore.PostgreSQL Target framework: .NET 8.0 Operating system: Windows 11 IDE: VS Code

[roji]

.NET itself doesn't have a Sum() extension method over short (see Sum() overloads), so your code doesn't compile. Are you trying to add your own overload of Sum or similar? If so, that won't work.

In general, rather than posting a couple snippets (which don't compile), when submitting an issue, please always include an actual runnable code sample, as a console program.

[mojtabakaviani]

efcore support Sum() overloading? I cast qty to int but not working and need change data types.

[roji]

I'd suggest simply changing the type to int - that would likely be the simplest solution here.

[mojtabakaviani]

There seems to be no other way at the moment, but the problem is the unnecessary space in the database that appears in the large number of records.

[roji]

I'm not sure exactly how much data you're deailng with, but in many/most cases the actual difference between an int16 and an int32 would be negligible. Otherwise, you can use SQL querying to write the SQL you want, without LINQ, for the specific query where you need to.

I'll keep this issue open to track providing some way to express this. Another way to go here is to propose adding Sum() overloads to .NET itself (for short, byte...).

[mojtabakaviani]

Thank you, @roji. I am looking forward to the removal of this restriction.