I don't use linq syntax. I have never got on with it. I use the linq extension instead, so my answer will be in that format.
If anyone can translate it then feel free ^_^
This is the query using extension methods:
var myLINQ = gDataset12.Tables[0].AsEnumerable()
.GroupBy(r=>new {
ID = r.Field<int>("ID"),
Date = r.Field<datetime>("Date").Date})
.SelectMany(g=> g.Max(r=>.Field<datetime>("Date"))
.Select(r=> new {
ID = r.Field<int>("ID"),
Status = r.Field<int>("Status"),
Date = r.Field<datetime>("Date")
});
I haven't tested this so there will probably be a mistake somewhere. I hope this helps you work out how to fix your query.
let me know if you need anything ^_^
Andy
UPDATE: ok - got it working. Max was not the way to go, instead I ordered the groups my date descending and took the first of each group.
There query I used is in there but here is the test I set up, just to excuse the reason I was a bit hesitant to test earlier:
internal class Program
{
private static void Main()
{
DataTable dt = new DataTable("table");
dt.Columns.AddRange(
new DataColumn[]
{
new DataColumn("ID", typeof (int)),
new DataColumn("Status", typeof (string)),
new DataColumn("Date", typeof (DateTime)),
});
dt.Rows.Add(NewRow(2706, "Y", "2015-08-27 14:26:25.103", dt));
dt.Rows.Add(NewRow(2706, "N", "2015-08-27 14:33:42.053", dt));
dt.Rows.Add(NewRow(2707, "Y", "2015-08-27 14:26:22.227", dt));
dt.Rows.Add(NewRow(2707, "N", "2015-08-27 14:27:11.000", dt));
dt.Rows.Add(NewRow(2710, "Y", "2015-08-25 17:07:01.937", dt));
dt.Rows.Add(NewRow(2710, "N", "2015-08-27 14:16:42.420", dt));
dt.Rows.Add(NewRow(2710, "Y", "2015-08-27 14:18:56.347", dt));
dt.Rows.Add(NewRow(2710, "N", "2015-08-27 14:27:07.753", dt));
dt.Rows.Add(NewRow(2755, "Y", "2015-08-25 17:06:59.210", dt));
dt.Rows.Add(NewRow(2755, "N", "2015-08-26 11:27:28.643", dt));
dt.Rows.Add(NewRow(2755, "Y", "2015-08-26 11:27:35.143", dt));
dt.Rows.Add(NewRow(2755, "N", "2015-08-27 14:16:39.917", dt));
dt.Rows.Add(NewRow(2755, "Y", "2015-08-27 14:18:53.477", dt));
dt.Rows.Add(NewRow(2755, "N", "2015-08-27 14:26:49.217", dt));
new Program().TestId(dt);
}
private void TestId(DataTable dt)
{
var myLinq = dt.AsEnumerable()
.GroupBy(r=>new {
ID = r.Field<int>("ID"),
Date = r.Field<DateTime>("Date").Date})
.Select(g=> g.OrderByDescending(r=>r.Field<DateTime>("Date")).First() )
.Select(r=> new {
ID = r.Field<int>("ID"),
Status = r.Field<string>("Status"),
Date = r.Field<DateTime>("Date")
});
Console.WriteLine("{0}\t{1}\t{2}", "ID","Status","Date");
myLinq.ToList().ForEach(i =>
{
Console.WriteLine("{0}\t{1}\t{2}", i.ID, i.Status, i.Date.ToString("G"));
});
Console.Read();
}
private static DataRow NewRow(int id, string status, string date, DataTable dt)
{
return NewRow(id, status, DateTime.Parse(date), dt);
}
private static DataRow NewRow(int id, string status, DateTime date, DataTable dt)
{
DataRow dr = dt.NewRow();
dr["ID"] = id;
dr["Status"] = status;
dr["Date"] = date;
return dr;
}
}
UPDATE: OP requires a return type of DataTable.
There are two ways we'll look at here:
1: The simple way:
DataTable result = dt.Clone();
myLinq.ToList().ForEach(a =>
{
DataRow row = dt.NewRow();
row["ID"] = a.ID;
row["Status"] = a.Status;
row["Date"] = a.Date;
result.Rows.Add(row);
});
return result;
This is simple and easy to understand. The only issue is that it is VERY specific to this single case. The best way IMHO is to use Extension methods:
2: The best way:
public static class Extensions
{
public static DataTable ToDataTable<T>(this IOrderedEnumerable<T> source)
{
if (source == null)
return null;
DataTable result = new DataTable();
var properties = typeof(T).GetProperties().ToList();
properties.ForEach(p =>
{
DataColumn column = new DataColumn(p.Name,p.PropertyType);
result.Columns.Add(column);
});
if (!source.Any())
return result;
source.ToList().ForEach(s =>
{
DataRow row = result.NewRow();
properties.ForEach(p =>
{
row[p.Name] = p.GetValue(s);
});
result.Rows.Add(row);
});
return result;
}
}
Once we have our Extension method set, we can use it to convert ANY IOrderedEnumerable object to a datatable:
DataTable result = myLinq.ToDataTable();
return result;