JSON File and LINQ Sum and Group

Question

Could I please get some help with querying from a JSON file? Populating a datagrid view works just fine but what I am trying to do now is filter the data using LINQ which I'm really struggling with. This works just fine, populating the datagridview with all of my jsonfile data

//dataGridView1.DataSource = (from p in movie2 // select p).ToArray();

Below is what I have been playing around with. When I group by employee ID into g, I can not longer use my p references to fields.

 using (StreamReader file = File.OpenText(@"C:\temp\GRMReportingJSONfiles\Assigned_FTE\" + myFile)) { JsonSerializer serializer = new JsonSerializer(); IEnumerable<AssgnData> movie2 = (IEnumerable<AssgnData>)serializer.Deserialize(file, typeof(IEnumerable<AssgnData>)); dataGridView1.DataSource = (from p in movie2 group p by p.EMPLID[0] into g select new { EMPLID = p.EMPLID, (decimal?)decimal.Parse(p.MNTH1) ?? 0).Sum(), }; ); //dataGridView1.DataSource = (from p in movie2 // select Int32.Parse(p.MNTH1).Sum(); dataGridView1.DataSource = (from p in movie2 group p by p.EMPLID[0] into g select (decimal?)decimal.Parse(p.MNTH1) ?? 0).Sum(); //dataGridView1.DataSource = (from p in movie2 // select p).ToArray(); //where p.Resource_BU == "7000776" //chart1.DataBindCrossTable(movie2, "MNTH1", "1", "PROJECT_ID", "Label = FTE"); //chart1.Refresh(); }

Here is part of the array layout, removed other fields for now as I was just trying to focus on these two, dataset has 100k rows and 50 columns

public class AssgnData { public string EMPLID { get; set; } public string MNTH1 { get; set; } }

p.EMPLID is not an array so why do you have p.EMPLID[0]. movie2 is IEnumerable<AssgnData> — jdweng
– jdweng, Commented Oct 25, 2018 at 16:54
i was copying the syntax i saw in the microsoft docs for group bys and trying to make it work for my json file — Spartanias
– Spartanias, Commented Oct 25, 2018 at 16:58
actually he is grouping by the first character of the employee id so that makes sense. — Geoffrey
– Geoffrey, Commented Oct 25, 2018 at 17:07
Can you explain what the first statement should do? First group by the first letter of the employee id and then? What do you want to do with these groups? Show the sum of the MNTH1 decimal per group? — Geoffrey
– Geoffrey, Commented Oct 25, 2018 at 17:09
i was trying to aggregate my data i pulled via sql and stored in a json file. MNTH1 is a number stored as a string for the amount of time the employee spent on a project. The employee can work on multiple projects. I was trying to see how much time Total, the employee spent in Month1 (october2018) — Spartanias
– Spartanias, Commented Oct 25, 2018 at 17:11

Geoffrey · Accepted Answer · 2018-10-26 06:39:03Z

In my opinion, using Fluent Syntax usually makes it a bit easier to understand what is going wrong here.

As soon as you group your data you are no longer working on the individual objects, but on a 'group', which is the key and an enumerable of objects.

Getting the sum per employee should then be grouping by the full employee id and then parsing the MNTH1 fields of your objects and summing them.

dataGridView1.DataSource = movie2 .GroupBy(p => p.EMPLID) // create a group of data per employee .Select(g => new { EMPLID = g.Key, // the employee id is the group key Sum = g.Sum(data => decimal.Parse(data.MNTH1)) // parse and sum }) .ToArray();

Edit: you are right, you need the ToArray to evaluate the query. I just verified on my computer and it works.

and I dont need a .ToArray() anywhere? the code processes but my DGV is left blank

jdweng · Accepted Answer · 2018-10-25 17:23:47Z

Try following :

 class Program { static void Main(string[] args) { IEnumerable<AssgnData> movie2 = null; dataGridView1.DataSource = movie2.GroupBy(x => new {id = x.EMPLID, month = x.MNTH1}) .Select(x => new { EMPLYID = x.Key.id, MONTH = x.Key.month, SUM = x.Sum(y => y.value) }); } } public class AssgnData { public string EMPLID { get; set; } public string MNTH1 { get; set; } public int value { get;set;} }

Collectives™ on Stack Overflow

JSON File and LINQ Sum and Group

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related