CODEWITHSUNDEEP
asp.netvb.netentity-frameworklinq
Nse
Nse

Reputation: 305

LINQ query WHERE SUM is greater than a number

I have a series of shelves in a warehouse mapped in a database. The shelves themselves contain boxes that are measured by size in cubic feet. I want to perform a query and filter out the locations that have a minimum amount of cubic feet free in their shelves. I am able to run the query that shows the amount of free space, however the WHERE clause to limit the results of the SUM doesn't seem to have any effect.

Here is my query to get all of the shelves with their free space and total space:

Dim shelves As IQueryable(Of Location) = _db.Locations.Include(Function(l) l.Boxes)
    .Select(Function(loc) New With 
    {
        .LocationID = loc.LocationID,
        .WarehouseID = loc.WarehouseID,
        .Warehouse = loc.Warehouse.Name,
        .Row = loc.Row, 
        .Column = loc.Column, 
        .Bunk = loc.Bunk,
        .TotalSpace = loc.Footage,
        .FreeSpace = (loc.Footage - loc.Boxes.Select(Function(s) s.Size).DefaultIfEmpty(0).Sum()) 
     }).ToList()

This returns the correct values for each location, including the correct amount of free space. When I introduce the WHERE clause, it has no effect. Here is my query with the WHERE clause included:

Dim shelves As IQueryable(Of Location) = _db.Locations.Include(Function(l) l.Boxes)
    .Where(Function(l) (l.Boxes.Select(Function(e) e.Size).DefaultIfEmpty(0).Sum() >= EmptySpace))
    .Select(Function(loc) New With 
    {
        .LocationID = loc.LocationID,
        .WarehouseID = loc.WarehouseID,
        .Warehouse = loc.Warehouse.Name,
        .Row = loc.Row, 
        .Column = loc.Column, 
        .Bunk = loc.Bunk,
        .TotalSpace = loc.Footage,
        .FreeSpace = (loc.Footage - loc.Boxes.Select(Function(s) s.Size).DefaultIfEmpty(0).Sum()) 
     }).ToList()

Since .FreeSpace returns the correct values I'm not sure why it can't filter by that same value in my WHERE clause.

Upvotes: 1

Views: 631

Answers (1)

Nkosi
Nkosi

Reputation: 247098

Update

You original Where predicate 

.Where(Function(l) (l.Boxes.Select(Function(e) e.Size).DefaultIfEmpty(0).Sum() >= EmptySpace))

was not calculating the free space, which is why it had no effect.

You will notice that in your initial select you calculated free space as

(loc.Footage - loc.Boxes.Select(Function(s) s.Size).DefaultIfEmpty(0).Sum())

Notice loc.Footage was missing in the Where predicate.

That would mean that this updated query should work

Dim shelves As IQueryable(Of Location) = _db.Locations.Include(Function(l) l.Boxes)
    .Where(Function(loc) (loc.Footage - loc.Boxes.Select(Function(s) s.Size).DefaultIfEmpty(0).Sum()) >= EmptySpace)
    .Select(Function(loc) New With 
    {
        .LocationID = loc.LocationID,
        .WarehouseID = loc.WarehouseID,
        .Warehouse = loc.Warehouse.Name,
        .Row = loc.Row, 
        .Column = loc.Column, 
        .Bunk = loc.Bunk,
        .TotalSpace = loc.Footage,
        .FreeSpace = (loc.Footage - loc.Boxes.Select(Function(s) s.Size).DefaultIfEmpty(0).Sum()) 
     }).ToList()

Original Answer

Try applying the filter on the projected objects after the select

Dim shelves As IQueryable(Of Location) = _db.Locations.Include(Function(l) l.Boxes)    
.Select(Function(loc) New With 
{
    .LocationID = loc.LocationID,
    .WarehouseID = loc.WarehouseID,
    .Warehouse = loc.Warehouse.Name,
    .Row = loc.Row, 
    .Column = loc.Column, 
    .Bunk = loc.Bunk,
    .TotalSpace = loc.Footage,
    .FreeSpace = (loc.Footage - loc.Boxes.Select(Function(s) s.Size).DefaultIfEmpty(0).Sum()) 
 })
.Where(Function(loc) loc.FreeSpace >= EmptySpace)
.ToList()

That way the free space is already calculated and included in the anonymous object.

Upvotes: 1

Related Questions