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)