Lambda Sum subquery not giving the right result

Viewed 30

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!

0 Answers
Related