1

I need to create a line graph for each store in every month of the year. However, store is dynamic it depends on the merchant. This is the return query from the db:

[{"Month":"January","Total":44,"Store":"Refoil"},
{"Month":"January","Total":242,"Store":"Sustainable Salons"},
{"Month":"January","Total":99,"Store":"The Base Collective"},
{"Month":"February","Total":37,"Store":"Refoil"},
{"Month":"February","Total":219,"Store":"Sustainable Salons"},
{"Month":"February","Total":122,"Store":"The Base Collective"},
{"Month":"February","Total":148,"Store":"Watersco Australia"}]

How can I return an object this like:

[{"Month":"January","Refoil":44,"Sustainable Salons":242},

{"Month":"February","Refoil":2,"Sustainable Salons":10}]
jazb
  • 5,498
  • 6
  • 37
  • 44
Jen143
  • 815
  • 4
  • 17
  • 42

3 Answers3

1

You could use an ExpandoObject to transpose the results:-

var results = new List<Result>();

results.Add(new Result() { Month = "January", Total = 44, Store = "Refoil" });
results.Add(new Result() { Month = "January", Total = 242, Store = "Sustainable Salons" });
results.Add(new Result() { Month = "January", Total = 99, Store = "The Base Collective" });
results.Add(new Result() { Month = "February", Total = 37, Store = "Refoil" });
results.Add(new Result() { Month = "February", Total = 219, Store = "Sustainable Salons" });
results.Add(new Result() { Month = "February", Total = 122, Store = "The Base Collective" });
results.Add(new Result() { Month = "February", Total = 148, Store = "Watersco Australia" });

var transpose = results.GroupBy(x => x.Month).Select(x =>
{
    dynamic e = new ExpandoObject();

    e.Month = x.Key;

    var ed = e as IDictionary<string, object>;

    x.ToList().ForEach(y => ed.Add(y.Store, y.Total));

    return e;
});

Debug.WriteLine(JsonConvert.SerializeObject(transpose, Newtonsoft.Json.Formatting.Indented));

Result class:-

public class Result
{
    public string Month { get; set; }
    public string Store { get; set; }
    public int Total { get; set; }
}

Gives the following output:-

[
  {
    "Month": "January",
    "Refoil": 44,
    "Sustainable Salons": 242,
    "The Base Collective": 99
  },
  {
    "Month": "February",
    "Refoil": 37,
    "Sustainable Salons": 219,
    "The Base Collective": 122,
    "Watersco Australia": 148
  }
]
Aleks
  • 1,629
  • 14
  • 19
  • Thank you for this. However, I find it hard to display in line graph in moris.js. – Jen143 Aug 20 '19 at 04:53
  • How can I set to 0 in total if that store not exist in that month? – Jen143 Aug 20 '19 at 04:54
  • @Jen143, see my answer [here](https://stackoverflow.com/a/57423156/1196867) which discusses how to use an outer join to include missing values. You could apply that to your `results` list before you use the dynamic code above. – Aleks Aug 20 '19 at 06:21
0

  1. Deserialize your json to c# object refer Deserializing JSON data to C# using JSON.NET

  2. Group by month using linq and json serialize your c# object and send it to your client side..

If this doesn't help, please provide more details, what would you expect after group by your data by month?.

Rex Andrew
  • 388
  • 2
  • 8
0

Map db response into c# model, and than create anonymous object with needed structure and serialize with Json.Net. If this not result you want, please add more details

Roma Pavliuk
  • 144
  • 8