LINQ to Datatable Group By

Question

I have a query that returns 3 columns of data.

Country | Religion | Population
--------------------------------
US | Christianity | 80000000
US | Islam | 1000000
US | Judaism | 5000000
China | Buddhism | 100000000
China | Christianity | 2345737

and so on.

I need to show this data by country. The JSON object will look like

[ { "Country" : "US", 
             {
                "Christianity" : 80000000, 
                "Islam": 1000000, 
                "Judaism": 5000000 
             }
      },
      { "Country" : "China", 
             {
                "Buddhism " : 100000000, 
                "Christianity ": 2345737
             }
      }
   ]

What's the best way to do this? Get the data into a datatable and use LINQ to query it (if yes then how?)

Or maybe create a Dictionary>. But I am confused as to how to populate the data.

Maybe create a class with Country, Dictionary and return an array of this class.

Thanks.

The query is already grouped by country in the inner loop. How will the output of the query look like according to you? I am not very clear how to do it. Thanks. — JohnnyCage, Aug 06 '15 at 19:54

score 0 · Accepted Answer · edited May 23 '17 at 11:43

0

you can group the data using Group by in LINQ.

You can use something like this:

var grouped = from d in sampleData
                group d by d.Country
                into g
                select new {
                         Country = g.Key,
                         Data = g.Select(i => new {
                                             Religion = i.Religion,
                                             Population = i.Population
                                         })
                          };

However once it serialized into JSON it will not look exactly the same.

edited May 23 '17 at 11:43

Community

1
1

answered Aug 06 '15 at 20:03

dnlgmzddr

628
1
7
25

It's not valid JSON to begin with ;-) Property names can't end with a space. You can't have unnamed properties. – Robert McKee Aug 06 '15 at 20:05
@RobertMcKee, you're right there's no property to hold the inner data of each group. – dnlgmzddr Aug 06 '15 at 20:09
sampleData is a datatable? Also I didn't understand what Robert is saying about this not being a valid JSon. Which property is ending with a space? – JohnnyCage Aug 06 '15 at 20:42
Just run your sample JSON through jsonlint.com, But in order to the sample JSON to be valid you will need a property to hold the groups like **Data** in the following sample. `{ "Country": "China", "Data": { "Buddhism ": 100000000, "Christianity ": 2345737 } }` – dnlgmzddr Aug 06 '15 at 21:06

LINQ to Datatable Group By

1 Answers1