Display the sum of the orders of the month in chart

Question

I don`t know how to display, at the beginning the sum of the orders of the month in the table in the asp.net MVC. My data in table Order look like:

DateCreated | TotalPrice
2017-02-06 | 400
2017-02-06 | 300
2017-03-06 | 100
2017-03-06 | 50

And I want to get mountly sum of TotalPrice, like this:

DateCreated | TotalPrice
2017-02 | 700
2017-03 | 150

My model class Order:

public int orderId { get; set; }
    public string UserId { get; set; }
    public virtual ApplicationUser User { get; set; }
    [StringLength(100)]
    public string firstName { get; set; }
    [StringLength(150)]
    public string lastName { get; set; }
    [StringLength(150)]
    public string address { get; set; }
    [StringLength(20)]
    public string phoneNumber { get; set; }
    public string email { get; set; }

    public string comment { get; set; }
    public DateTime dateCreated { get; set; }
    public OrderState orderState { get; set; }
    public decimal totalPrice { get; set; }

    public List<OrderIteam> OrderIteams { get; set; }

I try to write something like this to display the sum of the orders of the month in the table :

public ActionResult ListOrder()
{
    var result =
        from s in db.Orders
        group s by new { date = new DateTime(s.dateCreated.Year, s.dateCreated.Month, 1) } into g
        select new
        {
            dateCreated = g.Key.date,
            sumTotalPrice = g.Sum(x => x.totalPrice)
        };
    return View(result);
}

My view looks like this

I am getting the error message:

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery1[<>f__AnonymousType52[System.DateTime,System.Decimal]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[Sklep_Internetowy.Models.Order]'.

At the end I would like to show that the sum of the orders of a given month as the chart below: Chart

Answer 1

Currently the LINQ query below returns DbQuery instance with anonymous type instead of IEnumerable collection:

var result = from s in db.Orders
             group s by new { date = new DateTime(s.dateCreated.Year, s.dateCreated.Month, 1) } into g
             select new
             {
                 dateCreated = g.Key.date,
                 sumTotalPrice = g.Sum(x => x.totalPrice)
             }; // returns System.Data.Entity.Infrastructure.DbQuery<AnonymousTypeN>

The reason behind thrown InvalidOperationException here is simply because a different type of data has been passed into the view which requires IEnumerable<Sklep_Internetowy.Models.Order>.

Try change return type to Order instead using anonymous type one:

var result = from s in db.Orders
             group s by new { date = new DateTime(s.dateCreated.Year, s.dateCreated.Month, 1) } into g
             select new Order // return Sklep_Internetowy.Models.Order here
             {
                 dateCreated = g.Key.date,
                 sumTotalPrice = g.Sum(x => x.totalPrice)
             };

At this point, the return type of LINQ query above becomes IQueryable<Sklep_Internetowy.Models.Order> which implements IEnumerable, hence it doesn't require conversion (but you may still need to use collection aggregate methods to execute it, e.g. ToList()).

Related issues:

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery', but this dictionary requires a model item of type B

The model item passed into the dictionary is 'System.Data.Entity.Infrastructure.DbQuery`, but requires 'System.Collections.Generic.IEnumerable