Get a Sum of integers from an IQueryable-CodePudding

We use a SQL database in our ASP.Net Core solution that stores things and can also store zero to multiple item types for each thing. We also store a quantity for each item type stored. I need to be able to sum the quantity for an item type and return it to the View.

Here is what we use in the controller:

       IQueryable<Item> items = _context.Items
                    .Include(ep=>ep.ItemType)
                    .Include(ep=>ep.ItemActionType)
                    .Include(ep=>ep.Thing)
                    .AsQueryable();

The query we use in the Controller to get the sum is:

   int itemsum = (int)items.Where(ep => ep.ItemTypeId == 2 && ep.ItemActionType == 8).Select(ep => ep.ItemQuantity).Sum();

The database has the following entries that should result in a sum of six being returned:

Id  ThingId ItemTypeId  ItemActionTypeId    ItemQuantity    
1   100         2               8                2 
2   100         2               8                1  
3   103         2               8                1
4   103         2               8                1      
5   105         2               8                1

This information returns a value of 3.

What am I doing wrong?

CodePudding user response：

you can get it as List

List<item> result = _context.Items
    .GroupBy(l => l.ItemType)
    .Select(cl => new item
            {
                ItemType = cl.ItemType,
                ItemActionType = cl.ItemActionType,
                Thing = cl.Thing,
                TotalQty = cl.Sum(c => c.ItemQuantity).ToString(),
            }).ToList();

CodePudding user response：

It could be an issue with the translation to SQL and materialization of your query. You may also have code that's applying an additional filter to the IQueryable somewhere, which will modify the results as the IQueryable result doesn't fetch data from the server, and additional filters (e.g. Where clause) applied prior to triggering results from the server via ToList(), Sum(), etc. will alter the query.

First, try running the sample code below and see if you get the desired result and then swap out the sample data context with your DbContext to determine if you

//Sample data
var _context = new List<(int id, int ItemTypeId, int ItemActionTypeId, int ItemQuantity)>()
    {(1, 2, 8, 2),
    (2, 2, 8, 1),
    (3, 2, 8, 1),
    (4, 2, 8, 1),
    (5, 2, 8, 1)}
    .AsQueryable();

var itemSum = _context
    .Where(ep => ep.ItemTypeId == 2 && ep.ItemActionTypeId == 8)
    .Select(ep => ep.ItemQuantity)
    .DefaultIfEmpty(0)  //good practice to avoid exceptions
    .Sum();

Console.WriteLine(itemSum);
//result = 6