How to use specifications with aggregations?

Question

Consider an EF.Core query:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
 
namespace ConsoleApp2
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            var options = new DbContextOptionsBuilder<MyDbContext>()
                .UseInMemoryDatabase(databaseName: "MyDb")
                .Options;
            using var context = new MyDbContext(options);
            SeedData(context);
 
            var query = from r in context.Regions
                        orderby r.Id
 
                        let totalOrders = context.Orders.Where(x => x.RegionId == r.Id).ToArray()
 
                        let newOrders = totalOrders.Where(x => x.RegionId == r.Id && x.OrderState == 1)
                        let soldOrders = totalOrders.Where(x => x.RegionId == r.Id && x.OrderState == 2)
                        let staleOrders = totalOrders.Where(x => x.RegionId == r.Id && x.OrderState == 3)
 
                        let newSubOrderCount = newOrders.Sum(r => r.SubOrders.Count(s => s.SubOrderState == 1))
                        let soldSubOrdersCount = soldOrders.Sum(r => r.SubOrders.Count(s => s.SubOrderState == 2))
                        let staleSubOrdersCount = staleOrders.Sum(r => r.SubOrders.Count(s => s.SubOrderState == 3))
 
                        select new
                        {
                            RegionId = r.Id,
                            TotalOrders = totalOrders.Count(),
                            SoldOrders = soldOrders.Count(),
                            StaleOrders = staleOrders.Count(),
                            NewSubOrders = newSubOrderCount,
                            SoldSubOrdersCount = soldSubOrdersCount,
                            StaleSubOrdersCount = staleSubOrdersCount
                        };
 
            var results = await query.ToArrayAsync();
            foreach(var result in results)
            {
                ConsolePrintResult(result);
            }
            
        }
 
        private static void ConsolePrintResult(dynamic result)
        {
            Console.WriteLine($"Region ID: {result.RegionId}");
            Console.WriteLine($"Total Orders: {result.TotalOrders}");
            Console.WriteLine($"Sold Orders: {result.SoldOrders}");
            Console.WriteLine($"Stale Orders: {result.StaleOrders}");
            Console.WriteLine($"New Suborders: {result.NewSubOrders}");
            Console.WriteLine($"Sold Suborders Count: {result.SoldSubOrdersCount}");
            Console.WriteLine($"Stale Suborders Count: {result.StaleSubOrdersCount}");
            Console.WriteLine($"");
        }
 
        public static void SeedData(MyDbContext context)
        {
            var rng = new Random();
 
            // create approximately 5 regions
            for (int i = 1; i <= 5; i++)
            {
                var region = new Region
                {
                    Id = i
                };
 
                context.Regions.Add(region);
            }
 
            // create approximately 1,000 orders
            for (int i = 1; i <= 1000; i++)
            {
                var order = new Order
                {
                    Id = i,
                    RegionId = rng.Next(1, 21), // generate a random region ID between 1 and 20
                    OrderState = rng.Next(1, 4), // generate a random order state (1, 2, or 3)
                    SubOrders = new List<SubOrder>()
                };
 
                // add approximately 10 suborders to each order
                for (int j = 1; j <= 10; j++)
                {
                    var suborder = new SubOrder
                    {
                        Id = (i - 1) * 10 + j, // generate a unique ID for each suborder
                        SubOrderState = rng.Next(1, 4), // generate a random suborder state (1, 2, or 3)
                        Order = order
                    };
 
                    order.SubOrders.Add(suborder);
                }
 
                context.Orders.Add(order);
            }
 
            context.SaveChanges();
        }
    }
 
    public class MyDbContext : DbContext
    {
        public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { }
        public DbSet<Order> Orders { get; set; }
        public DbSet<SubOrder> SubOrders { get; set; }
        public DbSet<Region> Regions { get; set; }
    }
 
    public class Region
    {
        public int Id { get; set; }
    }
 
    public class Order
    {
        public int Id { get; set; }
        public int RegionId { get; set; }
        public Region Region { get; set; }
        public int OrderState { get; set; }
        public ICollection<SubOrder> SubOrders { get; set; }
    }
 
    public class SubOrder
    {
        public int Id { get; set; }
        public int SubOrderState { get; set; }
        public int OrderId { get; set; }
        public Order Order { get; set; }
    }
}

I wanted to utilize specification pattern (this library in particular: https://github.com/jnicolau/NSpecifications) to delegate expressions for newOrders ,soldOrders ,staleOrders into, well, specifications.

My problem is that after using let or group by statements I can no longer apply specifications, because it materializes (but the materialization is fake, see below) the subquery into an IEnumerable, e.g. this line let totalOrders = context.Orders.Where(x => x.RegionId == r.Id).ToArray()

Questions:

1. Why we need to call .ToArray()? It's not like we materialze entries, the query is still translated to SQL and executed on server
2. Why can we NOT call .ToArray() thus having IQueryable remaining and being able to apply a specification onto it?
3. Is there other way to still use specifications and overcome the problems listed above?

Answer 1

If you are using some SQL provider better to rewrite query in the following way:

var regions = context.Regions.AsQueryable();
var orders = context.Orders.AsQueryable();

var subordersQuery =
    from r in regions
    join o in orders on r.Id equals o.RegionId
    from so in o.SubOrders
    group new { o, so } by r.Id
    into g
    select new
    {
        RegionId = g.Key,
        NewSubOrders = g.Sum(x => x.o.OrderState == 1 && x.so.SubOrderState == 1 ? 1 : 0),
        SoldSubOrdersCount = g.Sum(x => x.o.OrderState == 2 && x.so.SubOrderState == 2 ? 1 : 0),
        StaleSubOrdersCount = g.Sum(x => x.o.OrderState == 3 && x.so.SubOrderState == 3 ? 1 : 0)
    };

var ordersQuery =
    from r in regions
    join o in orders on r.Id equals o.RegionId
    group o by o.RegionId into g
    select new
    {
        RegionId = g.Key,
        TotalOrders = g.Count(),
        SoldOrders = g.Sum(x => x.OrderState == 2 ? 1 : 0),
        StaleOrders = g.Sum(x => x.OrderState == 3 ? 1 : 0),
    };

var query =
    from o in ordersQuery
    join so in subordersQuery on o.RegionId equals so.RegionId
    orderby o.RegionId
    select new
    {
        o.RegionId,
        o.TotalOrders,
        o.SoldOrders,
        o.StaleOrders,
        so.NewSubOrders,
        so.SoldSubOrdersCount,
        so.StaleSubOrdersCount
    };

In this query you can apply specification only to Regions and Orders.