Get a Sum of integers from an IQueryable

jimd12 0
Viewed 104

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.ItemActionTypeId == 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?

SOLUTION:

I'm not sure why, but adding in a date range from our Search model allowed us to do the sum operation on the IQueryable. At least, that's what it seems to me. Here is the code we added to make it work. We put this in after the IQueryable, but before the Sum operation.

items = items.Where(e => e.Thing.ThingDateTime >= searchThings.FromDateTime
                 && e.Thing.ThingDateTime <= searchThings.ToDateTime);
``
iqueryable linq asp.net-core c#
3 Answers

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

//If your entity has navigation properties try excluding them w/select prior to .Sum()
var itemSum = _context
    .Select(ep => ep.ItemQuantity)
    .DefaultIfEmpty(0)  //good practice to avoid exceptions
    .Sum();
Adam Cohen0

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();
Laxmikant0

SOLUTION:

I'm not sure why, but adding in a date range from our Search Model allowed us to do the sum operation on the IQueryable. At least, that's what it seems to me. Here is the code we added to make it work. We put this in after the IQueryable, but before the Sum operation.

items = items.Where(e => e.Thing.ThingDateTime >= searchThings.FromDateTime
                 && e.Thing.ThingDateTime <= searchThings.ToDateTime);
jimd120
Related