Problem with fetching records from database with EntityFramework

Question

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

Please share more details about your underlying model and EF configuration. — julealgon
– julealgon, Commented Jan 17, 2023 at 13:05
Does it make a difference if, for testing purposes, you replace the DateTime.Now.* values with hardcoded numbers? I'm asking this because EF will in some cases attempt to interpret your code directly instead of using the underlying value. Another test you can do, is create a variable for those values, and use the variables in the query. See if that makes a difference in the results. — julealgon
– julealgon, Commented Jan 17, 2023 at 13:06
And lastly, do the SQL queries generated by EF make sense to you? You can check the queries in the console or by checking the IQueryable in the debugger. — julealgon
– julealgon, Commented Jan 17, 2023 at 13:08
Those queries result in conditions like YEAR(created)=2023 AND MONTH(created).... that can't use any indexes. Use a range condition instead, eg .Where(x => x.Created>=DateTime.Today && x.Created <DateTime.Today.AddDays(1)). The resulting query will use a simple range condition, eg created >='2023-01-17 AND created < '2024-01-18' — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 17, 2023 at 13:09
Maybe it is you grouping that is wrong, which does not seems to be in the code posted. — Magnus
– Magnus, Commented Jan 17, 2023 at 13:11

Panagiotis Kanavos · Accepted Answer · 2023-01-17 13:42:49Z

3

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);

or

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

edited Jan 17, 2023 at 13:42

answered Jan 17, 2023 at 13:37

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lora Over a year ago

Thank you for your great answer. I will need to take some tutorial regarding 'GroupBy' to avoid mistakes.

Collectives™ on Stack Overflow

Problem with fetching records from database with EntityFramework

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related