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",
"[email protected]": 15,
"Entity2s": [
{
"Name": "e2_1",
"[email protected]": 18,
"Entity3s": [
{
"Name": "e3_1",
"[email protected]": 12,
"Entity4s": [
{
"Name": "e4_1",
"[email protected]": 8,
"Entity5s": []
},
{
"Name": "e4_2",
"[email protected]": 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
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.