LINQ allows you to create both pivot and unpivot queries to the Entity Framework model. For this experiment I have a table with 6 columns: OperationDate and five columns Op1Result… Op5Result. Now I want to unpivot data to get a regular tables as it was in my previous post Querying Entity Model. Part 11 – Pivot
Unpivot operation can be made with two LINQ queries. First query will split table into separate result entities (rows). The second query will unite all rows into the regular table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
var split = context.PivotTable .Select(r => new { Op1 = new { Operation = "op1", Result = r.Op1Result, OperationDate = r.OperationDate }, Op2 = new { Operation = "op2", Result = r.Op2Result, OperationDate = r.OperationDate }, Op3 = new { Operation = "op3", Result = r.Op3Result, OperationDate = r.OperationDate }, Op4 = new { Operation = "op4", Result = r.Op4Result, OperationDate = r.OperationDate }, Op5 = new { Operation = "op5", Result = r.Op5Result, OperationDate = r.OperationDate } } ); var unpivot = split .Select(r => r.Op1).Where(r => r.Result != null) .Union(split.Select(x => x.Op2)).Where(x => x.Result != null) .Union(split.Select(x => x.Op3)).Where(x => x.Result != null) .Union(split.Select(x => x.Op4)).Where(x => x.Result != null) .Union(split.Select(x => x.Op5)).Where(x => x.Result != null); |
Entity Framework generates two SQL queries.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
SELECT 1 AS [C1], N'op1' AS [C2], [Extent1].[Op1Result] AS [Op1Result], [Extent1].[OperationDate] AS [OperationDate], N'op2' AS [C3], [Extent1].[Op2Result] AS [Op2Result], N'op3' AS [C4], [Extent1].[Op3Result] AS [Op3Result], N'op4' AS [C5], [Extent1].[Op4Result] AS [Op4Result], N'op5' AS [C6], [Extent1].[Op5Result] AS [Op5Result] FROM [dbo].[PivotTable] AS [Extent1] SELECT [Distinct4].[C1] AS [C1], [Distinct4].[C2] AS [C2], [Distinct4].[C3] AS [C3], [Distinct4].[C4] AS [C4] FROM ( SELECT DISTINCT [UnionAll4].[C1] AS [C1], [UnionAll4].[C2] AS [C2], [UnionAll4].[C3] AS [C3], [UnionAll4].[C4] AS [C4] FROM (SELECT [Distinct3].[C1] AS [C1], [Distinct3].[C2] AS [C2], [Distinct3].[C3] AS [C3], [Distinct3].[C4] AS [C4] FROM ( SELECT DISTINCT [UnionAll3].[C1] AS [C1], [UnionAll3].[C2] AS [C2], [UnionAll3].[C3] AS [C3], [UnionAll3].[C4] AS [C4] FROM (SELECT [Distinct2].[C1] AS [C1], [Distinct2].[C2] AS [C2], [Distinct2].[C3] AS [C3], [Distinct2].[C4] AS [C4] FROM ( SELECT DISTINCT [UnionAll2].[C1] AS [C1], [UnionAll2].[C2] AS [C2], [UnionAll2].[C3] AS [C3], [UnionAll2].[C4] AS [C4] FROM (SELECT [Distinct1].[C1] AS [C1], [Distinct1].[C2] AS [C2], [Distinct1].[C3] AS [C3], [Distinct1].[C4] AS [C4] FROM ( SELECT DISTINCT [UnionAll1].[C1] AS [C1], [UnionAll1].[C2] AS [C2], [UnionAll1].[Op1Result] AS [C3], [UnionAll1].[OperationDate] AS [C4] FROM (SELECT 1 AS [C1], N'op1' AS [C2], [Extent1].[Op1Result] AS [Op1Result], [Extent1].[OperationDate] AS [OperationDate] FROM [dbo].[PivotTable] AS [Extent1] WHERE ([Extent1].[Op1Result] IS NOT NULL) AND ([Extent1].[Op1Result] IS NOT NULL) AND ([Extent1].[Op1Result] IS NOT NULL) AND ([Extent1].[Op1Result] IS NOT NULL) AND ([Extent1].[Op1Result] IS NOT NULL) UNION ALL SELECT 1 AS [C1], N'op2' AS [C2], [Extent2].[Op2Result] AS [Op2Result], [Extent2].[OperationDate] AS [OperationDate] FROM [dbo].[PivotTable] AS [Extent2] WHERE ([Extent2].[Op2Result] IS NOT NULL) AND ([Extent2].[Op2Result] IS NOT NULL) AND ([Extent2].[Op2Result] IS NOT NULL) AND ([Extent2].[Op2Result] IS NOT NULL)) AS [UnionAll1] ) AS [Distinct1] UNION ALL SELECT 1 AS [C1], N'op3' AS [C2], [Extent3].[Op3Result] AS [Op3Result], [Extent3].[OperationDate] AS [OperationDate] FROM [dbo].[PivotTable] AS [Extent3] WHERE ([Extent3].[Op3Result] IS NOT NULL) AND ([Extent3].[Op3Result] IS NOT NULL) AND ([Extent3].[Op3Result] IS NOT NULL)) AS [UnionAll2] ) AS [Distinct2] UNION ALL SELECT 1 AS [C1], N'op4' AS [C2], [Extent4].[Op4Result] AS [Op4Result], [Extent4].[OperationDate] AS [OperationDate] FROM [dbo].[PivotTable] AS [Extent4] WHERE ([Extent4].[Op4Result] IS NOT NULL) AND ([Extent4].[Op4Result] IS NOT NULL)) AS [UnionAll3] ) AS [Distinct3] UNION ALL SELECT 1 AS [C1], N'op5' AS [C2], [Extent5].[Op5Result] AS [Op5Result], [Extent5].[OperationDate] AS [OperationDate] FROM [dbo].[PivotTable] AS [Extent5] WHERE [Extent5].[Op5Result] IS NOT NULL) AS [UnionAll4] ) AS [Distinct4] |
The second query has a rather bad execution plan. 5 table scans and 4 distinct sort operations are just for 5 columns to be unpivoted. I expect it will degrade when the number of columns would raise.
