LINQ中的动态枢轴:在运行时确定的枢轴列

本文关键字：运行时 LINQ 动态 | 更新日期: 2023-09-27 18:03:30

假设我有三个表。基本上，一个事物表，一个描述事物的可能属性的表，一个桥接表提供特定事物的这些属性值。这允许你做动态元数据。

用户可以随时添加元数据属性，然后他们可以为"things"表中的任何事物提供这些属性的值。

像这样:

Table: Persons
PersonID | FirstName | LastName 
1        | Bob       | Jones
2        | Fred      | Smith
3        | Sally     | Doe
Table: Properties
PropertyID | Name
1          | SupervisorName
2          | Age
3          | Birthday
4          | EmployeeNumber
5          | Hometown
Table: PropertyValues
PersonID | PropertyID | PropertyValue
1        | 1          | Frank Grimes
1        | 2          | 47
2        | 2          | 35
2        | 4          | 1983738
2        | 3          | 5/5/1978
3        | 3          | 4/4/1937
3        | 5          | Chicago, IL

因此，用户希望能够查看这些属性的报告。也许我想看到一个包含所有员工的年龄和生日的表。如果没有为这些用户填充这些值，表中就会出现空白。然后，也许我想看到一个包含主管、年龄和生日的报告——我也应该能够动态生成那个表。

现在，为了使用SQL完成此操作，我将动态构造一个查询，并为我想要枢轴到顶部的每个属性添加一个连接。这就是现在的工作方式。

如果我想在LINQ中这样做，并且在编写代码时我知道哪些属性需要pivot，我也可以这样做——我只需要使用GroupJoin()。

我不知道的是如何动态地构建一个LINQ查询，允许我在运行时透视任意数量的属性，而不知道它们是什么。

任何想法?

(在你下意识地把这个标记为重复之前，知道我在发布这个问题之前做了相当多的StackOverflow研究，如果这个确切的问题以前被问过，我找不到它。)

LINQ中的动态枢轴:在运行时确定的枢轴列

可以动态地构建linq表达式树。下面的MSDN文章涵盖了这个主题(包括示例):http://msdn.microsoft.com/en-us/library/bb882637.aspx

我的建议是为您的任务编写一个示例Linq查询，并使用表达式树以编程方式重新构建它。一旦它成功了，你就可以调整它并注入你的动态部分。

我的想法是你可以加入一个where条件，如下所示:

Person类

public class Person
{
    public int Id {get; set;}
    public string FirstName {get; set;}
    public string LastName {get; set;}
}
房地产类

public class Property
{
    public int Id {get; set;}
    public string Name {get; set;}
}
<<p> 价值类/strong> 
public class Value
{
    public int PersonId {get; set;} 
    public int PropertyId {get; set;}   
    public string Val {get; set;}
}


void Main()
{
    var selectBy = "Birthday";
    var persons = new List<Person>() { new Person {Id = 1, FirstName = "Bob", LastName = "Jones"}, new Person {Id = 2, FirstName = "Fred", LastName = "Smith"}, new Person {Id = 3,FirstName = "Sally", LastName = "Doe"}};
    var properties = new List<Property>() { new Property {Id = 1, Name = "SupervisorName"}, new Property {Id = 2, Name = "Age"}, new Property {Id = 3, Name = "Birthday"}, new Property {Id = 4, Name = "EmployeeNumber"}, new Property {Id = 5, Name = "Hometown"}};
    var values = new List<Value>() { new Value {PersonId = 1, PropertyId = 1, Val="Frank Grimes"}, new Value {PersonId = 1, PropertyId = 2, Val="47"}, new Value {PersonId = 2, PropertyId = 2, Val="35"}, new Value {PersonId = 2, PropertyId = 4, Val="1983738"}, new Value {PersonId = 2, PropertyId = 3, Val="5/5/1978"}, new Value {PersonId = 3, PropertyId = 3, Val="4/4/1937"}, new Value {PersonId = 3, PropertyId = 5, Val="Chicago, IL"}};

    var result = from v in values
                join p in persons on v.PersonId equals p.Id
                join p2 in properties on v.PropertyId equals p2.Id
                where p2.Name.Equals(selectBy)
                select new { Name = p.FirstName + " " + p.LastName,
                            Value = v.Val
                            };
    result.Dump();
}
结果

名称,值

Fred Smith 5/5/1978
Sally Doe 4/4/1937

修改答案

void Main()
{
    var selectBy = "Birthday";
    var persons = new List<Person>() { new Person {Id = 1, FirstName = "Bob", LastName = "Jones"}, new Person {Id = 2, FirstName = "Fred", LastName = "Smith"}, new Person {Id = 3,FirstName = "Sally", LastName = "Doe"}};
    var properties = new List<Property>() { new Property {Id = 1, Name = "SupervisorName"}, new Property {Id = 2, Name = "Age"}, new Property {Id = 3, Name = "Birthday"}, new Property {Id = 4, Name = "EmployeeNumber"}, new Property {Id = 5, Name = "Hometown"}};
    var values = new List<Value>() { new Value {PersonId = 1, PropertyId = 1, Val="Frank Grimes"}, new Value {PersonId = 1, PropertyId = 2, Val="47"}, new Value {PersonId = 2, PropertyId = 2, Val="35"}, new Value {PersonId = 2, PropertyId = 4, Val="1983738"}, new Value {PersonId = 2, PropertyId = 3, Val="5/5/1978"}, new Value {PersonId = 3, PropertyId = 3, Val="4/4/1937"}, new Value {PersonId = 3, PropertyId = 5, Val="Chicago, IL"}};

    // Default Values for the Cartesian Product
    var defaultValues = new string[]{"","","","",""};
    // propertyKeys are used to filter values generated for pivot table
    var propertyKeys = new List<Property> { new Property{Id=1}, new Property{Id=2}, new Property{Id=3}};
    // Generate default values for every person and each property
    var cartesianProduct = from ppl in persons
                            from prop in properties
                            join pk in propertyKeys on prop.Id equals pk.Id
                            select new {PersonId = ppl.Id, PropertyId = prop.Id, Val = defaultValues[prop.Id-1]};
    // Create Pivot Values based on selected PropertyIds
    var newValues = from cp in cartesianProduct 
                    join v in values on new {cp.PersonId, cp.PropertyId} equals new { v.PersonId, v.PropertyId } into gj
                    from x in gj.DefaultIfEmpty()
                    select new {
                        PersonId = (x == null ? cp.PersonId : x.PersonId),
                        PropertyId = (x == null ? cp.PropertyId: x.PropertyId),
                        Val = ( x == null ? cp.Val : x.Val )
                    };

    foreach( var y in newValues )
    {
        var aPerson = persons.Where( r=> r.Id == y.PersonId ).First().FirstName;
        var aProperty = properties.Where( r=> r.Id == y.PropertyId ).First().Name;
        Console.WriteLine(string.Format("{0:12}          {1:12}           {2:12}", aPerson, aProperty, y.Val));
    }
}

结果:

Bob       |   SupervisorName  |   Frank Grimes
Bob       |   Age             |   47
Bob       |   Birthday        |   
Fred      |   SupervisorName  |         
Fred      |   Age             |   35
Fred      |   Birthday        |   5/5/1978
Sally     |   SupervisorName  |         
Sally     |   Age             |
Sally     |   Birthday        |   4/4/1937
相关文章：