如何通过cq中的linq获取价值 [英] How to get value through linq in c#
问题描述
我有一个数据表
Column0 | Column1 |第2栏
04/05/2015 00:10 | xyz | ON
04/05/2015 00:20 | xyz | OFF
04/05/2015 00:30 | abc | ON
04/05/2015 00:40 | abc |关闭
我想要这个
DateTime | SrNo | Ontime |关闭时间| RunningTime
04/05/2015 00:10 | 1 | 00:10 | 00:20 | 00:10 //(关闭时间) - (准时)
im使用此linq查询
< pre lang =c#> var query = MyDataTable.AsEnumerable()
.Where(r = > r.Field< string>( Column0)。包含( 04/05/2015))
.Where(r = > r.Field< string>( Column1)。包含( xyz))
// 。选择(r => DateTime.Parse(r.Field< string>(Column0))。ToString(dd / MM / yyyy HH:mm:ss ));
。选择((r,迭代器)= > new
{
DDate = DateTime.Parse(r.Field< string>( Column0) ).ToString( dd / MM / yyyy HH:mm:ss),
SrNo = iterator + 1 ,
On = r.Field< string>( Column2)。包含( ON) ,
Off = r.Field< string>( Column2)。包含(< span class =code-string> OFF)
Running = r.Field< string>( Column2)
});
如何获得正确的结果
帮助我
中为了达到您想要的结果,您必须根据Column1对记录进行分组并从中获取结果。
这是LINQ查询,用于获取您要查找的结果,但不完全符合您要求的格式。此查询需要进行一些小的更改才能以您需要的格式获得结果。我要离开它试试。
int iteration = 0 ;
var result = 来自 dr MyDataTable.AsEnumerable()
group dr dr.Field< string>( column1) into 分组
选择 new
{
Name = grouped.Key,
SrNo = ++ iteration,
OnTime =(< span class =code-sdkkeyword> from dr1 in 分组
选择 dr1.Field< string>( column0))。Min(),
OffTime =(来自 dr1 分组
选择 dr1.Field< string>( column0))。Max (),
RunTime = Convert.ToDateTime((来自 dr1 分组
选择 dr1.Field< string>( column0))。Max()) -
Convert.ToDateTime(( from dr1 in 分组
选择 dr1.Field< string>( column0))。Min())
};
检查:
DataTable one = new DataTable();
one.Columns.Add( new DataColumn( Column0,Type.GetType( System.DateTime)));
one.Columns.Add( new DataColumn( Column1,Type.GetType( System.String)));
one.Columns.Add( new DataColumn( Column2,Type.GetType( System.String)));
one.Rows.Add( new Object [] { new DateTime( 2015 , 5 , 4 , 0 , 10 , 0 ), xyz, ON});
one.Rows.Add( new Object [] { new DateTime( 2015 , 5 , 4 , 0 , 20 , 0 ), xyz, OFF});
one.Rows.Add( new Object [] { new DateTime( 2015 , 5 , 4 , 0 , 30 , 0 ), abc, ON});
one.Rows.Add( new Object [] { new DateTime( 2015 , 5 , 4 , 0 , 40 , 0 ), abc, OFF});
var qry = one.AsEnumerable()
.GroupBy(r => new {DDate = r .Field< DateTime>( Column0)。ToString( @ yyyy / MM / dd),Id = r.Field< string>( Column1)})
。选择((g,i)=> new
{
Id = g .Key.Id,
SrNo = i + = 1 ,
DDate = g.Key.DDate,
OnTime = g.Min (c => c.Field< DateTime>( Column0))。ToString( @ HH:mm:ss),
OfTime = g.Max(c => c.Field< DateTime>( Column0))。ToString ( @ HH:mm:ss),
RunningTime =(g.Max( c => c.Field< DateTime>( Column0)) - g.Min(c => c.Field< DateTime>( Column0)))。TotalMinutes
});
结果:
Id SrNo DDate OnTime OfTime RunningTime
xyz 1 2015-05-04 00:10:00 00:20:00 10
abc 2 2015-05-04 00:30:00 00:40:00 10
I have one datatable
Column0 | Column1 | Column2
04/05/2015 00:10 | xyz | ON
04/05/2015 00:20 | xyz | OFF
04/05/2015 00:30 | abc | ON
04/05/2015 00:40 | abc | OFF
I want this
DateTime | SrNo | Ontime | Off Time | RunningTime
04/05/2015 00:10 | 1 | 00:10 | 00:20 | 00:10 //(off time) - (on time)
i m using this linq query
var query = MyDataTable.AsEnumerable()
.Where(r => r.Field<string>("Column0").Contains("04/05/2015"))
.Where(r => r.Field<string>("Column1").Contains("xyz"))
//.Select(r => DateTime.Parse(r.Field<string>("Column0")).ToString("dd/MM/yyyy HH:mm:ss"));
.Select((r, iterator) => new
{
DDate = DateTime.Parse(r.Field<string>("Column0")).ToString("dd/MM/yyyy HH:mm:ss"),
SrNo = iterator + 1,
On = r.Field<string>("Column2").Contains("ON"),
Off = r.Field<string>("Column2").Contains("OFF")
Running = r.Field<string>("Column2")
});
How to get correct result
Help me
In order to achieve the result you are looking for, you have to group the records based on Column1 and get the result from that.
Here is the LINQ query to get the result you are looking for but not exactly in the same format you asked for. This query requires some minor changes to get the result in the format you need. I am leaving it for you to try.
int iteration = 0; var result = from dr in MyDataTable.AsEnumerable() group dr by dr.Field<string>("column1") into grouped select new { Name = grouped.Key, SrNo = ++iteration, OnTime = (from dr1 in grouped select dr1.Field<string>("column0")).Min(), OffTime = (from dr1 in grouped select dr1.Field<string>("column0")).Max(), RunTime = Convert.ToDateTime((from dr1 in grouped select dr1.Field<string>("column0")).Max()) - Convert.ToDateTime((from dr1 in grouped select dr1.Field<string>("column0")).Min()) };
Check this:
DataTable one = new DataTable(); one.Columns.Add(new DataColumn("Column0", Type.GetType("System.DateTime"))); one.Columns.Add(new DataColumn("Column1", Type.GetType("System.String"))); one.Columns.Add(new DataColumn("Column2", Type.GetType("System.String"))); one.Rows.Add(new Object[]{new DateTime(2015, 5, 4, 0, 10, 0), "xyz", "ON"}); one.Rows.Add(new Object[]{new DateTime(2015, 5, 4, 0, 20, 0), "xyz", "OFF"}); one.Rows.Add(new Object[]{new DateTime(2015, 5, 4, 0, 30, 0), "abc", "ON"}); one.Rows.Add(new Object[]{new DateTime(2015, 5, 4, 0, 40, 0), "abc", "OFF"}); var qry = one.AsEnumerable() .GroupBy(r=>new{DDate = r.Field<DateTime>("Column0").ToString(@"yyyy/MM/dd"), Id=r.Field<string>("Column1")}) .Select((g, i)=>new { Id = g.Key.Id, SrNo = i+=1, DDate = g.Key.DDate, OnTime = g.Min(c=>c.Field<DateTime>("Column0")).ToString(@"HH:mm:ss"), OfTime = g.Max(c=>c.Field<DateTime>("Column0")).ToString(@"HH:mm:ss"), RunningTime = (g.Max(c=>c.Field<DateTime>("Column0")) - g.Min(c=>c.Field<DateTime>("Column0"))).TotalMinutes });
Result:
Id SrNo DDate OnTime OfTime RunningTime xyz 1 2015-05-04 00:10:00 00:20:00 10 abc 2 2015-05-04 00:30:00 00:40:00 10
这篇关于如何通过cq中的linq获取价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!