c# - c# DataTable Linq Group by Multiple column sum int and timespan
问题描述
我想将 DataTable Alias 中的 Group BY 之后的所有 Volume & TotalTimes 列相加为 Temp_DT。记住 Volume & All TotalTimes 列可能有空值或空白值。谁能帮我通过Linq 方法获取代码
样本 Temp_DT:
我已经尝试了下面的代码,但 Temp_DT 结果为 Null。
var Temp_DT = tempdt.AsEnumerable().Select(x =>
new
{
UID = x.Field<string>("UID"),
EMPNAME = x.Field<string>("Emp Name"),
EMPROLE = x.Field<string>("Role"),
SUPID = x.Field<string>("Sup ID"),
SUPNAME = x.Field<string>("Sup Name"),
DESIGNATION = x.Field<string>("Designation"),
VOLUME = x.Field<int>("Volume"),
LOGINTIME = x.Field<TimeSpan>("Login Time"),
BREAKTIME = x.Field<TimeSpan>("Break Time"),
HANDLETIME = x.Field<TimeSpan>("Handle Time"),
ACTIVETIME = x.Field<TimeSpan>("Active Time"),
HOLDTIME = x.Field<TimeSpan>("Hold Time"),
ACWTIME = x.Field<TimeSpan>("ACW"),
IDLETIME = x.Field<TimeSpan>("Idle"),
PRODUCTIVE = x.Field<TimeSpan>("Productive"),
NONPRODUCTIVE = x.Field<TimeSpan>("Non Productive"),
USERERROR = x.Field<TimeSpan>("User Error Time")
}).GroupBy(s => new { s.UID, s.EMPNAME, s.EMPROLE, s.SUPID, s.SUPNAME, s.DESIGNATION })
.Select(g => new
{
g.Key.UID,
g.Key.EMPNAME,
g.Key.EMPROLE,
g.Key.SUPID,
g.Key.SUPNAME,
g.Key.DESIGNATION,
VOLUME = g.Sum(x => Convert.ToInt16(x.VOLUME)),
LOGINTIME = new TimeSpan(g.Sum(x => x.LOGINTIME.Ticks)),
BREAKTIME = new TimeSpan(g.Sum(x => x.BREAKTIME.Ticks)),
HANDLETIME = new TimeSpan(g.Sum(x => x.HANDLETIME.Ticks)),
ACTIVETIME = new TimeSpan(g.Sum(x => x.ACTIVETIME.Ticks)),
HOLDTIME = new TimeSpan(g.Sum(x => x.HOLDTIME.Ticks)),
ACWTIME = new TimeSpan(g.Sum(x => x.ACWTIME.Ticks)),
IDLETIME = new TimeSpan(g.Sum(x => x.IDLETIME.Ticks)),
PRODUCTIVE = new TimeSpan(g.Sum(x => x.PRODUCTIVE.Ticks)),
NONPRODUCTIVE = new TimeSpan(g.Sum(x => x.NONPRODUCTIVE.Ticks)),
USERERROR = new TimeSpan(g.Sum(x => x.USERERROR.Ticks)),
});
解决方案
您必须使用 解析and TimeSpan
,如下所示。Sum
Ticks
var Temp_DT = tempdt.AsEnumerable().Select(x =>
new
{
UID = x["UID"],
EMPNAME = x["Emp Name"],
EMPROLE = x["Role"],
SUPID = x["Sup ID"],
SUPNAME = x["Sup Name"],
DESIGNATION = x["Designation"],
VOLUME = x["Volume"],
ERRORTIME = x["Error_Time"],
ACWTIME = x["ACW"],
BREAKTIME = x["Break Time"],
IDLETIME = x["Idle"],
NONPRODUCTIVE = x["Non Productive"],
}).GroupBy(s => new { s.UID, s.EMPNAME, s.EMPROLE, s.SUPID, s.SUPNAME, s.DESIGNATION })
.Select(g => {
var grouped = g.ToList();
return new
{
UID = g.Key.UID,
EMPNAME = g.Key.EMPNAME,
EMPROLE = g.Key.EMPROLE,
SUPID = g.Key.SUPID,
SUPNAME = g.Key.SUPNAME,
DESIGNATION = g.Key.DESIGNATION,
VOLUME = grouped.Sum(x => Convert.ToInt16(x.VOLUME)),
Error_Time = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.ERRORTIME.ToString())).ToList().Sum(r=> r.Ticks)),
ACW = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.ACWTIME.ToString())).ToList().Sum(r=> r.Ticks)),
Break = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.BREAKTIME.ToString())).ToList().Sum(r=> r.Ticks)),
Idle = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.IDLETIME.ToString())).ToList().Sum(r=> r.Ticks)),
NonProductive = new TimeSpan(grouped.Select(x => ConvertTimeSpan(x.NONPRODUCTIVE.ToString())).ToList().Sum(r=> r.Ticks))
};
}).ToList();
转换方法是,
private static TimeSpan ConvertTimeSpan(string str)
{
TimeSpan outputValue;
TimeSpan.TryParse(str, out outputValue);
return outputValue;
}
C# 摆弄示例数据。
推荐阅读
- node.js - 应用装饰器时,在函数方法中未设置 this
- reactjs - 发送更新请求以更新服务器端未定义的配置文件
- c - 如何解决此段错误?
- reactjs - React-navigation 并不总是将参数传递给屏幕
- r - 当字符串长度不同时,R将字符串拆分为多列,dplyr
- ios - UITalbeViewCell 在 reloadSection 后闪烁
- java - Android Retrofit2:创建片段时如何读取和设置 EditText 值?
- javascript - 在添加到列表之前搜索重复项
- algorithm - 从源到接收器的步行次数,恰好是 h 个跃点
- sql - 在 PostgreSQL 中使用 @extschema@ 是一个语法错误