Use method inside LINQ GroupBy

Question

I'm trying to manipulate properties in a GroupBy clause to be used in a dictionary:

var lifeStages = await _dbContext.Customers
                                 .GroupBy(x => GetLifeStage(x.DoB))
                                 .Select(x => new { LifeStage = x.Key, Count = x.Count() })
                                 .ToDictionaryAsync(x => x.LifeStage, x => x.Count);

I'm expecting results like

adolescent: 10, adult: 15, senior: 12 etc

But getting error:

Either rewrite the query in a form that can be translated,
    or switch to client evaluation explicitly 
    by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). 

Offcourse I can't combine ToDictionary() with any of the mentioned calls, and splitting up the query did not resolve the issues or taught my anything)

I've tried with making GetLifeStage() static and async, no difference there as well. The method gets called, performs what it needs to do, and still GroupBy can't be translated

If I leave out the Select() part and work with the Key of the GroupBy, same error: "...could not be translated."

I saw an error too that said I couldn't combine a GroupBy() with a ToDictionary() during try-outs, but doesn't seem to pop up atm.

As I'm running out of ideas, all suggestions are welcome!

update:

private LifeStage GetLifeStage(DateTimeOffset doB)
{
  var ageInMonths = Math.Abs(12 * (doB.Year - DateTimeOffset.UtcNow.Year) + doB.Month - DateTimeOffset.UtcNow.Month);

  switch (ageInMonths)
  {
    case < 216:
      return LifeStage.Adolescent;
    case < 780:
      return LifeStage.Adult;
    case >= 780:
      return LifeStage.Senior;
  }
}

Answer 1

The problem is the usage of the custom GetLifeStage method inside the GroupBy expression. Custom methods cannot be translated to SQL because the query translator code has no way to know what is inside that method. And it cannot be called because there are no objects at all during the translation process.

In order to make it translatable, you have to replace the custom method call with its body, converted to translatable expression - basically something which can be used as expression bodied method. You can't use variables and switch, but you can use conditional operators. Instead of variable, you could use intermediate projection (Select).

Here is the equivalent translatable query:

var lifeStages = await _dbContext.Customers
    .Select(c => new { Customer = c, AgeInMonths = Math.Abs(12 * (c.DoB.Year - DateTimeOffset.UtcNow.Year) + c.DoB.Month - DateTimeOffset.UtcNow.Month) })
    .GroupBy(x => x.AgeInMonths < 216 ? LifeStage.Adolescent : x.AgeInMonths < 780 ? LifeStage.Adult : LifeStage.Senior)
    // the rest is the same as original
    .Select(x => new { LifeStage = x.Key, Count = x.Count() })
    .ToDictionaryAsync(x => x.LifeStage, x => x.Count);