Is Unpivot (Not Pivot) functionality available in Linq to SQL? How?

Question

I have seen posts which would fetch you pivot results but not unpivot, Need to know if there is any clean way to achieve ? If not the any workaround would do as well ?

Execute this to see unpivot results in Management Studio

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

The output for first Select statement

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

(2 row(s) affected)

The output for second Select statement & this is what i am looking for

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

Ok, I cant see a way you can do it where it is translated into SQL, below is what I have come up with but this is all performed managed code.

Or... you can simply create a view in 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 Generated is

-- 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

With 500 paytypes you will need to use reflection to extract the fields you want to unpivot, which can be slow, but effective if you aren't handling large amounts of data. Because of this, you won't be able have the unpivot operation translate to SQL, you will have to do it in LINQ to Objects after bringing over the pivoted data. (Theoretically you could write a code generator to create the query but I am not sure how well SQL translation would handle the 500 columns, or how well the database engine would handle a 500 element union.)

So here is the type for the answer - the ShallowCopy method is used to avoid setting the pivot (flat) data for each 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();
}

Now the extension method to unpivot your data is straightforward - extract the pivot data and then return one new object per paytype. This method takes an array of strings for the names of the pivot data and a lambda predicate for selecting the field names that are for 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;
            }
        }
    }
}

Now you can use the Unpivot method to unpivot any number of paytypes:

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