[Solved] Get Records count of specified date-range from database, if no record present for one of the date then return count as ‘0’

Question

You should create some dummy data to fill in the gaps. Since you’re doing a Sum, you can just create some dummies of your input data.

To do so, I’ve assumed that Set is List<SourceData> – change this as necessary:

public class SourceData
{
    public DateTime Date { get; set; }
    public long TotalHours { get; set; }
}

public class GraphData
{
    public DateTime Date { get; set; }
    public long TotalHoursCount { get; set; }
}

I’ve then added a method to get a date range (upper bound exclusive):

public static IEnumerable<DateTime> GetDateRange(DateTime startDate, DateTime endDate)
{
    return Enumerable.Range(0, (endDate - startDate).Days).Select(d => startDate.AddDays(d));
}

Finally, I’ve updated your GetDataWiseUsage method to generate the date range and convert it into dummy source data:

public static List<GraphData> GetDateWiseUsage(DateTime startDate, DateTime endDate)
{
    return Set.Where(x => x.Date >= startDate.Date && x.Date < endDate.Date)
    .Concat(GetDateRange(startDate, endDate).Select(date => new SourceData() { Date = date, TotalHours = 0 }))
    .GroupBy(x => x.Date.Date)
    .Select(x => new GraphData { Date = x.Key, TotalHoursCount = x.Sum(i => i.TotalHours) }).ToList();
}

I’ve changed your code to group by x.Date.Date rather than x.Date, as it seems that you want a separate grouping per day, rather than per unique time. The group by will not group the dummy data with the real data, and factor it into the sum.

You could add the dummy data afterwards but it seems easier/less work to do it beforehand.

Accepted Answer