Linq summing by date in Asp.NET MVC

Question

SiteCode    |     date      |softwarename  |  Success  |      Error
ASZ             2017-08-5         a              1             1
ASZ             2017-08-5         b              2             2
ASZ             2017-08-5         a              3             3
ASZ             2017-08-6         a              6             6
NBL             2017-08-5         a              5             5

var CurrentCustomer = Database.Session.Query<Customer>()
    .FirstOrDefault(x => x.deleted_at == null 
        && x.Customer_Username == User.Identity.Name);

if(CurrentCustomer != null) 
{
    var CurrentDepValues = Database.Session.Query<deployments>()
        .Where(x => x.SiteCode == CurrentCustomer.SiteCode 
            && x.softwarename == current_type)
        .OrderBy(x => x.date)
        .ToList();
}

I have a db like that. I want to get an output like below. The same date values should be sum.

SiteCode    |     date      |softwarename  |  Success  |      Error
ASZ             2017-08-5         a              4             4
ASZ             2017-08-6         a              6             6

You can use GroupBy(x=>x.date).Select(blah).ToList()

Sean T
– Sean T

2017-09-05 18:14:25 +00:00
Commented Sep 5, 2017 at 18:14 — Sean T
– Sean T, Commented Sep 5, 2017 at 18:14
But, how will I sum successes and errors?

İsmail Kasap
– İsmail Kasap

2017-09-05 18:16:23 +00:00
Commented Sep 5, 2017 at 18:16 — İsmail Kasap
– İsmail Kasap, Commented Sep 5, 2017 at 18:16

Ofir Winegarten · Accepted Answer · 2017-09-05 18:34:50Z

1

Using a GroupBy:

if(CurrentCustomer != null) {
     var CurrentDepValues = Database.Session.Query<deployments>()
      .Where(x => x.SiteCode == CurrentCustomer.SiteCode 
          && x.softwarename == current_type)
      .GroupBy (x => new {x.SiteCode, x.date, x.softwarename})
      .Select (g => new {
            SiteCode = g.Key.SiteCode,
            Date = g.Key.date,
            SoftwareName = g.Key.softwarename,
            Success = g.Sum(x => x.Success),
            Error = g.Sum(x => x.Error)
      })
      .OrderBy(x => x.Date)
      .ToList();
}

EDIT

The above will create an anonymous type. If you would like to get List<Models.deployments>, you should do so in the select lambda:

.Select (g => new Models.deployments {
            SiteCode = g.Key.SiteCode,
            Date = g.Key.date,
            SoftwareName = g.Key.softwarename,
            Success = g.Sum(x => x.Success),
            Error = g.Sum(x => x.Error)
      })

edited Sep 5, 2017 at 18:34

answered Sep 5, 2017 at 18:16

Ofir Winegarten

9,3852 gold badges23 silver badges27 bronze badges

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

1 Comment

İsmail Kasap Over a year ago

I have one more question. I can not transfer it to view with the current ViewModel. (public IList<Models.deployments> deployments { get; set; }) How do I write in the ViewModel?

Collectives™ on Stack Overflow

Linq summing by date in Asp.NET MVC

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