sql - How to convert this query to linq

Question

Hi Please let me know how can I convert this query to linq, actually I need create dynamic pivot table from sql server I was done with this query but I can not save this query in my sql server same as view or function or etc. so I want to use linq veersion of this query from my Entity mode.

tnx

DECLARE @ColumnsTable TABLE ([ColumnName] VARCHAR(50));

INSERT INTO @ColumnsTable ([ColumnName])
SELECT DISTINCT '[' + CONVERT(nvarchar(50), Unit) + ']'
FROM   dbo.MeterValueView;

DECLARE @PivotColumns VARCHAR(MAX), @TotalColumn VARCHAR(MAX), @SQL VARCHAR(MAX);

SET @PivotColumns = (SELECT STUFF((SELECT DISTINCT ', ' + CONVERT(nvarchar(50), [ColumnName])
                                   FROM   @ColumnsTable
                                   FOR XML PATH('')), 1, 2, ''));
SET @TotalColumn = (SELECT STUFF((SELECT DISTINCT ' + ISNULL(' + CONVERT(nvarchar(50), [ColumnName]) + ', 0)'
                                  FROM   @ColumnsTable
                                  FOR XML PATH('')), 1, 3, ''));
SET @SQL = 'SELECT *, (' + @TotalColumn + ') AS [Total]
FROM   (SELECT [Serial],
               [Unit],
               [Value]
        FROM   [MeterValueView]) AS t
       PIVOT (MAX([Value])
             FOR [Unit] IN (' + @PivotColumns + ')) AS p;';

EXEC(@SQL)

Answer 1

I find out a simple way I can use store procedure replace linq and etc. for getiing this purpose:

CREATE PROC MeterValuesView 

AS
BEGIN

DECLARE @ColumnsTable TABLE ([ColumnName] VARCHAR(50));

INSERT INTO @ColumnsTable ([ColumnName])
SELECT DISTINCT '[' + CONVERT(nvarchar(50), Unit) + ']'
FROM   dbo.MeterValueView;

DECLARE @PivotColumns VARCHAR(MAX), @TotalColumn VARCHAR(MAX), @SQL VARCHAR(MAX);

SET @PivotColumns = (SELECT STUFF((SELECT DISTINCT ', ' + CONVERT(nvarchar(50), [ColumnName])
                                   FROM   @ColumnsTable
                                   FOR XML PATH('')), 1, 2, ''));
SET @TotalColumn = (SELECT STUFF((SELECT DISTINCT ' + ISNULL(' + CONVERT(nvarchar(50), [ColumnName]) + ', 0)'
                                  FROM   @ColumnsTable
                                  FOR XML PATH('')), 1, 3, ''));
SET @SQL = 'SELECT *, (' + @TotalColumn + ') AS [Total]
FROM   (SELECT [Serial],
               [Unit],
               [Value]
        FROM   [MeterValueView]) AS t
       PIVOT (MAX([Value])
             FOR [Unit] IN (' + @PivotColumns + ')) AS p;';

EXEC(@SQL)

END