OData8 aggregate multiple count on nested Entities

I am trying to find the best solution to get the count and sum of large relational db using AspNetCore EntityFramework and/or OData8.

I have the following Entity models

    public class Entity1
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Entity2>? Entity2s { get; set; }
    }
    public class Entity2
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Entity1Id { get; set; }
        public virtual Entity1 Entity1 { get; set; }
        public virtual ICollection<Entity3>? Entity3s { get; set; }
    }
    public class Entity3
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Entity2Id { get; set; }
        public virtual Entity2 Entity2 { get; set; }
        public virtual ICollection<Entity4>? Entity4s { get; set; }
    }
    public class Entity4
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Entity3Id { get; set; }
        public virtual Entity3 Entity3 { get; set; }
        public virtual ICollection<Entity5>? Entity5s { get; set; }
    }
    public class Entity5
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Value { get; set; }
        public int Entity4Id { get; set; }
        public virtual Entity4 Entity4 { get; set; }
    }

> Note: there are over 100 000 items of each type.
> Not all Entity1 necessarily have Entity2s

What I want to get at the end of the day is the counts of each rolled up to the root entity and the sum of the Value in Entity5

[
   { E1: "e1_1", E2_Cnts: 5622, E3_Cnts: 876, E4_Cnts: 3242, E5_Cnts: 12, E5_Sum:42343},
   { E1: "e1_2", E2_Cnts: 64, E3_Cnts: 978, E4_Cnts: 321, E5_Cnts: 0, E5_Sum:0},
   ...
]

I searched for OData related and know that aggregation on expand wasn't first supported but it seems that it is now.

Option 1 Using Odata8 query

https://dev.local:7123/odata/Entity1s?$expand=Entity2s($select=Name;$count=true;$expand=Entity3s($select=Name;$count=true;$expand=Entity4s($select=Name;$count=true;$expand=Entity5s($top=0;$count=true))))

I get expected result, but the result is huge and then I have to map to calculate the counts I require.

{
    "@odata.context": "https://dev.local:7123/odata/$metadata#Entity1s(Entity2s(Name,Entity3s(Name,Entity4s(Name,Entity5s()))))",
    "value": [
        {
            "Id": 1,
            "Name": "e1_1",
            "Entity2s@odata.count": 15,
            "Entity2s": [
                {
                    "Name": "e2_1",
                    "Entity3s@odata.count": 18,
                    "Entity3s": [
                        {
                            "Name": "e3_1",
                            "Entity4s@odata.count": 12,
                            "Entity4s": [
                                {
                                    "Name": "e4_1",
                                    "Entity5s@odata.count": 8,
                                    "Entity5s": []
                                },
                                {
                                    "Name": "e4_2",
                                    "Entity5s@odata.count": 19,
                                    "Entity5s": []
                                },
                                ...

Option 2 Using OData8

https://dev.local:7123/odata/Entity5s?apply=groupby((Entity4/Entity3/Entity2/Entity1/Name),aggregate($count%20as%20Count))

I get a much smaller result but only the count of Entity5. Also this has a drawback that any Entity 1 which doesnt contain any Entity5's wont be listed.

[
    {
        "Entity4": {
            "Entity3": {
                "Entity2": {
                    "Entity1": {
                        "Name": "e1_1"
                    }
                }
            }
        },
        "Count": 22685
    },
    {
        "Entity4": {
            "Entity3": {
                "Entity2": {
                    "Entity1": {
                        "Name": "e1_2"
                    }
                }
            }
        },
        "Count": 14881
    },
    ...

Option 3 Using just LINQ in controller like

    var model = _context.Entity1s.Include(x=>x.Entity2s).ThenInclude(x=>x.Entity3s).ThenInclude(x=>x.Entity4s).ThenInclude(x=>x.Entity5s)

I can populate view with

   @model.Name @model.Entity2s.Count() @model.Entity2s.SelectMany(x=>Sum(x.Entity3s.Count())).....

But as can be expected this is also a huge query and takes quite a while.

Option2 seems to be the best performing if I could just know hjow to include additional aggregations

        "Entity4": {
            "Entity3": {
                "Entity2": {
                    "Entity1": {
                        "Name": "e1_1"
                    }**,"Count": 234**
                }**,"Count": 34**
            }**,"Count": 234**
        },
        "Count": 23456
        **,"Sum": 234**
    },

Or even better yet to populate the Entity1 Name in the root by. This query times out for Entity1Name, but successfully populates for Entity4/Name.

https://dev.local:7123/odata/Entity5s?apply=groupby((Entity4/Entity3/Entity2/Entity1/Name),aggregate($count%20as%20Count,(Entity4/Entity3/Entity2/Entity1/Name) with max as Entity1Name))

to get

        "Entity4": {
            "Entity3": {
                "Entity2": {
                    "Entity1": {
                        "Name": "e1_2"
                    }
                }
            }
        },
        "Count": 14881,
        "Entity1Name" : "e1_2"
    },

Is there perhaps a way to use select in the above query to only show the root items Count and Entity1Name?

I am using

1. "Microsoft.AspNetCore.OData" Version="8.2.0"
2. "Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.18"

Thank you Chen for the response.

At the end of the day I ended up with a Stored Procedure on the SQL DB. This reduced the query load significantly.

For those interested here is a quick snapshot on what I did

Create Procedure in SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 
ALTER PROCEDURE EntitySummary 
	 @Tenant varchar(max) ='Default'
AS
BEGIN 
	SET NOCOUNT ON; 
	SELECT
        dbo.Entity1s.Name, 
        COUNT(DISTINCT dbo.Entity2s.Id) AS Entity2s_Cnt, 
        COUNT(DISTINCT dbo.Entity3s.Id) AS Entity3s_Cnt, 
        COUNT(DISTINCT dbo.Entity4s.Id) AS Entity4s_Cnt, 
        COUNT(DISTINCT dbo.Entity5s.Id) AS Entity5s_Cnt, 
        Sum(dbo.Entity5s.Value) AS Entity5s_Sum
FROM dbo.Entity1s LEFT OUTER JOIN 
        dbo.Entity2s ON dbo.Entity2s.Entity1Id = dbo.Entity1s.Id LEFT OUTER JOIN
        dbo.Entity3s ON dbo.Entity3s.Entity2Id = dbo.Entity2s.Id LEFT OUTER JOIN
        dbo.Entity4s ON dbo.Entity4s.Entity3Id = dbo.Entity3s.Id LEFT OUTER JOIN
        dbo.Entity5s ON dbo.Entity5s.Entity4Id = dbo.Entity4s.Id 
WHERE dbo.Entity1s.Tenant =  @Tenant
GROUP BY dbo.Entity1s.Name
RETURN
END
GO

Create model with expected results

    public class EntitySummary
    {
        public string? Entity1Name { get; set; }
        public int? Entity2_Cnt { get; set; }
        public int? Entity3_Cnt { get; set; }
        public int? Entity4_Cnt { get; set; }
        public int? Entity5_Cnt { get; set; }
        public int? Entity5_Sum {  get; set; }
    }

Create Summary Repository

    public interface ISummaries
    {
        Task<IEnumerable<EntitySummary>> GetEntitySummary(string Tenant);
    }
    public class SummariesRepository : ISummaries
    {
        readonly ApplicationDbContext _context;
        public SummariesRepository(ApplicationDbContext context)
        {
            _context = context;
        }

        public async Task<IEnumerable<EntitySummary>> GetEntitySummary(string Tenant)
        {
            try
            {
                return await _context.Set<EntitySummary>()
                   .FromSqlRaw($"EXECUTE dbo.EntitySummary {Tenant}")
                   .ToListAsync();

            }
            catch
            {

                return new List<EntitySummary>();
            }  
        }

    }

In DbContext Add KeyLess

            modelBuilder.Entity<EntitySummary>(entity =>
            {
                entity.HasNoKey();
            });

Add startup service

            services.AddTransient<ISummaries, SummariesRepository>();

And call from Controller

    public class SummariesController : BaseController<SummariesController>
    {
        private readonly ApplicationDbContext _context;
        private ISummaries _report;

        public SummariesController(ApplicationDbContext context, ISummaries report)
        {
            _context = context;
            _report = report;
        }
         
        public async Task<IActionResult> Index()
        {
            var model = await _report.GetEntitySummary(_context.MyTenant);
            return View(model);
        }
 
    }

In conclusion I think this is probably the most performant solution. If anyone has any better suggestions or could even improve on this I would really appreciate it.