c# - 使用来自动态数据透视表的匿名列加入列表

Question

我有两个共享公共(public)字段的列表。我想加入公共(public)字段上的列表，但是，其中一个列表来自 SQL 动态数据透视表，因此该列表中的所有列名(链接字段除外)都具有未知的列名。我的问题是如何找到这些列名称以便创建新列表？

示例

class Student 
{
 int StudentID {get; set;}
 string FirstName {get; set;}
 string LastName {get; set;}
}

studentCollection 是学生的集合

我在这里使用 class ReportRanking 作为示例。它是一个 dynamic 类，从使用动态数据透视表的存储过程返回。所以我不知道列名提前。我正在使用 TestScore-1、TestScore-2 等作为占位符来显示返回的内容。列名称将包含学生参加的测试的名称。列中的值将是他们收到的分数。

class StudentTestScores
{
 int StudentID {get; set;}
 int TestScore-1 {get; set;}
 int TestScore-2 {get; set;}
 int TestScore-3 {get; set;}
 ...
}

testResultCollection 是 StudentScores 的集合。

+-----------+---------+----------+----------+---------+
| StudentId | History | Algebra | Geometry | Biology |
+-----------+---------+----------+----------+---------+
|     1     |    88   |    96    |    87    |    91   |
+-----------+---------+----------+----------+---------+
|     2     |    92   |    75    |    88    |    74   |
+-----------+---------+----------+----------+---------+

因为结果来自动态数据透视表，所以我在编译时不知道 StudentTestScores 中列的名称是什么。它们代表学生参加的考试的名称。如何引用列名称以便将列表组合成新的复合列表？

var testResults = from student in studentCollection 
                    join testResult in testResultCollection 
                      on student.StudentId equals testResult.StudentId 
                    select new {
                      student.StudentId,
                      student.FirstName,
                      student.LastName,
                      testResult.XXXXXXX // Not sure how to reference the test scores
                      ...
                    }

这就是我需要结束的......

+-----------+-----------+----------+---------+---------+----------+---------+
| StudentId | FirstName | LastName | History | Algebra | Geometry | Biology |
+-----------+-----------+----------+---------+---------+----------+---------+
|     1     | Bob       | Smith    |    88   |    96   |    87    |    91   |
+-----------+-----------+----------+---------+---------+----------+---------+
|     2     | Sally     | Jenkins  |    92   |    75   |    88    |    74   |
+-----------+-----------+----------+---------+---------+----------+---------+

Answer 1

我会将您的对象组合成一个 ExpandoObject，并将其作为动态返回。这样你就可以像往常一样访问它的属性(因为它是动态的)并且任何反射代码(比如序列化到 json\csv)也将能够像往常一样探索它的属性。这是代码:

class Student
{
    public int StudentId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class StudentTestScores {
    public int StudentId { get; set; }
    public int History { get; set; }
    public int Algebra { get; set; }
    public int Geometry { get; set; }
    public int Biology { get; set; }
}

static void Main(string[] args) {
    var studentCollection = new List<Student>(new [] {
        new Student() {StudentId = 1, FirstName = "Test", LastName = "Test"}, 
    });
    var testResultCollection = new List<StudentTestScores>(new [] {
        new StudentTestScores() {StudentId = 1, Algebra = 2, Biology = 5, Geometry = 3}, 
    });
    var testResults = from student in studentCollection
                      join testResult in testResultCollection
                        on student.StudentId equals testResult.StudentId
                      select Combine(student, testResult);
    Console.WriteLine(JsonConvert.SerializeObject(testResults));
    // outputs [{"StudentId":1,"FirstName":"Test","LastName":"Test","History":0,"Algebra":2,"Geometry":3,"Biology":5}]
    Console.ReadKey();
}



static dynamic Combine(params object[] objects) {            
    var exp = (IDictionary<string, object>) new ExpandoObject();
    foreach (var o in objects) {
        var dict = o as IDictionary<string, object>;
        if (dict != null) {
            foreach (var prop in dict) {
                if (!exp.ContainsKey(prop.Key)) {
                    exp.Add(prop.Key, prop.Value);
                } 
            }
        }
        else {
            foreach (var prop in o.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public)) {
                if (prop.CanRead && !exp.ContainsKey(prop.Name)) {
                    exp.Add(prop.Name, prop.GetValue(o));
                }
            }
        }
    }            
    return exp;
}