I need linq query for report using mvc5 c#

Question

User-471420332 posted

I need below result in linq query using mvc5

Date            New Registration        Cumulative Registration           New Application            Cumulative Application

09-02-2018         1                             3                              1                                2
09-01-2018         2                             2                              1                                1

I am using below two tables Registration and Application to get above counts report base on Dates, from 1 Sept 2018 to till and grid should be date descending order as above output my report. New Registration means on that day how registration came, cumulative registrations means sum of registration counts on that particular day. And Application concept as same as registration.

1) Registration

Name                                   Email                           RegDate
Mazhar                                [e-mail]           2018-09-02 13:08:32.303
mohan                                 [e-mail]                2018-09-01 13:08:32.303
kjdj                                  [e-mail]                  2018-09-01 13:08:32.303

2) Application

Title                Sector                   AppDate
 kk                     jj                  2018-09-02 13:08:32.303 
 mm                     tt                  2018-09-01 13:08:32.303

You can also check how cumulation count should in image view

enter image description here

I search lot but still not getting any idea, thank you in advance

Answer 1

User1520731567 posted

HI mazhar khan india,

According to your description,I have some question:

where is the second item from?

New Registration means on that day how registration came,

Which field of the database is this New Registration from?

Answer 2

User-471420332 posted

Dear Yuki thank you for your important reply, please check the below image and i have explain more to understand, and added two more dates in below image url

https://imgur.com/a/VBsoV6V

Which field of the database is this new registration from.

See i have told in above question registration table just you have to write counts base on date in new registration and cumulative registration counts you have to write sum of all previous date counts, same concept to applications new application and cumulative application. 

I think you to pass start date and end date static in linq query  like start date will be 1 sept 2018 and End date will be till today. 

Just i am explain to get the concept and you write your own way to achieve this thank you in advance.

Answer 3

User1520731567 posted

Hi mazhar khan india,

According to your description,I think if you want to achieve it by linq ,you need to loop nested loops.

If so,once the data is wrong, you will not be able to debug the code.

Answer 4

User-271186128 posted

Hi Sir,

According to the screenshot and your description, I suggest you could refer to the following code:

using join clause to get the date list, then according to the date to get the new/cummulative columns.

            DateTime dt2 = new DateTime(2018, 09, 02, 13, 08, 32, 303);
            DateTime dt1 = new DateTime(2018, 09, 01, 13, 08, 32, 303);
            List<Registration> reglist = new List<Registration>()
            {
                new Registration(){ Name="Mazhar",Email="[e-mail] ",RegDate=dt2 },
                new Registration(){ Name="mohan",Email="[e-mail]",RegDate=dt1 },
                new Registration(){ Name="kjdj",Email="[e-mail]",RegDate=dt1},
            };
            List<Application> applist = new List<Application>()
            {
                new Application(){ Title="kk", Sector="jj", AppDate=dt2},
                new Application(){ Title="mm", Sector="tt", AppDate=dt1},
            };


            var datalist = (from cc in reglist
                           join aa in applist on cc.RegDate equals aa.AppDate
                           group cc by cc.RegDate into newgroup
                           select new
                           {
                               date = newgroup.Key.ToShortDateString(),
                               NewRegistration = reglist.Where(c => c.RegDate == newgroup.Key).Count(),
                               CumulativeRegistration = reglist.Where(c => c.RegDate <= newgroup.Key).Count(),
                               NewApplication = applist.Where(c => c.AppDate == newgroup.Key).Count(),
                               CumulativeApplication = applist.Where(c => c.AppDate <= newgroup.Key).Count(),
                           }).ToList();

The out put as below:

You could also use Union to get the date list, code as below:

            //get the date list
            var datelist = reglist.Select(c => c.RegDate).Union(applist.Select(c => c.AppDate)).ToList();
            var datelist2 = (from cc in datelist
                             select new
                             {
                                 date = cc.ToShortDateString(),
                                 NewRegistration = reglist.Where(c => c.RegDate == cc).Count(),
                                 CumulativeRegistration = reglist.Where(c => c.RegDate <= cc).Count(),
                                 NewApplication = applist.Where(c => c.AppDate == cc).Count(),
                                 CumulativeApplication = applist.Where(c => c.AppDate <= cc).Count(),
                             }).ToList();

Best regards,
Dillion