Group a list of object to a json array

Question

I have a list of objects like this:

CreateDate   Name     Unit
2015-01-01   Users     100
2015-01-01   Items      50
2015-01-02   Users     400
2015-01-02   Items     150

Now i want to create a json array where i group the items in the list by CreateDate and get this:

[
    {"CreateDate":"2015-01-01", "Users":100, "Items" : 50}, 
    {"CreateDate":"2015-01-02", "Users":400, "Items" : 150}
]

CreateDate and Name is PK in the database and contains more type of names.

I have this:

    public class Statistics
    {
        public DateTime CreateDate { get; set; }

        public int Unit { get; set; }

        public string Name { get; set; }
    }

    [HttpGet]
    public ActionResult Items(DateTime startDate, DateTime endDate)
    {
        try
        {
            List<Statistics> Coll = FROM DB

            var Data = Coll.GroupBy(p => p.CreateDate);

            return Json(Coll, JsonRequestBehavior.AllowGet);
        }
        catch (Exception Ex)
        {
            return ...
        }
    }

What is the best way to do this?

Answer 1

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication6
{

    public class Transactions
    {
        public string CreateDate { get; set; }
        public string Name { get; set; }
        public int Unit { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            var transactionsList = new List<Transactions>();
            transactionsList.Add(new Transactions() { CreateDate = "2015-01-01", Name = "Users", Unit = 100 });
            transactionsList.Add(new Transactions() { CreateDate = "2015-01-01", Name = "Items", Unit = 50 });
            transactionsList.Add(new Transactions() { CreateDate = "2015-01-02", Name = "Users", Unit = 400 });
            transactionsList.Add(new Transactions() { CreateDate = "2015-01-02", Name = "Items", Unit = 150 });
            transactionsList.Add(new Transactions() { CreateDate = "2015-01-02", Name = "NewItems", Unit = 600 });

            var dictionaryList = new List<Dictionary<string, object>>();

            var transactionsQuery = (from t in transactionsList
                                     group t by new { t.CreateDate, t.Name }
                                         into grp
                                         select new
                                         {
                                             grp.Key.CreateDate,
                                             grp.Key.Name,
                                             Units = grp.Sum(t => t.Unit)
                                         }).ToList();

            var days = (from t in transactionsList
                        group t by new { t.CreateDate }
                            into grp
                            select grp.Key.CreateDate).ToList();


            foreach (string day in days)
            {
                var dataOfDay = new Dictionary<string, object>();
                dataOfDay.Add("CreateDate", day);
                foreach (var transaction in transactionsQuery.Where(x => x.CreateDate == day))
                {
                    dataOfDay.Add(transaction.Name, transaction.Units);
                }
                dictionaryList.Add(dataOfDay);
            }



            Console.WriteLine(JsonConvert.SerializeObject(dictionaryList));
            Console.ReadLine();

            // Output :
            // [{"CreateDate":"2015-01-01","Users":100,"Items":50},{"CreateDate":"2015-01-02","Users":400,"Items":150,"NewItems":600}]


        }
    }
}

Answer 2

It looks like you just need to re-shape the data using LINQ. This will work based on the sample data you provided:

var grouped = stats.GroupBy(x => x.CreateDate);

var data = grouped.Select(g => new { 
    CreateDate = g.Key, 
    // you might need to adjust the First() depending on whether you have nulls or multiples in your data
    Users = g.First(stat => stat.Name=="Users").Unit, 
    Items = g.First(stat => stat.Name=="Items").Unit 
});

Edit

Thinking about it some more, this LINQ statement might be safer if you have multiple Users/Items for a date or if you have, for example, a Users row for a date but no Items row:

var result = grouped.Select(g => new { 
    CreateDate = g.Key, 
    // you might need to adjust the First() depending on whether you have nulls or multiples
    Users = g.Where(stat => stat.Name=="Users").Sum(stat => stat.Unit), 
    Items = g.Where(stat => stat.Name=="Items").Sum(stat => stat.Unit) 
});

Then you should be able to spit it out to Json:

return Json(data, JsonRequestBehavior.AllowGet);