如何在linq中找到id的最大日期记录 [英] How to find max date record by id in linq
问题描述
ID Status Date
-------------------------------------------
2706 Y 2015-08-27 14:26:25.103
2706 N 2015-08-27 14:33:42.053
2707 Y 2015-08-27 14:26:22.227
2707 N 2015-08-27 14:27:11.000
2710 Y 2015-08-25 17:07:01.937
2710 N 2015-08-27 14:16:42.420
2710 Y 2015-08-27 14:18:56.347
2710 N 2015-08-27 14:27:07.753
2755 Y 2015-08-25 17:06:59.210
2755 N 2015-08-26 11:27:28.643
2755 Y 2015-08-26 11:27:35.143
2755 N 2015-08-27 14:16:39.917
2755 Y 2015-08-27 14:18:53.477
2755 N 2015-08-27 14:26:49.217
您好，这是我的DataTable（gDataset12.Tables [0]）上面...从这里我希望在c＃中作为最大日期记录ID及其状态
结果应该是每天每个ID的最新更新状态
Hello, Here is my DataTable (gDataset12.Tables[0]) above ... from this i want in c# as the max date record by ID and its status
the result should be the last updated status of the for each ID for each day
2706 N 2015-08-27 14:33:42.053
2707 N 2015-08-27 14:27:11.000
2710 N 2015-08-27 14:27:07.753
2755 N 2015-08-27 14:26:49.217
2755 Y 2015-08-26 11:27:35.143
2710 Y 2015-08-25 17:07:01.937
2755 Y 2015-08-25 17:06:59.210
LINQ m使用...
LINQ m using ...
var myLINQ = from dt in gDataset12.Tables[0].AsEnumerable()
group dt by dt.Field<string>("ID") into grp
select new
{
ID = grp.Max(T => T.Field<int>("ID")),
Status = grp.Key,
Date = grp.Max(T => T.Field<datetime>("Date"))
};
推荐答案
我不使用linq语法。我从未接受过它。我使用linq扩展名，所以我的答案将采用这种格式。
如果有人可以翻译它，那么请随意^ _ ^
这是使用扩展方法的查询：
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"), //each ID
Date = r.Field<datetime>("Date").Date}) //each Day
.SelectMany(g=> g.Max(r=>.Field<datetime>("Date")) //item in group with max time
.Select(r=> new {
ID = r.Field<int>("ID"),
Status = r.Field<int>("Status"),
Date = r.Field<datetime>("Date")
});
我没有对此进行过测试，因此某处可能存在错误。我希望这可以帮助您找出解决问题的方法。
如果您需要任何东西，请告诉我^ _ ^
Andy
更新：好的 - 搞定了。 Max不是要走的路，而是我订购了我的约会日期，并取了每组的第一个。
我用过的查询是在那里，但这里是我设置的测试，原因是我之前有点犹豫不决的原因：
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)
{
////////////////////
// Here is it ^_^ //
////////////////////
var myLinq = dt.AsEnumerable()
.GroupBy(r=>new {
ID = r.Field<int>("ID"), //each ID
Date = r.Field<DateTime>("Date").Date})//each Day
.Select(g=> g.OrderByDescending(r=>r.Field<DateTime>("Date")).First() ) //item in group with max time
.Select(r=> new {
ID = r.Field<int>("ID"),
Status = r.Field<string>("Status"),
Date = r.Field<DateTime>("Date")
});
//Result output
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;
}
}
更新：OP需要返回类型的DataTable。
我们将在这里看到两种方式：
1：简单方法：
UPDATE: OP requires a return type of DataTable.
There are two ways we'll look at here:
1: The simple way:
//The return datatable will look just like the input dt
DataTable result = dt.Clone();
//Loop through the list of anon items and populate the new table
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 the new table
return result;
这很简单易懂。唯一的问题是它对这个案例非常具体。恕我直言的最佳方式是使用扩展方法：
2：最好的方法：
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:
//The class must be public static
public static class Extensions
{
// the method must be public static and use "this [type] [name]" as the first parameter
public static DataTable ToDataTable<T>(this IOrderedEnumerable<T> source)
{
//T could be misleading so return null
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);
});
//source is empty so return formated datatable
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;
}
}
一旦我们设置了Extension方法，我们就可以用它来将任何IOrderedEnumerable对象转换为数据表：
Once we have our Extension method set, we can use it to convert ANY IOrderedEnumerable object to a datatable:
//Make sure you have a "using" ref for the Extension class
DataTable result = myLinq.ToDataTable();
//return the new table
return result;
我会这样做：
I would do that this way:
var result = dt.AsEnumerable()
.GroupBy(r=>new{ID = r.Field<int>("ID"), Day = r.Field<DateTime>("Date").Date.ToString("yyyy-MM-dd")})
.Select(grp=>new
{
ID = grp.Key.ID,
Day = grp.Key.Day,
Date = grp.Max(d=>d.Field<DateTime>("Date")),
Status = grp.Where(p=>p.Field<DateTime>("Date")==grp.Max(d=>d.Field<DateTime>("Date"))).Select(s=>s.Field<string>("Status")).FirstOrDefault()
})
.OrderBy(a=>a.ID)
.ThenBy(b=>b.Date);
结果：
Result:
ID Day Date Status
2706 2015-08-27 2015-08-27 14:33:42 N
2707 2015-08-27 2015-08-27 14:27:11 N
2710 2015-08-25 2015-08-25 17:07:01 Y
2710 2015-08-27 2015-08-27 14:27:07 N
2755 2015-08-25 2015-08-25 17:06:59 Y
2755 2015-08-26 2015-08-26 11:27:35 Y
2755 2015-08-27 2015-08-27 14:26:49 N
这篇关于如何在linq中找到id的最大日期记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
