Question 161744 in ASP.NET Question posted in
The official documentation can be found here.

Can I group a table in Asp.net by month?

A IGrouping<'a, Order>.Key { get; }
Gets the key of the IGrouping<out TKey, out TElement>.
Returns:
The key of the IGrouping<out TKey, out TElement>.
Types:
a is new { int Month }

Cannot implicitly convert type ‘int’ to ‘System.DateTimeOffset’

This is what i tried…

public class Order {
    public DateTimeOffset OrderDate { get; set; }
    public decimal Subtotal { get; set; }
}

public class MyViewModel
{
    public DateTimeOffset Month { get; set; }
    public decimal Subtotal { get; set; }
}

public interface IOrderService
{
    IEnumerable<Order> GetDataAsync();
}

public class OrderService : RepositoryBase<Order>,IOrderService
{
    public OrderService(RepositoryContext repositoryContext) : base(repositoryContext)
    {
    }

    public IEnumerable<Order> GetDataAsync()
    {
        return FindAll().OrderBy(ow => ow.OrderDate)
            .GroupBy(o => new
            { 
                   Month = o.OrderDate.Month
            })
            .Select(g => new MyViewModel
            {
                Month = g.Key.Month,// error is here
                Subtotal =g.Sum(x => x.Subtotal)
            })
            .ToList();
    }
}

Here is my database
order:

[
  {"OrderDate": "2023-01-08 20:07:31.0002873", "Subtotal": 100},
  {"OrderDate": "2023-01-08 20:07:31.0002873", "Subtotal": 100},
  {"OrderDate": "2023-01-08 20:07:31.0002873", "Subtotal": 100},
  {"OrderDate": "2023-02-08 20:07:31.0002873", "Subtotal": 200},
  {"OrderDate": "2023-02-08 20:07:31.0002873", "Subtotal":200},
  {"OrderDate": "2023-03-08 20:07:31.0002873", "Subtotal": 400},
  {"OrderDate": "2023-04-08 20:07:31.0002873", "Subtotal":700}
]

Here is what am expecting

[
  {"Month": "January", "Subtotal": 300}
  {"Month": "February", "Subtotal": 400}
  {"Month": "March", "Subtotal": 400}
  {"Month": "April", "Subtotal": 700}
]
Tags:

3

Answers


  1. 0

    I don’t understand why you use a DateTimeOffset to store only the month as string in the viewmodel.

    why not juste use a string directly for month in your viewmodel ?

    if you realy need to have a DateTimeOffset use g.First(), it will give you the first item for the current key

    Login or Signup to reply.
  2. 0

    o.OrderDate.Month is an int. Also note that grouping only by the month number is problematic if the dates span more than one year. I would group by year and month. From this, you can then create a new DateTimeOffset.

    Another problem is the Offset. Is the source date also of type DateTimeOffset? If yes you can take this offset from there. This requires grouping by this offset as well. Otherwise you will have to specify a constant offset as a TimeSpan.

    public IEnumerable<MyViewModel> GetDataAsync()
{
    return FindAll()
        .GroupBy(o => new
            {
                Year = o.OrderDate.Year,
                Month = o.OrderDate.Month,
                Offset = o.OrderDate.Offset
            })
        .OrderBy(g => g.Key.Year)
        .ThenBy(g => g.Key.Month)
        .Select(g => new MyViewModel
            {
                Month = new DateTimeOffset(
                    g.Key.Year, g.Key.Month, 1, 0, 0, 0, g.Key.Offset),
                Subtotal = g.Sum(x => x.Subtotal)
            })
        .ToList();
}

    I have set day to 1 and hour, minute and second to 0. Note that it is more efficient to order the grouped data containing less records.

    It may also be okay not to group by the offset and then to take one with g.First(x => x.OrderDate.Offset)

    Login or Signup to reply.
  3. 0

    You can group by using the following code

     var _dds = context.Orders.OrderBy(ow => ow.OrderDate)
          .GroupBy(o => new
          {
              Year = o.OrderDate.Year,
              Month = o.OrderDate.Month

          })
          .Select(g => new MyViewModel
          {
              Month = new DateTimeOffset(DateTime.Parse(g.Key.Year + "-" + g.Key.Month + "-01")),
              Subtotal = g.Sum(x => x.Subtotal)
          })
          .ToList();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search