How do I combine multiple LINQ queries that hit different associated mappings

Question

I have a legacy .net application where certain code is trying to sum up the counts for certain related database rows. It's an inventory solution where items could be added to the system from multiple sources (item receipts, adjustments, returns, etc).

The current code isn't very performant due mostly to it executing multiple queries, one for each relationship and adding them to a running counter. This is in code on the main item class so it is making use of the relationship attributes to start each line

count += ReceiptLines.Where(p => p.ItemReceipt.TxnDate < dt).Sum(p => p.Quantity);
count += AdjustmentLines.Where(p => p.Adjustment.TxnDate < dt).Sum(p => p.Quantity);
count += TransferLines.Where(p => p.Transfer.TxnDate < dt).Sum(p => p.Quantity);
count += ReturnLines.Where(p => p.Return.TxnDate < dt).Sum(p => p.Quantity);

This is just a snippet as the code has many more inputs and some lines that reduce the count. There are usually ~14 different queries just to calculate this one value.

I was hoping that if I could move all of this to one query it would make the method more performant, but I'm rather new to .NET and LINQ and I'm unsure how to combine these into a single query.

Is there an approach with LINQ that would allow me to merge these statements?

Edit to answer question below:

This is how these attributes are defined on the Item class (where the code above lives)

[global::System.Data.Linq.Mapping.AssociationAttribute(Name="Item_ReturnLine", Storage="_ReturnLines", ThisKey="ItemId", OtherKey="ItemId")]
        public EntitySet<ReturnLine> ReturnLines ...

These are the mappings to the tables like ReceiptLines that make reference to this Item object.

2nd Edit

I did some DB work and the query example below is pretty close to what this should be (with a few tweaks).

select i.itemid as itemId
, ISNULL(irl.total, 0) + ISNULL( rl.total, 0) + ISNULL(ial.total, 0) + ISNULL( itl.total, 0) as total,
CacheQuantityOnHand
from item i
left join ( select itemid, sum(quantity) as total from ItemReceiptLine where TxnDate < SYSDATETIME() group by itemid) irl on i.itemid = irl.itemid
left join ( select itemid, sum(quantity) as total from ReturnLine where TxnDate < SYSDATETIME() group by itemid) rl on i.itemid = rl.itemid
left join ( select itemid, sum(QuantityDiff) as total from InventoryAdjustmentLine where TxnDate < SYSDATETIME() group by itemid) ial on i.itemid = ial.itemid
left join ( select itemid, sum(quantity) as total from InventoryTransferLine where TxnDate < SYSDATETIME() group by itemid) itl on i.itemid = itl.itemid

This seems pretty fast from my testing, but I'm not sure still how to implement this in LINQ

Can you tell us more about `ReceiptLines` and `AdjustmentLines`? Type, class members, etc... — NetMage, Jul 01 '19 at 23:23
How would you envision a single SQL query computing the same sum? Also, the definition of e.g. `ReturnLine` would be more helpful... — NetMage, Jul 02 '19 at 19:40
@NetMage, see my example above. Return lines are just tracking when items are returned (this is inventory software) — Jason Daniels, Jul 05 '19 at 16:56
Your SQL references fields and tables that aren't evident in your code - what is `item`? Can you show more details on this? — NetMage, Jul 08 '19 at 19:05
I re-wrote my answer to be a translation of your SQL to LINQ. — NetMage, Jul 08 '19 at 19:15

score 1 · Answer 1 · answered Jul 01 '19 at 23:37

You can call all queries asynchronously and calculate sum when all queries are completed.

var receiptLines = ReceiptLines.Where(p => p.ItemReceipt.TxnDate < dt).SumAsync(p => p.Quantity);
var adjustmentLines = AdjustmentLines.Where(p => p.Adjustment.TxnDate < dt).SumAsync(p => p.Quantity);
var transferLines = TransferLines.Where(p => p.Transfer.TxnDate < dt).SumAsync(p => p.Quantity);
var returnLines = ReturnLines.Where(p => p.Return.TxnDate < dt).SumAsync(p => p.Quantity);

await Task.WhenAll(receiptLines, adjustmentLines, transferLines, returnLines);

var count = receiptLines.Result + adjustmentLines.Result + transferLines.Result + returnLines.Result;

When calling asynchronously all queries will be executed almost simultaneously.

Because all queries have same return type (Task<int>), you can use a collection.

var queryTasks = new[]
{
    ReceiptLines.Where(p => p.ItemReceipt.TxnDate < dt).SumAsync(p => p.Quantity),
    AdjustmentLines.Where(p => p.Adjustment.TxnDate < dt).SumAsync(p => p.Quantity),
    TransferLines.Where(p => p.Transfer.TxnDate < dt).SumAsync(p => p.Quantity),
    ReturnLines.Where(p => p.Return.TxnDate < dt).SumAsync(p => p.Quantity)
};

await Task.WhenAll(queryTasks);

var count = queryTasks.Select(task => task.Result).Sum();

Very nice. I'll test this and see what improvement it makes. — Jason Daniels, Jul 02 '19 at 14:53
I'm guessing the SumAsync is something added since VS 2013 (which is what the legacy app is developed on), as that code isn't compiling due to it not finding SumAsync. I guess I'll either need to update to a newer VS (which I need to do anyway, but the original developer is still working on the project and is hesitant to migrate), or find a different solution. — Jason Daniels, Jul 02 '19 at 16:02

NetMage · Answer 2 · 2019-07-08T19:15:17.330

Using my SQL to LINQ Recipe, your SQL query (with some assumptions about unspecified DB to class mappings) would translate to:

var irlq = from r in ReceiptLines
           where r.ItemReceipt.TxnDate < dt
           group r by r.ItemId into rg
           select new {
               ItemId = rg.Key,
               Total = rg.Sum(r => r.Quantity)
           };
var rlq = from r in ReturnLines
          where r.Return.TxnDate < dt
          group r by r.ItemId into rg
          select new {
              ItemId = rg.Key,
              Total = rg.Sum(r => r.Quantity)
          };
var ialq = from r in AdjustmentLines
           where r.Adjustment.TxnDate < dt
           group r by r.ItemId into rg
           select new {
               ItemId = rg.Key,
               Total = rg.Sum(r => r.Quantity)
           };
var itlq = from r in TransferLines
           where r.Transfer.TxnDate < dt
           group r by r.ItemId into rg
           select new {
               ItemId = rg.Key,
               Total = rg.Sum(r => r.Quantity)
           };

var ans = from i in Items
          join irl in irlq on i.ItemId equals irl.ItemId into irlj
          from irl in irlj.DefaultIfEmpty()
          join rl in rlq on i.ItemId equals rl.ItemId into rlj
          from rl in rlj
          join ial in ialq on i.ItemId equals ial.ItemId into ialj
          from ial in ialj
          join itl in itlq on i.ItemId equals itl.ItemId into itlj
          from itl in itlj
          select new {
              i.ItemId,
              Total = (irl.Total ?? 0) + (rl.Total ?? 0) + (ial.Total ?? 0) + (itl.Total ?? 0)
            i.CacheQuantityOnHand
          };

I really liked the idea of this, but when I implemented it into my code it was about 10% slower than the previous code. — Jason Daniels, Jul 03 '19 at 21:48
@JasonDaniels I can find no way to combine the `Sum`s in LINQ that don't involve a cross-join in SQL :( — NetMage, Jul 03 '19 at 22:43
This felt so close, and I have tried a bunch of variations of this. I can get it to work but it still isn't performant. From looking at the database trace, this still breaks down into multiple SQL calls instead of one call, so has the same performance as the original code. I may just look at executing the sql directly instead of using Linq in this case. — Jason Daniels, Jul 11 '19 at 16:40
@JasonDaniels Not sure why it breaks up into multiple calls - unfortunately I can't simulate your DB. Are you using LINQ to SQL, EF, EF Core (version)? — NetMage, Jul 11 '19 at 18:02

How do I combine multiple LINQ queries that hit different associated mappings

2 Answers2