Sum of multiple queries to optimise number of queries performed

Question

I have a query which must sum the values from several tables and add the result. The system is simply an inventory system and I'm trying to get the stock level by calculating incomings (deliveries), outgoings (issues) and adjustments to items.

As the stock level is a calculated value (sum(deliveries) - sum(issues)) + sum(adjustments) I am trying to create a function that will get this value with a minimal number of queries.

At current I have linq that performs three separate queries to get each summed value and then perform the addition/subtraction in my function, however I am convinced there must be a better way to calculate the value without having to do three separate queries.

The current function is as follows:

public static int GetStockLevel(int itemId)
{
    using (var db = EntityModel.Create())
    {
        var issueItemStock = db.IssueItems.Where(x => x.ItemId == itemId).Sum(x => x.QuantityFulfilled);
        var deliveryItemStock = db.DeliveryItems.Where(x => x.ItemId == itemId).Sum(x => x.Quantity);
        var adjustmentsStock = db.Adjustments.Where(x => x.ItemId == itemId).Sum(x => x.Quantity);
        return (deliveryItemStock - issueItemStock) + adjustmentsStock;
    }
}

In my mind the SQL query is quite simple, so I have considered a stored procedure, however I think there must be a way to do this with linq.

Many thanks

Edit: Answer

Taking the code from Ocelot20's answer, with a slight change. Each of the lets can return a null, and if it does then linq throws an exception. Using the DefaultIfEmpty command will negate this, and return a 0 for the final calculation. The actual code I have used is as follows:

from ii in db.Items
let issueItems = db.IssueItems.Where(x => x.ItemId == itemId).Select(t => t.QuantityFulfilled).DefaultIfEmpty(0).Sum()
let deliveryItemStock = db.DeliveryItems.Where(x => x.ItemId == itemId).Select(t => t.Quantity).DefaultIfEmpty(0).Sum()
let adjustmentsStock = db.Adjustments.Where(x => x.ItemId == itemId).Select(t => t.Quantity).DefaultIfEmpty(0).Sum()
select (deliveryItemStock - issueItems) + adjustmentsStock);

Have you considered using a join? See http://stackoverflow.com/a/9722744/1341477 — Dave Williams, Jul 01 '13 at 11:22
SQL: select (select count(\*) from a) - (select count(\*) from b) + (select count(\*) from c) — Krisztián Balla, Jul 01 '13 at 12:07
I've looked at joins, but I can't see how they would apply this this situation — Ryan Amies, Jul 01 '13 at 12:35

Ocelot20 · Accepted Answer · 2013-07-01T13:54:56.030

3

Without knowing what your entities look like, you could do something like this:

public static int GetStockLevel(int itemId)
{
    using (var db = EntityModel.Create())
    {
        // Note: Won't work if there are no IssueItems found.
        return (from ii in db.IssueItems
                let issueItems = db.IssueItems.Where(x => x.ItemId == itemId)
                                              .Sum(x => x.QuantityFulfilled)
                let deliveryItemStock = db.DeliveryItems.Where(x => x.ItemId == itemId)
                                                        .Sum(x => x.Quantity)
                let adjustmentsStock = db.Adjustments.Where(x => x.ItemId == itemId)
                                                     .Sum(x => x.Quantity)
                select issueItems + deliveryItemStock + adjustmentsStock).FirstOrDefault() ?? 0;
    }
}

I tested a similar query on my own db and it worked in a single query. I suspect that since they all have a common ItemId, that using entity relations could make this look something like:

// Ideal solution:
(from i in db.Items
 where i.Id == itemId
 let issueItems = i.IssueItems.Sum(x => x.QuantityFulfilled)
 let deliveryItemStock = i.DeliveryItems.Sum(x => x.Quantity)
 let adjustmentsStock = i.Adjustments.Sum(x => x.Quantity)
 select issueItems + deliveryItemStock + adjustmentsStock).SingleOrDefault() ?? 0;

edited Jul 01 '13 at 13:54

answered Jul 01 '13 at 13:26

Ocelot20

10,510
11
55
96

This is the closest to getting it to work, however when there are no records that match in the tables the returned value is null, not 0. i.e. if there are 5 records for IssueItems for Item 13 but nothing in DeliveryItems or Adjustments, then the returned result is null instead of 5. – Ryan Amies Jul 01 '13 at 13:34
@Ryan Amies: Easily remedied by `.FirstOrDefault() ?? 0`, or `.SingleOrDefault() ?? 0` in the second case. Edit - Sorry, the first solution won't work, but if the relationships in the second exist (not sure why they wouldn't), you should be able to get around that problem with the `SingleOrDefault`. – Ocelot20 Jul 01 '13 at 13:48
@RyanAmies: See Update to answer. – Ocelot20 Jul 01 '13 at 13:55
I've marked this as the answer though the actual solution to the problem was as seen in my updated question. – Ryan Amies Jul 01 '13 at 14:38
@RyanAmies: Have you tried the second approach I posted? The answer you posted will still present problems if you do not have any IssueItems since that is the basis of the query. – Ocelot20 Jul 01 '13 at 20:13
It would still return null if any one of the three sub tables had no results. You have to `DefaultIfEmpty` each `let` statement – Ryan Amies Jul 02 '13 at 10:55

score 0 · Answer 2 · answered Jul 01 '13 at 13:00

0

Have you considered adding a view to the database that performs the calculations that you can then just use a simple select query (or SP) to return the values that you need?

answered Jul 01 '13 at 13:00

steoleary

8,968
2
33
47

Yes, but if possible I wanted to keep all Data Access in my service layer – Ryan Amies Jul 01 '13 at 13:16

Dave Williams · Answer 3 · 2013-07-01T14:05:22.867

0

I reckon this should work and the SQL generated is not particularly complex. If you think there is something wrong with it let me know and I will update my answer.

public static int GetStockLevel(int itemId)
{
    using (var db = EntityModel.Create())
    {
        return db.IssueItems.Where(x => x.ItemId == itemId).GroupBy(x => x.ItemId)
        .GroupJoin(db.DeliveryItems, x => x.First().ItemId, y => y.ItemId, (x, y) => new 
        { Issues = x, Deliveries = y})
        .GroupJoin(db.Adjustments, x=> x.Issues.First().ItemId, y=> y.ItemId, (x, y) => new 
        {
            IssuesSum = x.Issues.Sum(i => i.QuantityFullfilled), 
            DeliveriesSum = x.Deliveries.Sum(d => d.Quantity), 
            AdjustmentsSum = y.Sum(a => a.Quantity)})
        .Select(x => x.IssuesSum - x.DeliverysSum + x.AdjustmentsSum);
    }
}

edited Jul 01 '13 at 14:05

answered Jul 01 '13 at 13:25

Dave Williams

2,166
19
25

The second `GroupJoin` is erroring as the returned object from the first `GroupJoin` is an anonymous type – Ryan Amies Jul 01 '13 at 13:38
Sorry forgot you are returning a collection. I have updated to include First() – Dave Williams Jul 01 '13 at 13:53
Suffers from the same issue @RyanAmies pointed out with my first stab at it. If there are no IssueItems, the other counts are ignored. – Ocelot20 Jul 01 '13 at 14:06
1

You can just use DefaultIfEmpty() in that case? – Dave Williams Jul 01 '13 at 14:15

Sum of multiple queries to optimise number of queries performed

Edit: Answer

3 Answers3