如何将此查询转换为linq [英] How to convert this query to linq
本文介绍了如何将此查询转换为linq的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨请让我知道如何将此查询转换为linq,实际上我需要从sql server创建动态数据透视表我已完成此查询但我无法将此查询保存在我的sql server中与视图或函数相同或所以我想从我的实体模式中使用此查询的linq版本。
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 )
解决方案
您可以尝试使用 Linqer [ ^ ]。它是从SQL到Linq的转换工具。
http://www.sqltolinq。 com / [ ^ ]
< blockquote>我找到了一个非常简单的方法来执行此要求,因此我将动态查询保存到存储过程中:
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 )
< span class =code-keyword> END
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.
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)
解决方案
You can try to use Linqer[^]. It's a conversion tool from SQL to Linq.
http://www.sqltolinq.com/[^]
I find out a really simple way for doing this requirement, so I saved my Dynamic Query into store procedure:
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
这篇关于如何将此查询转换为linq的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文