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?

The answer is that SqlLite doesn't yet support decimal appropriately. Here's an open issue about it: github.com/dotnet/efcore/issues/19635 — AndyG
– AndyG, Commented Dec 8, 2023 at 15:37
SQLite doesn't support decimal. In fact, SQLite has no types, only 4 storage classes, INTEGER, REAL, STRING and BLOB. If you want to use aggregations and comparisons use double, which maps to REAL. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 8, 2023 at 15:50

D Stanley · Accepted Answer · 2023-12-11 14:43:16Z

3

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))
               };

edited Dec 11, 2023 at 14:43

answered Dec 8, 2023 at 15:42

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr. Luo Over a year ago

The code 'OrdersPrice = g.Sum(p => p.TotalPrice)' can't work, because the type of 'TotalPrice' in 'Order' is decimal. An unhandled exception has occurred while executing the request. 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.

Mr. Luo Over a year ago

I used SQLite database, SQLite has no types

D Stanley Over a year ago

I see - the problem is on the Order type, not the 'ManagerAnalysis' type. You'll probably have to use DTO types and map them to your business objects, doing the conversion in the mapping. I'll give a rough example.

Mr. Luo · Accepted Answer · 2023-12-13 09:25:29Z

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)

answered Dec 13, 2023 at 9:25

Mr. Luo

711 silver badge8 bronze badges

Comments

Joel R. Hall · Accepted Answer · 2023-12-08 15:58:12Z

0

Have you tried:

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

answered Dec 8, 2023 at 15:58

Joel R. Hall

1678 bronze badges

2 Comments

Mr. Luo Over a year ago

A new error has occurred，Sum(e => decimal.ToDouble(e.Outer.TotalPrice))' could not be translated. Additional information: Translation of method 'decimal.ToDouble' failed.

Mr. Luo Over a year ago

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

Jalpa Panchal · Accepted Answer · 2023-12-12 09:53:16Z

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;
}

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related