SQLite cannot apply aggregate operator 'Sum' on expressions of type 'decimal'. Using LINQ to Objects to aggregate the results on the client side

Question

Running the following code, there is an error in the OrdersPrice=g. Sum (p=>p.TotalPrice) line, Why? I want to query the sales data of the corresponding Managers for Orders within the past month, group them by ManagerId, and count the number of orders and total sales amount. One Order corresponds to one order, and the TotalPrice is the amount of one order.

System.NotSupportedException: SQLite cannot apply aggregate operator 'Sum' on expressions of type 'decimal'. Convert the values to a supported type, or use LINQ to Objects to aggregate the results on the client side.

my code:

[HttpGet]
public async Task<ActionResult<IEnumerable<ManagerAnalysis>>> GetManagerAnalysis()
{
    if (_context.Orders == null)
    {
        return NotFound();
    }
    var analysis = from Order in _context.Orders
                   where Order.OrderTime > DateTime.Now.AddMonths(-1)
                   group Order by Order.Manager.Id into g
                   select new ManagerAnalysis
                   {
                       ManagerName = g.First().Manager.Name,
                       OrderCount = g.Count(),
                       OrdersPrice = g.Sum(p => p.TotalPrice),//The error line.
                   };
    return await analysis.ToListAsync();
}
public class ManagerAnalysis
 {
     public string? ManagerName { get; set; }
     public int OrderCount { get; set; }
     public decimal OrdersPrice { get; set; } = 0;
 }
public class Order
{
    public ulong Id { get; set; }

    public Manager Manager { get; set; } = new Manager();

    public decimal TotalPrice { get; set; }
 
    public DateTime OrderTime { get; set; }
}   

I tried OrdersPrice = g.Sum(p => (decimal)p.TotalPrice) and OrdersPrice = (decimal)g.Sum(p => p.TotalPrice), but none of them had any effect. How can I do it?

Answer 1

SQLLite does not support decimal data natively. Using double for the SQL aggregation then convert to decimal in a separate query:

OrdersPrice = (decimal)g.Sum(p => (double)p.TotalPrice)

Answer 2

SQLLite does not support decimal data natively. One workaround would be to use double for the SQL aggregation then convert to decimal in a separate query:

var analysisRaw = from Order in _context.Orders
               where Order.OrderTime > DateTime.Now.AddMonths(-1)
               group Order by Order.Manager.Id into g
               select new 
               {
                   ManagerName = g.First().Manager.Name,
                   OrderCount = g.Count(),
                   OrdersPrice = g.Sum(p => p.TotalPrice)
               };

var analysis = from Order in analysisRaw.AsEnumerable()
               select new ManagerAnalysis
               {
                   ManagerName = Order.ManagerName,
                   OrderCount = Order.OrderCount,
                   OrdersPrice = Convert.ToDecimal(Order.OrdersPrice))
               };

Answer 3

Have you tried:

OrdersPrice = (decimal)g.Sum(p => decimal.ToDouble(p.TotalPrice));

Answer 4

The error you are getting is because SQLite, the database you're using, doesn't support the Sum operation on decimal types directly in the query. The issue arises from Entity Framework attempting to translate your LINQ query into SQL, which SQLite can't execute due to its limitations with decimals.The workaround for this is to first convert your LINQ query to a list (to execute the query and fetch the data into memory) and then perform the Sum operation.

You could try below code:

[HttpGet]
public async Task<ActionResult<IEnumerable<ManagerAnalysis>>> GetManagerAnalysis()
{
    if (_context.Orders == null)
    {
        return NotFound();
    }

    // First, fetch the necessary data into memory
    var ordersInMemory = await _context.Orders
        .Where(order => order.OrderTime > DateTime.Now.AddMonths(-1))
        .ToListAsync();

    // Then perform the grouping and summing in memory
    var analysis = ordersInMemory
        .GroupBy(order => order.Manager.Id)
        .Select(g => new ManagerAnalysis
        {
            ManagerName = g.First().Manager.Name,
            OrderCount = g.Count(),
            OrdersPrice = g.Sum(p => p.TotalPrice) // Now this operation is performed in memory
        });

    return analysis;
}