是逆透视（不透视）功能可在LINQ to SQL的？ 怎么样？(Is Unpivot (Not

我已经看到了这将提取您转动结果的职位，但不能逆转置，需要知道，如果有任何清洁的方式来实现？ 如果没有任何解决办法会做呢？

执行此看到Management Studio中unpivot的结果

CREATE TABLE [dbo].[Payment](
    [PaymentId] [int] NOT NULL,
    [EmployeeId] [int] NOT NULL,
    [RegularHours] [decimal](18, 0) NULL,
    [OvertimeHOurs] [decimal](18, 0) NULL
) ON [PRIMARY]

go

insert into payment values (1, 1, 40,  10)
insert into payment values (1, 2, 20,  0)

go

select * from payment 

select * from payment unpivot ([hours] for [paytype] in ([RegularHours], [OvertimeHOurs]))a

对于第一个SELECT语句的输出

PaymentId   EmployeeId  RegularHours                            OvertimeHOurs
----------- ----------- --------------------------------------- 
1           1           40                                      10
1           2           20                                      0

(2 row(s) affected)

对于第二个SELECT语句的输出与这就是我期待的

PaymentId   EmployeeId  hours                                   paytype
----------- ----------- ----------------------------------------------------- 
1           1           40                                      RegularHours
1           1           10                                      OvertimeHOurs
1           2           20                                      RegularHours
1           2           0                                       OvertimeHOurs

(4 row(s) affected)

Answer 1:

好吧，我不能明白的方式，你可以做到这一点它被翻译成SQL，下面是我想出了，但这是所有执行托管代码。

或者......你可以简单地创建SQL视图。

var payments = Payments.Select (p => new {
                            OvertimeHOurs = new {
                                    p.PaymentId,
                                    p.EmployeeId,
                                    Hours = p.OvertimeHOurs,
                                    PayType = "OvertimeHOurs"
                                    },
                            RegularHours = new {
                                    p.PaymentId,
                                    p.EmployeeId,
                                    Hours = p.RegularHours,
                                    PayType = "RegularHours"
                                    }
                            }
                );
var result = payments.Select(a => a.OvertimeHOurs).Union(payments.Select (p => p.RegularHours));
result.Dump(); // LINQPad Method

SQL生成的

-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'OvertimeHOurs'
DECLARE @p1 NVarChar(1000) = 'RegularHours'
-- EndRegion
SELECT [t4].[PaymentId], [t4].[EmployeeId], [t4].[OvertimeHOurs] AS [Hours], [t4].[value] AS [PayType]
FROM (
    SELECT [t1].[PaymentId], [t1].[EmployeeId], [t1].[OvertimeHOurs], [t1].[value]
    FROM (
        SELECT [t0].[PaymentId], [t0].[EmployeeId], [t0].[OvertimeHOurs], @p0 AS [value]
        FROM [payment] AS [t0]
        ) AS [t1]
    UNION
    SELECT [t3].[PaymentId], [t3].[EmployeeId], [t3].[RegularHours], [t3].[value]
    FROM (
        SELECT [t2].[PaymentId], [t2].[EmployeeId], [t2].[RegularHours], @p1 AS [value]
        FROM [payment] AS [t2]
        ) AS [t3]
    ) AS [t4]

Answer 2:

var result = new List<UnpivotedDbRecord>();
Payments.ForEach(r =>
                    {
                        result.Add(new UnpivotedDbRecord
                                        {
                                            EmployeeId = r.EmployeeId,
                                            PaymentId = r.PaymentId,
                                            PaymentType = "Regular",
                                            Hours = r.RegularHours
                                        });
                        result.Add(new UnpivotedDbRecord
                                        {
                                            EmployeeId = r.EmployeeId,
                                            PaymentId = r.PaymentId,
                                            PaymentType = "Overtime",
                                            Hours = r.OvertimeHours
                                        });
                    });

Answer 3:

随着500个paytypes如果你不处理大量数据，你需要使用反射来提取要unpivot的领域，它可能会很慢，但有效。 正因为如此，你将无法在已unpivot的操作转换为SQL，你将不得不把在摆动数据后做的LINQ到对象。 （理论上，你可以写一个代码生成器来创建查询，但我不知道SQL翻译将如何处理500列，或数据库引擎将如何处理好一个500元的工会。）

因此，这里是的回答的类型-该ShallowCopy方法用于避免设置枢轴（平面）数据为每个paytype：

public class UnpivotData {
    public int PaymentId { get; set; }
    public int EmployeeId { get; set; }
    public string PayType { get; set; }
    public decimal Hours { get; set; }

    public UnpivotData ShallowCopy() => (UnpivotData)this.MemberwiseClone();
}

现在扩展方法UNPIVOT您的数据很简单 - 提取透视数据，然后每paytype返回一个新的对象。 此方法采用串的用于枢轴的数据和用于选择用于paytypes的字段名称的λ谓词的名称的数组。

public static class IEnumerableExt {
    public static IEnumerable<UnpivotData> Unpivot<T>(this IEnumerable<T> src, string[] pivotFieldNames, string unPivotName, string unPivotValue, Func<string, bool> unpivotFieldNameFn) {
        var srcPIs = typeof(T).GetProperties();
        var srcPivotPIs = srcPIs.Where(pi => pivotFieldNames.Contains(pi.Name));
        var srcUnpivotPIs = srcPIs.Where(pi => unpivotFieldNameFn(pi.Name)).ToList();
        var ansPIs = typeof(UnpivotData).GetProperties();
        var ansPivotPIs = ansPIs.Where(pi => pivotFieldNames.Contains(pi.Name));
        var srcAnsPivotPIs = srcPivotPIs.Zip(ansPivotPIs, (spi, api) => new { spi, api }).ToList();
        var unPivotNamePI = ansPIs.First(pi => pi.Name == unPivotName);
        var unPivotValuePI = ansPIs.First(pi => pi.Name == unPivotValue);

        foreach (var d in src) {
            var ansbase = new UnpivotData();
            foreach (var sapi in srcAnsPivotPIs)
                sapi.api.SetValue(ansbase, sapi.spi.GetValue(d));

            foreach (var spi in srcUnpivotPIs) {
                var ans = ansbase.ShallowCopy();
                unPivotNamePI.SetValue(ans, spi.Name);
                unPivotValuePI.SetValue(ans, spi.GetValue(d));

                yield return ans;
            }
        }
    }
}

现在，您可以使用Unpivot方法unpivot的任何数量的paytypes的：

var result = payments.AsEnumerable().Unpivot(new[] { "PaymentId", "EmployeeId" }, "PayType", "Hours", fn => fn.EndsWith("Hours"));

文章来源: Is Unpivot (Not Pivot) functionality available in Linq to SQL? How?