I’ve been struggling with getting a bit of Linq to DataTable working, in essence I have a datatable (dtResults) that contains two columns:
TopicName (string)
DateAdded (long integer)
I want to be able to use linq to group by TopicName and order by DateAdded.
I can Group By with this:
Dim linqTopics = From dtResults In dtResults _
Group dtResults By TopicList = dtResults.Field(Of String)("TopicName") Into Group _
Select TopicList Distinct
But have been unable to work out how to Order by “DateAdded” Decending without it throwing an error. If somebody could help me out with the code I would be very grateful.
DataTable dtResult = new DataTable();
DataColumn dcTopicName = new DataColumn("TopicName", typeof(String));
dtResult.Columns.Add(dcTopicName);
DataColumn dcDateAdded = new DataColumn("DateAdded", typeof(Int32));
dtResult.Columns.Add(dcDateAdded);
for (int i = 0; i < 5; i++)
{
DataRow dr = dtResult.NewRow();
dr["TopicName"] = "Topic Name" + i.ToString();
dr["DateAdded"] = i;
dtResult.Rows.Add(dr);
}
for (int i = 0; i < 5; i++)
{
DataRow dr = dtResult.NewRow();
dr["TopicName"] = "Topic Name" + (i + 1).ToString();
dr["DateAdded"] = i;
dtResult.Rows.Add(dr);
}
var results = (from row in dtResult.AsEnumerable()
group row by new
{
DateAdded = row.Field<Int32>("DateAdded")
} into grp
orderby grp.Key.DateAdded
select new
{
key = grp.Key,
DateAdded = grp.Key.DateAdded,
TopicName = grp.Max(r => r.Field<string>("TopicName"))
}).OrderBy(a => a.DateAdded).ToList();
I am grouping by Date and getting the max of the Topic Name,
For the first five records, I am adding Topic Name as Topic Name +i and for next 5 records, I am adding TopicName as i+1 so if I group by I should get last five records.
I did this in c#, I did not get a chance to convert it to VB.
EdmundIJones
0 Points
13 Posts
Group by and Order by in the same expression
Jul 21, 2010 07:03 PM|LINK
Hi,
I’ve been struggling with getting a bit of Linq to DataTable working, in essence I have a datatable (dtResults) that contains two columns:
I want to be able to use linq to group by TopicName and order by DateAdded.
I can Group By with this:
Dim linqTopics = From dtResults In dtResults _
Group dtResults By TopicList = dtResults.Field(Of String)("TopicName") Into Group _
Select TopicList Distinct
But have been unable to work out how to Order by “DateAdded” Decending without it throwing an error. If somebody could help me out with the code I would be very grateful.
All the best
Ed
sansan
All-Star
53942 Points
8147 Posts
Re: Group by and Order by in the same expression
Jul 21, 2010 08:03 PM|LINK
Probably its something with syntax
check these
http://weblogs.asp.net/wenching/archive/2008/05/16/linq-datatable-query-group-aggregation.aspx
http://msdn.microsoft.com/en-us/vbasic/bb737926.aspx
EdmundIJones
0 Points
13 Posts
Re: Group by and Order by in the same expression
Jul 21, 2010 08:16 PM|LINK
Thanks, I'm sure it is todo with the syntax and I just can't work it out. Thanks for the two links but I've already tried and failed with them.
Thanks
Ed
sansan
All-Star
53942 Points
8147 Posts
Re: Group by and Order by in the same expression
Jul 22, 2010 01:59 AM|LINK
I did some sample test
DataTable dtResult = new DataTable(); DataColumn dcTopicName = new DataColumn("TopicName", typeof(String)); dtResult.Columns.Add(dcTopicName); DataColumn dcDateAdded = new DataColumn("DateAdded", typeof(Int32)); dtResult.Columns.Add(dcDateAdded); for (int i = 0; i < 5; i++) { DataRow dr = dtResult.NewRow(); dr["TopicName"] = "Topic Name" + i.ToString(); dr["DateAdded"] = i; dtResult.Rows.Add(dr); } for (int i = 0; i < 5; i++) { DataRow dr = dtResult.NewRow(); dr["TopicName"] = "Topic Name" + (i + 1).ToString(); dr["DateAdded"] = i; dtResult.Rows.Add(dr); } var results = (from row in dtResult.AsEnumerable() group row by new { DateAdded = row.Field<Int32>("DateAdded") } into grp orderby grp.Key.DateAdded select new { key = grp.Key, DateAdded = grp.Key.DateAdded, TopicName = grp.Max(r => r.Field<string>("TopicName")) }).OrderBy(a => a.DateAdded).ToList();I am grouping by Date and getting the max of the Topic Name,
For the first five records, I am adding Topic Name as Topic Name +i and for next 5 records, I am adding TopicName as i+1 so if I group by I should get last five records.
I did this in c#, I did not get a chance to convert it to VB.
try this and let me know if you need any help