C# Linq rows to column

Question

我想将linq结果从行转换为列，字段名称是用户可更改的，因此我需要该函数是动态的。

示例数据

ID: 331   FieldName: "BusinessCategory"  FieldContents: "Regulatory" 
ID: 331   FieldName: "PriorityGroup"     FieldContents: "Must Do" 
ID: 332   FieldName: "BusinessCategory"  FieldContents: "Financial" 
ID: 332   FieldName: "PriorityGroup"     FieldContents: "Should Do" 

将其转换为（示例端输出）

ID   BusinessCategory    PriorityGroup
331  Regulatory          Must Do
332  Financial           Should DO

下面是要从数据库中提取到字段名称和内容的代码块。

public static IEnumerable<InitProjectValues1> GetProgramInitiativeAttributesPart1(int id)
{
    using (dpm db = new dpm())
    {
        string partit = (string)HttpContext.Current.Session["sitePart"];
        var configrefs = from c in (
                from e in db.Metrics
                join j in db.ProgramLink on e.ProjectRef equals j.LinkedProject
                where (j.ProjectRef == id) && e.PartitNo == partit                    
                select new
                {
                    FieldName = e.FieldName,
                    FieldContents = e.MetricValue,
                    ProjectRef = e.ProjectRef,
                })
                         select new InitProjectValues1
                         {
                             ProjectRef = c.ProjectRef,
                             FieldName = c.FieldName,                                 
                             FieldContents = c.FieldContents,
                         };         //somewhere here would be the code to cover this into a single row per ProjectRef number.
        return configrefs.ToList();
    }
}

这是数据模型。

public class InitProjectValues1
{
    public int? ProjectRef { get; set; }
    public string FieldName { get; set; }
    public string FieldContents { get; set; }
}

我真的不知道从这里去哪里，希望有人可以提供指导/示例代码

Answer 1

您需要

的操作类型称为透视。您有效地围绕唯一的 productRef 旋转表并将行更改为列。

您可以尝试使用动态列生成所需的动态对象。

var configrefs = from c in (
            from e in db.Metrics
            join j in db.ProgramLink on e.ProjectRef equals j.LinkedProject
            where (j.ProjectRef == id) && e.PartitNo == partit                    
            select new
            {
                FieldName = e.FieldName,
                FieldContents = e.MetricValue,
                ProjectRef = e.ProjectRef,
            }).ToArray();
return configrefs.ToPivotArray(
                                i => i.FieldName, 
                                i => i.ProjectRef,
                                items => items.Any() ? items.FirstOrDefault().FieldContents : null);

获取动态对象的私有方法：

private static dynamic GetAnonymousObject(IEnumerable<string> columns, IEnumerable<object> values)
{
   IDictionary<string, object> eo = new ExpandoObject() as IDictionary<string, object>;
   int i;
   for (i = 0; i < columns.Count(); i++)
   {
       eo.Add(columns.ElementAt<string>(i), values.ElementAt<object>(i));
   }
   return eo;
}

和扩展方法

public static dynamic[] ToPivotArray<T, TColumn, TRow, TData>(
    this IEnumerable<T> source,
    Func<T, TColumn> columnSelector,
    Expression<Func<T, TRow>> rowSelector,
    Func<IEnumerable<T>, TData> dataSelector)
{
       var arr = new List<object>();
       var cols = new List<string>();
       String rowName = ((MemberExpression)rowSelector.Body).Member.Name;
       var columns = source.Select(columnSelector).Distinct();       
       cols =(new []{ rowName}).Concat(columns.Select(x=>x.ToString())).ToList();

       var rows = source.GroupBy(rowSelector.Compile())
                        .Select(rowGroup => new
                        {
                            Key = rowGroup.Key,
                            Values = columns.GroupJoin(
                                rowGroup,
                                c => c,
                                r => columnSelector(r),
                                (c, columnGroup) => dataSelector(columnGroup))
                        }).ToArray();

       foreach (var row in rows)
       {
           var items = row.Values.Cast<object>().ToList();
           items.Insert(0, row.Key);
           var obj = GetAnonymousObject(cols, items);
           arr.Add(obj);               
       }
       return arr.ToArray();
}

Answer 2

修改了 ToPivotArray 扩展以处理多个列选择器（使用匿名类作为列选择器）

public static dynamic[] ToPivotArrayNew<T, TColumn, TRow, TData>(
    this IEnumerable<T> source,
    Func<T, TColumn> columnSelector,
    Expression<Func<T, TRow>> rowSelector,
    Func<IEnumerable<T>, TData> dataSelector)
{
    var arr = new List<object>();
    var cols = new List<string>();
    List<string> rowNames = new List<string>();
    bool isObjectSelector = false;
    if (rowSelector.Body.GetType() == typeof(MemberExpression))
    {
        rowNames.Add(((MemberExpression)rowSelector.Body).Member.Name);
    }
    else if (rowSelector.Body.GetType() == typeof(NewExpression))
    {
        isObjectSelector = true;
        ((NewExpression)rowSelector.Body).Members.ToList().ForEach(m => rowNames.Add(m.Name));
    }
    var columns = source.Select(columnSelector).Distinct();
    cols = rowNames.ToArray().Concat(columns.Select(x => x.ToString())).ToList();

    var rows = source.GroupBy(rowSelector.Compile())
                     .Select(rowGroup => new
                     {
                         Key = rowGroup.Key,
                         Values = columns.GroupJoin(
                             rowGroup,
                             c => c,
                             r => columnSelector(r),
                             (c, columnGroup) => dataSelector(columnGroup))
                     }).ToArray();

    foreach (var row in rows)
    {
        var items = row.Values.Cast<object>().ToList();
        if (isObjectSelector)
        {
            for (int i = 0; i < rowNames.Count(); i++)
            {
                items.Insert(i, row.Key.GetType().GetProperty(rowNames[i]).GetValue(row.Key));
            }
        }
        else
        {
            items.Insert(0, row.Key);
        }

        var obj = GetAnonymousObject(cols, items);
        arr.Add(obj);
    }
    return arr.ToArray();
}