透视可观察的集合

Question

我在可观察集合中有这样的记录，

ID Department salary joingdate 
1    .NET   5000  04/08/2011
2    .NET   6000  04/07/2011
3    JAVA   7000  04/08/2011
4    JAVA   8000  04/07/2011
5    .NET   9000  04/06/2011

现在我想要新的可观察集合，例如

Joingdate    .NET(Salary)  JAVA(Salary)
04/08/2011  5000        7000
04/07/2011  6000        8000
04/06/2011  9000        NULL

如何获得这种类型的可观察集合？

Answer 1

假设您有与您的结构类似的以下结构：

public class Data1
{
    public int Id { get; set; }
    public String Dep { get; set; }
    public int Sal { get; set; }
    public String JoinDate { get; set; }
}

public class Data2
{
    public Data2()
    {
        Sal = new List<int>();
    }

    public List<int> Sal { get; set; }
    public String JoinDate { get; set; }

    public override string ToString()
    {
        return Sal.Aggregate(JoinDate, (current, s) => current + s.ToString());
    }
}

并遵循可观察的集合：

public class Data1List : ObservableCollection<Data1>
{
    public Data1List()
    {
        Add(new Data1{ Id = 1, Dep = ".NET", Sal = 5000, JoinDate = "04/08/2011"});
        Add(new Data1{ Id = 2, Dep = ".NET", Sal = 6000, JoinDate = "04/07/2011"});
        Add(new Data1{ Id = 3, Dep = "JAVA", Sal = 7000, JoinDate = "04/08/2011"});
        Add(new Data1{ Id = 4, Dep = "JAVA", Sal = 8000, JoinDate = "04/07/2011"});
        Add(new Data1{ Id = 5, Dep = ".NET", Sal = 9000, JoinDate = "04/06/2011"});
    }
}

public class Data2List : ObservableCollection<Data2>
{
}

您可以尝试此代码来解决您的问题，或者至少让您走上可能更好的解决方案：

var l1 = new Data1List();
var l2 = new Data2List();

foreach (var items in l1.GroupBy(d => d.JoinDate))
{
    var d2 = new Data2 { JoinDate = items.Key };
    foreach (var item in items)
        d2.Sal.Add(item.Sal);
    l2.Add(d2);
}

我希望这可以帮助您找到解决方案！ :)

Answer 2

不久前，我尝试了旋转和 ExpandoObject。这当然不是生产代码。

public static dynamic pivot(IEnumerable<Employee> rows)
{
    IDictionary<string, Object> expando = new ExpandoObject();
    expando["Joindate"] = rows.First().Joindate;
    foreach (var row in rows)
    {
        expando[row.Department] = row.Salary;
    }
    return (dynamic)expando;
}

然后以某种方法在某个地方

var employees = new ObservableCollection<Employee>() { 
    new Employee() {ID=1, Department="NET", Salary=5000, Joindate=new DateTime(2011,04,08)},
    new Employee() {ID=2, Department="NET", Salary=6000, Joindate=new DateTime(2011,04,07)},
    new Employee() {ID=3, Department="JAVA", Salary=7000, Joindate=new DateTime(2011,04,08)},
    new Employee() {ID=4, Department="JAVA", Salary=8000, Joindate=new DateTime(2011,04,07)},
    new Employee() {ID=5, Department="NET", Salary=9000, Joindate=new DateTime(2011,04,06)}
};

var distinctDates = employees.Select(j => j.Joindate).Distinct().OrderByDescending(d => d);

var salaryByDepartmentAndJoindate = distinctDates.Select(d => pivot(employees.Where(jd => jd.Joindate == d)));

var result = new ObservableCollection<dynamic>(salaryByDepartmentAndJoindate);

Answer 3

由于数据的使用方式，我需要表格格式的结果，而不是嵌套对象格式。因此，下面的代码从数据列表中生成一个数据透视表。它的调用方式如下：

    // generate a pivot table
    var pivot = linqQueryResults.Pivot(
        rowKey => rowKey.DepartmentName,
        columnKey => columnKey.JoiningDate,
        value => value.Sum(emp => emp.Salary),
        "Department",
        new Dictionary<string, Func<GetComplianceForClientCurriculums_Result, object>>()
            {
                {"DepartmentCode", extraRow => extraRow.DepartmentCode},
                {"DepartmentManager", extraRow => extraRow.DeptManager}
            }
    );

LINQ 扩展方法如下所示：

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace MyApplication.Extensions 
{

    public static class LinqExtenions 
    {
        /// <summary>
        ///     Groups the elements of a sequence according to a specified firstKey selector 
        ///     function and rotates the unique values from the secondKey selector function into 
        ///     multiple values in the output, and performs aggregations. 
        /// </summary>
        /// <param name="source">The data source for the pivot</param>
        /// <param name="rowKeySelector">A function to derive the key for the rows</param>
        /// <param name="columnKeySelector">A function to derive the key for the columns</param>
        /// <param name="valueSelector">A function to calculate the contents of the intersection element. Usually this is an aggreation function</param>
        /// <param name="firstColumnName">The label to give the first column (row title)</param>
        /// <param name="additionalHeaderSelectors">An optional dictionary of additional rows to use as headers. Typically, this data should be consistent with the row selector since only the first match is taken.</param>
        /// <returns>A datatable pivoted from the IEnumerable source.</returns>
        /// <remarks>
        /// Based on concepts from this article: http://www.extensionmethod.net/Details.aspx?ID=147
        /// </remarks>
        public static DataTable Pivot<TSource, TRowKey, TColumnKey, TValue>(this IEnumerable<TSource> source, Func<TSource, TRowKey> rowKeySelector, Func<TSource, TColumnKey> columnKeySelector, Func<IEnumerable<TSource>, TValue> valueSelector, string firstColumnName = "", IDictionary<string, Func<TSource, object>> additionalHeaderSelectors = null)
        {
            var result = new DataTable();

            // determine what columns the datatable needs and build out it's schema
            result.Columns.Add(new DataColumn(firstColumnName));
            var columnNames = source.ToLookup(columnKeySelector);
            foreach (var columnName in columnNames)
            {
                var newColumn = new DataColumn(columnName.Key.ToString());
                result.Columns.Add(newColumn);
            }

            // if we have a 2nd header row, add it
            if (additionalHeaderSelectors != null)
            {
                foreach (var additionalHeaderSelector in additionalHeaderSelectors)
                {
                    var newRow = result.NewRow();

                    newRow[firstColumnName] = additionalHeaderSelector.Key;

                    foreach (var columnName in columnNames)
                    {
                        newRow[columnName.Key.ToString()] = additionalHeaderSelector.Value(columnName.FirstOrDefault());
                    }

                    result.Rows.Add(newRow);
                }
            }


            // build value rows
            var rows = source.ToLookup(rowKeySelector);
            foreach (var row in rows)
            {
                var newRow = result.NewRow();

                // put the key into the first column
                newRow[firstColumnName] = row.Key.ToString();

                // get the values for each additional column
                var columns = row.ToLookup(columnKeySelector);
                foreach (var column in columns) 
                {
                    newRow[column.Key.ToString()] = valueSelector(column);
                }

                result.Rows.Add(newRow);
            }

            return result;
        }
    }
}