C# Linq 透视数据与变量列
本文关键字:变量 数据 Linq 透视 | 更新日期: 2023-09-27 18:37:25
在下面的代码中,我返回的数据将具有可变数量的列。因此,它可以返回列 'a,b,c,d,e' 或者它可以返回列 'a,b,c,g,m,n' - 设置了前三列,但随后可能会有任意数量的附加列。 然后,我需要将数据作为匿名类型列表返回。 在下面的代码中,我展示了如何获取数据,对其进行透视,并将列添加到ArrayList objDataColumn
中,然后创建一个数据表,这确实让我接近了,但我无法弄清楚如何将数据作为列表返回。
与其让它返回datatable
行的列表,不如让它返回一个匿名类型的通用列表,如下所示(在这种情况下,"属性"是唯一的动态列):
{ EntitlementId = 477653184, FileSetTypeID = 146, FileTypeCode = "test", SourceSystemKey = "userkey", Entitlement = "Chg Mgrs - AppDev Sour GUI", Attribute = "Change Manager" }
////Applying linq for geting pivot output
var d = (from f in result
group f by new { f.EntitlementId, f.FileSetTypeID, f.FileTypeCode, f.SourceSystemKey, f.Entitlement }
into myGroup
where myGroup.Count() > 0
select new
{
myGroup.Key.EntitlementId,
myGroup.Key.FileSetTypeID,
myGroup.Key.FileTypeCode,
myGroup.Key.SourceSystemKey,
myGroup.Key.Entitlement,
ColumnName = myGroup.GroupBy(f => f.ColumnName).Select(m => new { Col = m.Key, Value = m.Max(c => c.Value) })
}).ToList();
//PART 2 - Distinct ColumnName
var cols = (
from a in result
select new { ColumnName = a.ColumnName }
).Distinct().ToList();
//PART 3 - Creating array for adding dynamic columns
ArrayList objDataColumn = new ArrayList();
//Fixed columns
objDataColumn.Add("FileTypeCode");
objDataColumn.Add("SourceSystemKey");
objDataColumn.Add("Entitlement");
//Add Subject Name as column in Datatable
for (int i = 0; i < cols.Count; i++)
{
objDataColumn.Add(cols[i].ColumnName);
}
//Add dynamic columns name to datatable dt
DataTable dt = new DataTable();
for (int i = 0; i < objDataColumn.Count; i++)
{
dt.Columns.Add(objDataColumn[i].ToString());
}
//PART 4 - Add data into datatable with respect to dynamic columns and dynamic data
for (int i = 0; i < d.Count; i++)
{
List<string> tempList = new List<string>();
tempList.Add(result[i].FileTypeCode.ToString());
tempList.Add(result[i].SourceSystemKey.ToString());
tempList.Add(result[i].Entitlement.ToString());
var res = d[i].ColumnName.ToList();
for (int j = 0; j < res.Count; j++)
{
tempList.Add(res[j].Value.ToString());
}
dt.Rows.Add(tempList.ToArray<string>());
}
//END PIVOT
IEnumerable<DataRow> rows = dt.AsEnumerable();
提前感谢,如果需要任何其他信息,请告诉我,希望这不会太混乱。
能够使用动态列表和 ExpandoObject 类来解决这个问题。这是代码,希望它对其他人有所帮助:
List<dynamic> dynList = new List<dynamic>();
for (int i = 0; i < d.Count; i++)
{
dynamic dynObj = new ExpandoObject();
((IDictionary<string, object>)dynObj).Add("FileTypeCode", result[i].FileTypeCode.ToString());
((IDictionary<string, object>)dynObj).Add("Fullname", result[i].Fullname.ToString());
((IDictionary<string, object>)dynObj).Add("Entitlement", result[i].Entitlement.ToString());
var res = d[i].ColumnName.ToList();
for (int j = 0; j < res.Count; j++)
{
((IDictionary<string, object>)dynObj).Add(res[j].Col.ToString(), res[j].Value.ToString());
}
dynList.Add(dynObj);
}
var dList = (from da in dynList select da).ToList();
return dList;