I have an IQueryable that should be able to sum the points of all records in a subquery like so (This is meant to work with OData):
public Task<IQueryable<TemplatePMPResultViewModel>> GetTemplatePMPResultViewModel()
{
var result = _dbSet.Select(x => new TemplatePMPResultViewModel
{
Id = x.Id,
InsertDate = x.InsertDate,
Latitude = x.Latitude,
Longitude = x.Longitude,
CompanyAssetName = x.TemplatePMP.CompanyAsset.CodeAsset,
ConstructionSiteName = x.ConstructionSite.Name,
MechanicName = x.Mechanic.Name,
SupervisorName = x.Supervisor.Name,
Points = _dbContext.ItemCheckListResults.Where(x => x.TemplatePMPResultId.Equals(x.Id)).Sum(x => x.Points),
PendingPoints = _dbContext.ItemCheckListResults
.Where(x => x.TemplatePMPResultId.Equals(x.Id) &&
x.ExecuteActionPlan &&
(x.EndDate ?? DateTime.MaxValue) <= DateTime.Now)
.Sum(x => x.Points),
RegionName = x.ConstructionSite.Region.Name,
TypeName = x.TemplatePMP.CompanyAsset.Group.GroupType.Description
});
return Task.FromResult(result);
}
However the sum of points is returning the wrong value, when I get the query from the output window and execute it in my database manager query tool it works just fine. (I just change the Id of the record I want to see)
From what I cant tell, the lambda expression returns only the sum of the first record of ItemCheckListResults but not the sum of all records.
Can I run subqueries like that or should I try another aproach, if so, how should I do it?
Best regards!