c# - Get Reports for next 10 days in single database hit -

i want fetch count status next 10 days in single database hit using linq in c#. eg:

table : task coulmun: id     status      duedate value     1     new          jan 1 2013 value     2     new          jan 3 2013 value     3     in progress  jan 1 2014 value     4     completed    jun 21 2016 

now want fetch report next 10 days today stating : no of records in each status. records count before today date should part of today date , tomorrow date should count of particular day.

how can achieve in single database hit. don't want query db again n again 10 times .

expected result:

date  jun 20  jun 21   jun22 jun 23 jun24   jun 25...... new    2       0        0       0     0        0 inprg  1       0        0       0     0        0 complt 0       1        0       0     0        0 

solution tried :

sql query:

select count (taskid) , statuscode, duedate  task  dateadd(day, datediff(day, 0, duedate), 0) between cast('2013/06/08' date) , cast('2013/06/18' date) --where convert(datetime, duedate)  >convert(datetime, '2013/06/08')  , convert(datetime, duedate) < convert(datetime, '2013/06/18') group statuscode, duedate  order 1 desc   result : (no column name)    statuscode  duedate 1   ip  2013-06-08 08:13:36.080 1   ip  2013-06-08 09:49:04.263 1   ip  2013-06-08 10:03:26.550 1   nw  2013-06-08 10:14:11.247 1   ip  2013-06-08 10:33:45.760 1   ip  2013-06-08 20:44:27.427 1   nw  2013-06-09 01:13:54.150 

linq tried :

 datetime startdate;              datetime.tryparse("2013/06/08", out startdate);             datetime enddate;              datetime.tryparse("2013/06/18", out enddate);   var query = repository.data                             .where(x => x.statuscode != null                                 && entityfunctions.truncatetime(x.duedate) < entityfunctions.truncatetime(startdate))                             .groupby(p => new                             {                                 p.statuscode                              })                             .select(g => new                             {                                 g.key.statuscode,                                  availablecpunt = g.count()                             }).tolist();                   var result1 = repository.data                            .where(x => x.statuscode != null                                && entityfunctions.truncatetime(x.duedate) > entityfunctions.truncatetime(startdate)                                 && entityfunctions.truncatetime(x.duedate) < entityfunctions.truncatetime(enddate))                            .groupby(p => new                            {                                p.statuscode,                                p.duedate                             })                            .select(g => new                            {                                statuscode = g.key.statuscode,                                duedate = g.key.duedate,                                availablecount = g.count()                            }).tolist(); 

but again compare time , hence results not correct. 1 please help

you should break problem in simpler queries have clear perspective of want. here can first categorize every record day1, day2, ..., day10. count every category grouping on status.

i think want:

select     status,     sum(day1) day1, sum(day2) day2 , sum(day3) day3 , sum(day4) day4 , sum(day5) day5,     sum(day6) day6 , sum(day7) day7 , sum(day8) day8 , sum(day9) day9 , sum(day10) day10 from(     select         status,         case when duedate <= cast(getdate() date) 1 else 0 end day1,         case when duedate = dateadd(day, 1, cast(getdate() date)) 1 else 0 end day2,         case when duedate = dateadd(day, 2, cast(getdate() date)) 1 else 0 end day3,         case when duedate = dateadd(day, 3, cast(getdate() date)) 1 else 0 end day4,         case when duedate = dateadd(day, 4, cast(getdate() date)) 1 else 0 end day5,         case when duedate = dateadd(day, 5, cast(getdate() date)) 1 else 0 end day6,         case when duedate = dateadd(day, 6, cast(getdate() date)) 1 else 0 end day7,         case when duedate = dateadd(day, 7, cast(getdate() date)) 1 else 0 end day8,         case when duedate = dateadd(day, 8, cast(getdate() date)) 1 else 0 end day9,         case when duedate = dateadd(day, 9, cast(getdate() date)) 1 else 0 end day10     @task ) t group status 

and here linq equivalent:

tasks.select(a => new {     status = a.status,     day1 = a.duedate <= datetime.today ? 1 : 0,     day2 = a.duedate == datetime.today.adddays(1) ? 1 : 0,     day3 = a.duedate == datetime.today.adddays(2) ? 1 : 0,     day4 = a.duedate == datetime.today.adddays(3) ? 1 : 0,     day5 = a.duedate == datetime.today.adddays(4) ? 1 : 0,     day6 = a.duedate == datetime.today.adddays(5) ? 1 : 0,     day7 = a.duedate == datetime.today.adddays(6) ? 1 : 0,     day8 = a.duedate == datetime.today.adddays(7) ? 1 : 0,     day9 = a.duedate == datetime.today.adddays(8) ? 1 : 0,     day10 = a.duedate == datetime.today.adddays(9) ? 1 : 0, }).groupby(a => a.status).select(a => new {     status = a.key,     day1 = a.sum(b => b.day1),     day2 = a.sum(b => b.day2),     day3 = a.sum(b => b.day3),     day4 = a.sum(b => b.day4),     day5 = a.sum(b => b.day5),     day6 = a.sum(b => b.day6),     day7 = a.sum(b => b.day7),     day8 = a.sum(b => b.day8),     day9 = a.sum(b => b.day9),     day10 = a.sum(b => b.day10) })