Problem with fetching records from database with EntityFramework-CodePudding

I need little help with the three functions below. I expect the functions to take the records daily, monthly and all records of the current year. However, I notice on the daily report the amount of 'scrap' is around 126 meanwhile monthly and year reports are showing 32.

Why 126 'scrap' in the daily report is not included in the others reports? Thank you in advance.

public async Task<List<Scrap>> GetDailyScrap()
{
    return await _dbContext.Scrap.Where(x => 
                    x.Created.Year == DateTime.Now.Year && 
                    x.Created.Month == DateTime.Now.Month && 
                    x.Created.Day == DateTime.Now.Day).ToListAsync();
}
public async Task<List<Scrap>> GetMonthlyScrap()
{
    return await _dbContext.Scrap.Where(x => 
                    x.Created.Year == DateTime.Now.Year && 
                    x.Created.Month == DateTime.Now.Month).ToListAsync();
}
public async Task<List<Scrap>> GetYearScrap()
{
    return await _dbContext.Scrap.Where(x => 
                    x.Created.Year == DateTime.Now.Year).ToListAsync();
}

charts

The amount of scrap for KST-420(daily chart) to reflect with the correct numbers on the monthly and year report.

Scrap Model :

public class Scrap
{
    public int Id { get; set; }

    public int ScrapLineId { get; set; }

    public string Line { get; set; }

    public string Type { get; set; }

    public string Position { get; set; }

    public string Tag { get; set; }

    public int Shift { get; set; }

    public int ShiftLeaderPersonalId { get; set; }

    public int OperatorPersonalId { get; set; }
    
    public int Quantity { get; set; }

    public int Week { get; set; }

    public DateTime Created { get; set; }

}

endpoint:

    //Daily Bar Chart SCRAP
    List<Scrap> dailyScrap = await _scrapService.GetDailyScrap();
    List<string> xValues = dailyScrap.DistinctBy(x => x.Line).Select(x => x.Line).ToList();
    List<int> yValues = dailyScrap.DistinctBy(x => x.Quantity).Select(x => x.Quantity).ToList();

    // Monthly Bar Chart SCRAP
    List<Scrap> monthlyScrap = await _scrapService.GetMonthlyScrap();
    List<string> xValuesMonthly = monthlyScrap.DistinctBy(x => x.Line).Select(x => x.Line).ToList();
    List<int> yValuesMonthly = monthlyScrap.DistinctBy(x => x.Quantity).Select(x => x.Quantity).ToList();

    // Year Bar Chart SCRAP
    List<Scrap> yearScrap = await _scrapService.GetYearScrap();
    List<string> xValuesYear = yearScrap.DistinctBy(x => x.Line).Select(x => x.Line).ToList();
    List<int> yValuesYear= yearScrap.DistinctBy(x => x.Quantity).Select(x => x.Quantity).ToList();

charts

CodePudding user response：

The way these queries are written, they count individual values, not the count or sum of items per line. For example, 101 and 102 would produce an Y value of 2, while 100 individual 100s would produce 1.

To get totals by line, use GroupBy and Count or Sum, eg :

var dailies=dailyScrap.GroupBy(s=>s.Line)
                      .Select(g=>new 
                          { 
                              X=g.Key, 
                              Y=g.Sum(s=>s.Quantity)
                          })
                      .ToList();

This can be done in EF Core too, retrieving only the totals from the database :

var dateFrom=DateTime.Today;
var dateTo=dateFrom.AddDays(1);

var dailies=_dbContext.Scrap
                      .Where(s=> s.Created>=dateFrom 
                              && s.Created <dateTo)
                      .GroupBy(s=>s.Line)
                      .Select(g=>new 
                          { 
                              X=g.Key, 
                              Y=g.Sum(s=>s.Quantity)
                          })
                      .ToList()

This generates

SELECT Line,SUM(Quantity)
FROM Scrap
WHERE Created >=@d1 && Created < @d2
GROUP BY Line

The condition can be simplified to only Where(s=> s.Created>=DateTime.Today) if there are no future values.

The query can be adapted to cover any period by changing the From and To parameters, eg :

var dateFrom=new DateTime(DateTime.Today.Year,DateTime.Today.Month,1);
var dateTo=dateFrom.AddMonths(1);

var dateFrom=new DateTime(DateTime.Today.Year,1,1);
var dateTo=dateFrom.AddYears(1);