使用LINQ的动态枢轴可能吗？ | 使用LINQ的动态枢轴可能吗

本文介绍了使用LINQ的动态枢轴可能吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述

我有一个T-SQL 2005查询返回：
  pid propertyid displayname value 
 ---- ------- ----------- --------------- --------------- 
 14270790 74低价1.3614 
 14270790 75高价0 
 14270791 74低价1.3525 
 14270791 75高价0 
 14270792 74低价1.353 
 14270792 75高价格0 
 14270793 74低价1.3625 
 14270793 75高价0 
 14270794 74低价1.3524 
 14270794 75高价0 
  / pre> 
 
 我想做的是在 displayname 字段的基础上，希望产生：
  pid低价高价
 14270790 1.3614 0 
 14270791 1.3525 0 
 14270792 1.353 0 
 14270793 1.3625 0 
 14270794 1.3 524 0 
  
（不知道如何输出propertyid字段，所以我把它留下了（希望它将简单地坐在低价格和高价格的领域，以表示他们的身份证，但我不认为这将工作。）
 
 
 问题是，内容原始的 displayname 字段是动态的 - 它是由具有 PropertyName'表的连接生成的，因此枢轴列的数量是可变的。因此可以包含高价，低价，打开和关闭，这取决于该表的联接返回。
 
 
 当然这是比较容易的在编写初始查询！）中，以固定查询或存储过程生成此枢纽。但是，是否可以让LINQ生成一个SQL查询，它将命名每个列以生成，而不必编写列出列名的动态（可能在存储过程中）查询？
 
 
 谢谢，
 
 
  Matt。
解决方案
 p>我会给你一个不同数据的样本（我需要的）。你可以适应你的需要。注意，只有两个linq查询被使用，大多数其他绒毛是将列表转换为数据。 
  var data = new [] {
 new {Student = 1，Subject =English，Marks = 40 }，
 new {Student = 1，Subject =Maths，Marks = 50}，
 new {Student = 1，Subject =Science，Marks = 60}，
 new { Student = 1，Subject =Physics，Marks = 70}，
 new {Student = 1，Subject =Chemistry，Marks = 80}，
 new {Student = 1，Subject = ，Marks = 90}，
 new {Student = 2，Subject =English，Marks = 4}，
 new {Student = 2，Subject =Maths，Marks = 5} b $ b new {Student = 2，Subject =Science，Marks = 6}，
 new {Student = 2，Subject =Physics，Marks = 7}，
 new {Student = 2 ，Subject =Chemistry，Marks = 8}，
 new {Student = 2，Subject =Biology，Marks = 9} 
}; 
 
 / *这里的枢轴列是主题，静态列是student 
将静态列组合数据* / 
 
 var groups =从数据
组d由d.Student进入grp 
选择新
 {
 StudentId = grp.Key，
 Marks = grp.Select（d2 => ; new {d2.Subject，d2.Marks}）。ToArray（）
}; 
 
 
 / *将所有可能的科目分成一个单独的组* / 
 var subjects =（从数据中的d 
选择d.Subject）.Distinct（）; 
 
 DataTable dt = new DataTable（）; 
 
 / * for static cols * / 
 dt.Columns.Add（STUDENT_ID）; 
 
 
 / * for动态cols * / 
 foreach（主题中的主题）
 {
 dt.Columns.Add（subject.ToString（） ）; 
} 
 
 / *将数据转换成新的数据表* / 
 foreach（组中的var g）
 {
 DataRow dr = dt.NewRow （）; 
 dr [STUDENT_ID] = g.StudentId; 
 
 foreach（var mark in g.Marks）
 {
 dr [mark.Subject] = mark.Marks; 
} 
 dt.Rows.Add（dr）; 
} 
  
 
I have a T-SQL 2005 query which returns:
pid         propertyid  displayname     value
----------- ----------- --------------- ---------------
14270790    74          Low Price       1.3614
14270790    75          High Price      0
14270791    74          Low Price       1.3525
14270791    75          High Price      0
14270792    74          Low Price       1.353
14270792    75          High Price      0
14270793    74          Low Price       1.3625
14270793    75          High Price      0
14270794    74          Low Price       1.3524
14270794    75          High Price      0
What I would like to do is essentially pivot on the displayname field, hopefully producing:
pid       Low Price  High Price
14270790  1.3614     0
14270791  1.3525     0
14270792  1.353      0
14270793  1.3625     0
14270794  1.3524     0
(Not sure how the propertyid field would be output, so I left it out (was hoping it would simply sit alongside the Low Price and High Price fields, to indicate their IDs, but I don't think that will work.)
The problem is that the content of the original displayname field is dynamic - it is produced from a join with a PropertyName' table, so the number of pivoted columns is variable. It could therefore containHigh Price,Low Price,OpenandClose`, depending on what the join with that table returns.
It is, of course, relatively easy (regardless of the trouble I'm having writing the initial query!) to produce this pivot in a fixed query or stored proc.  However, is it possible to get LINQ to generate a SQL query which would name each column to be produced rather than having to write a dynamic (probably in a stored proc) query which lists out the column names?
Thanks,
Matt.
 解决方案 
I'll give you a sample with a different data (that I needed). You can adapt that to your need. Note only two linq queries are used, most of the other fluff is to convert a list into a datatable. 
         var data = new[] {
                new{Student=1, Subject="English", Marks=40},
                new{Student=1, Subject="Maths", Marks=50},
                new{Student=1, Subject="Science", Marks=60},
                new{Student=1, Subject="Physics", Marks=70},
                new{Student=1, Subject="Chemistry", Marks=80},
                new{Student=1, Subject="Biology", Marks=90},
                new{Student=2, Subject="English", Marks=4},
                new{Student=2, Subject="Maths", Marks=5},
                new{Student=2, Subject="Science", Marks=6},
                new{Student=2, Subject="Physics", Marks=7},
                new{Student=2, Subject="Chemistry", Marks=8},
                new{Student=2, Subject="Biology", Marks=9}
            };

        /*Here the pivot column is the subject and the static column is student
        group the data against the static column(s)*/

        var groups = from d in data
                     group d by d.Student into grp
                     select new
                     {
                         StudentId = grp.Key,
                         Marks = grp.Select(d2 => new { d2.Subject, d2.Marks }).ToArray()
                     };


        /*get all possible subjects into a separate group*/
        var subjects = (from d in data
                        select d.Subject).Distinct();

        DataTable dt = new DataTable();

        /*for static cols*/
        dt.Columns.Add("STUDENT_ID");


        /*for dynamic cols*/
        foreach (var subject in subjects)
        {
            dt.Columns.Add(subject.ToString());
        }

        /*pivot the data into a new datatable*/
        foreach (var g in groups)
        {
            DataRow dr = dt.NewRow();
            dr["STUDENT_ID"] = g.StudentId;

            foreach (var mark in g.Marks)
            {
                dr[mark.Subject] = mark.Marks;
            }
            dt.Rows.Add(dr);
        }
                        
这篇关于使用LINQ的动态枢轴可能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！