C# Linq rows to column

Question

I would like to turn linq result into columns from rows, the field names are user changeable so I need the function to be dynamic.

sample data

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"

Turn it into (sample end output)

ID   BusinessCategory    PriorityGroup
331  Regulatory          Must Do
332  Financial           Should DO

Here is the code block to extract to fieldnames and contents from the database.

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();
    }

}

Here is the data model.

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

I really don't know where to go from here, hoping someone can provide guidance / sample code

check this link.. http://stackoverflow.com/questions/6854146/convert-rows-into-columns-in-linq — Sharad Patel, Mar 02 '16 at 12:36

score 0 · Accepted Answer · answered Mar 02 '16 at 12:48

The kind of operation you need is called a pivot. You are effectively rotating the table around a unique productRef and changing the rows to columns.

You could try this which makes use of a dynamic object which you require for dynamic column generation.

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 method to get dynamic object:

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;
}

And the extension method

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();
}

Thank you, just what I was looking for. – 2bitcoder Mar 03 '16 at 09:56 — 2bitcoder, Mar 03 '16 at 09:56

Abraham Sabel · Answer 2 · 2022-07-21T16:35:13.577

Modified the ToPivotArray extension to handle multiple column selectors (using an anonymous class as the column selector)

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();
}

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jul 22 '22 at 23:36

C# Linq rows to column

2 Answers2