CODEWITHSUNDEEP
c#asp.netlinqdatatable
ace
ace

Reputation: 225

How to merge duplicate rows (Based on column) of data table & put it in separate data table?

I have a data table which returns result something like following :

enter image description here

What i wanted to do is group by them based on column : cfg with the respective count.

The desired output should be like :

enter image description here

i tried with following LINQ syntax to get this done :

    DataTable objDataTable = DLService.GetData();

    var distinctTable = from row in objDataTable.AsEnumerable()
                        group row by row.Field<string>("cfg") into newProps
                        orderby newProps.Key
                        select new
                        {
                            PropName = newProps.Key,
                            CountOfProps = newProps.Count()
                        };

i am able to group by cfg using this but not able to get count (sum) based on cfg. as there are 4 entries for 'Apartment', what i am getting is : 4 Apartment (its returning count of entries). it should return : 74 Apartment

Can anyone provide solution to get this done? any improvements in the syntax or any other method?

Please note that i don't want to group by using sql query.

Upvotes: 1

Views: 633

Answers (2)

jdweng
jdweng

Reputation: 34429

Try following. :

var distinctTable = objDataTable.AsEnumerable()
   .GroupBy(x => row.Field<string>("cfg"))
   .OrderBy(x => x.Key)
   .Select( x => new { PropName = x.Key, CountofProps = x.Count}).ToList();

Upvotes: 1

vkluge
vkluge

Reputation: 324

In your case Group by returns an IEnumerable<IGrouping<object, DataRow>>. What you need to to is sum up all the values in the grouping:

 Dictionary<string, int> counts = dt.AsEnumerable()
            .GroupBy(x => x["cfg"])
            .OrderBy(x => x.Key)
            .ToDictionary(
                x => (string) x.Key,
                x => x.Select(row => (int) row["count"]).Sum()
            );

Upvotes: 1

Related Questions