Write nested foreach as LINQ to aggregate two separate values

Question

I'm trying to aggregate the total number of points and coins that a user can achieve in a game on a single challenge. The hierarchy of which the player can get coins and points on a challenge is as follows:

• Challenge
  • Step
    • StepBlock

A challenge can have several steps, and a step can have several step blocks.

I've written this as a nested foreach, but this causes a lot of queries to the DB, and I wondered if this is solvable with a LINQ query aggregated on the DB which just returns an object with with two values, one for TotalAchievable points and one for total achievable coins.

The code is as follows:

foreach (var challenge in userChallenges)
        {
            var coins = 0;
            var points = 0;
            coins += challenge.CoinsWhenCompleted;
            points += challenge.PointsWhenCompleted;
            var steps = await db.Steps.Where(s => s.ChallengeId == challenge.ChallengeId).ToListAsync().ConfigureAwait(false);
            foreach (var step in steps)
            {
                coins += step.CoinsWhenCompleted;
                points += step.PointsWhenCompleted;
                var blocks = await db.StepBlocks.Where(b => b.StepId == step.StepId).ToListAsync().ConfigureAwait(false);
                foreach (var block in blocks)
                {
                    coins += block.CoinsWhenCompleted;
                    points += block.PointsWhenCompleted;
                }
            }
            challenge.TotalPossibleCoins = coins;
            challenge.TotalPossiblePoints = points;
        }

CoinsWhenCompleted and PointsWhenCompletedis the max score on that specific challenge, step or stepblock.

I've tried looking around, but couldn't find when there were several values to be aggregated.

Any help is appreciated, thanks!

Answer 1

Your main problem here is you have two select n + 1 issues.

You can rewrite it to the following:

var challengeIDs = userChallenges.Select(c => c.ChallengeId).Distinct();
var steps = await db.Steps.Where(s => challengeIDs.Contains(s.ChallengeId))
    .ToListAsync()
    .ConfigureAwait(false);

var stepIDs = steps.Select(s => s.StepId).Distinct();
var blocks = await db.StepBlocks.Where(b => stepIDs.Contains(b.StepId))
    .ToListAsync()
    .ConfigureAwait(false);

foreach (var challenge in userChallenges)
{
    challenge.TotalPossibleCoins = challenge.CoinsWhenCompleted;
    challenge.TotalPossiblePoints = challenge.PointsWhenCompleted;
    var challengeSteps = steps.Where(s => s.ChallengeId == challenge.ChallengeId).ToList();
    var stepBlocks = stepBlocks.Where(b => challengeSteps
        .Any(c => c.StepId == b.StepId))
        .ToList();

    challenge.TotalPossibleCoins += challengeSteps.Sum(c => c.CoinsWhenCompleted);
    challenge.TotalPossiblePoints += challengeSteps.Sum(c => c.PointsWhenCompleted);

    challenge.TotalPossibleCoins += stepBlocks.Sum(c => c.CoinsWhenCompleted);
    challenge.TotalPossiblePoints += stepBlocks.Sum(c => c.PointsWhenCompleted);
}

Explanation:

This query

var steps = await db.Steps.Where(s => s.ChallengeId == challenge.ChallengeId).ToListAsync().ConfigureAwait(false);

and this query

var blocks = await db.StepBlocks.Where(b => b.StepId == step.StepId).ToListAsync().ConfigureAwait(false);

are getting executed for every iteration of the loop.

To prevent this, we can simply do the two above queries, but before the foreach loops, by doing:

var challengeIDs = userChallenges.Select(c => c.ChallengeId).Distinct();
var steps = await db.Steps.Where(s => challengeIDs.Contains(s.ChallengeId))
    .ToListAsync()
    .ConfigureAwait(false);

var stepIDs = steps.Select(s => s.StepId).Distinct();
var blocks = await db.StepBlocks.Where(b => stepIDs.Contains(b.StepId))
    .ToListAsync()
    .ConfigureAwait(false);

This means that we're only ever doing two queries however many steps or user challenges we have.

Additionally, we can refactor your foreach statements by simply using Sum in Linq in order to get the aggregated totals for each of the sub collections.