时间戳

首页 > 解决方案 > 动态 SQL Pivot 参数查询实体框架

c# - 动态 SQL Pivot 参数查询实体框架

问题描述

我有一个查询，当我在 SQL Server Management Studio 中使用它时效果很好。值得一提的是 inPIVOT [MedicalInformation.MedicalInformationForm.Name.firstName]和[MedicalInformation.MedicalInformationForm.Name.lastName]不是静态的，并且可以变化，也就是它们可以在运行时使用不同的列进行更改

WITH pivot_data AS
(
    SELECT 
        [p].RecordId, -- Grouping Column
        [Key], -- Spreading Column
        [Value] -- Aggregate Column
    FROM 
        [RecordDatas] AS [p]
    INNER JOIN 
        (SELECT [p0].*
         FROM [Records] AS [p0]
         WHERE [p0].[IsDeleted] = 0) AS [t] ON [p].[RecordId] = [t].[ID]
    WHERE
        [p].DatasetId = 1386
        AND [p].[IsDeleted] = 0
        AND ([t].[ProjectID] = 191)
        AND [Key] IN ('MedicalInformation.MedicalInformationForm.Name.firstName', 
                      'MedicalInformation.MedicalInformationForm.Name.lastName')
)
SELECT 
    RecordId, 
    [MedicalInformation.MedicalInformationForm.Name.firstName], 
    [MedicalInformation.MedicalInformationForm.Name.lastName]
FROM 
    pivot_data
PIVOT 
    (MAX([Value]) 
     FOR [Key] IN ([MedicalInformation.MedicalInformationForm.Name.firstName], 
                   [MedicalInformation.MedicalInformationForm.Name.lastName])) AS p

当我尝试从 C# 构建相同的查询动态时会出现问题：

var sql1 = @"
            WITH pivot_data AS
(
SELECT [p].RecordId, -- Grouping Column
[Key], -- Spreading Column
[Value] -- Aggregate Column
FROM [RecordDatas] AS [p]
INNER JOIN (
SELECT [p0].*
FROM [Records] AS [p0]
WHERE [p0].[IsDeleted] = 0
) AS [t] ON [p].[RecordId] = [t].[ID]
where [p].DatasetId = 1386
AND [p].[IsDeleted] = 0
AND ([t].[ProjectID] = 191)
AND  [Key] IN( {0} , {1}))
SELECT RecordId, {2},{3}
FROM pivot_data
PIVOT (max([Value]) FOR [Key] IN ({2},{3})) AS p;";

await repositoryMapper.Repository.Context.Database.ExecuteSqlCommandAsync(sql1, 
"MedicalInformation.MedicalInformationForm.Name.firstName", 
"MedicalInformation.MedicalInformationForm.Name.lastName", 
"[MedicalInformation.MedicalInformationForm.Name.firstName]", 
"[MedicalInformation.MedicalInformationForm.Name.lastName]");

我的问题来了 - 当使用ExecuteSqlCommandAsyncPIVOT 列 ([MedicalInformation.MedicalInformationForm.Name.firstName]和[MedicalInformation.MedicalInformationForm.Name.lastName]) 生成查询并添加参数时，语法有问题。我收到一个错误：

'@p2' 附近的语法不正确。

我试图获取 Entity Framework 正在执行的生成查询，它看起来像这样：

exec sp_executesql N'
            WITH pivot_data AS
(
SELECT [p].RecordId, -- Grouping Column
[Key], -- Spreading Column
[Value] -- Aggregate Column
FROM [RecordDatas] AS [p]
INNER JOIN (
SELECT [p0].*
FROM [Records] AS [p0]
WHERE [p0].[IsDeleted] = 0
) AS [t] ON [p].[RecordId] = [t].[ID]
where [p].DatasetId = 1386
AND [p].[IsDeleted] = 0
AND ([t].[ProjectID] = 191)
AND  [Key] IN( @p0 , @p1))
SELECT RecordId, @p2,@p3
FROM pivot_data
PIVOT (max([Value]) FOR [Key] IN (@p2,@p3)) AS p;
',N'@p0 nvarchar(4000),
@p1 nvarchar(4000),
@p2 nvarchar(4000),
@p3 nvarchar(4000)',
@p0=N'MedicalInformation.MedicalInformationForm.Name.firstName',
@p1=N'MedicalInfo rmation.MedicalInformationForm.Name.lastName',
@p2=N'[MedicalInformation.MedicalInformationForm.Name.firstName]',
@p3=N'[MedicalInformation.MedicalInformationForm.Name.lastName]'

请帮助我，我不确定这里有什么问题

标签： c#sql-serverentity-framework.net-coreentity-framework-core

解决方案

您不能在from 参数@p2中分配这些值。您需要在运行查询之前设置这些语句，如下所示：@p3SELECT RecordId, @p2,@p3

var sql1 = @"
            WITH pivot_data AS
(
SELECT [p].RecordId, -- Grouping Column
[Key], -- Spreading Column
[Value] -- Aggregate Column
FROM [RecordDatas] AS [p]
INNER JOIN (
SELECT [p0].*
FROM [Records] AS [p0]
WHERE [p0].[IsDeleted] = 0
) AS [t] ON [p].[RecordId] = [t].[ID]
where [p].DatasetId = 1386
AND [p].[IsDeleted] = 0
AND ([t].[ProjectID] = 191)
AND  [Key] IN( {0} , {1}))
SELECT RecordId, [MedicalInformation.MedicalInformationForm.Name.lastName],[MedicalInformation.MedicalInformationForm.Name.lastName]
FROM pivot_data
PIVOT (max([Value]) FOR [Key] IN ({2},{3})) AS p;";

await repositoryMapper.Repository.Context.Database.ExecuteSqlCommandAsync(sql1, 
"MedicalInformation.MedicalInformationForm.Name.firstName", 
"MedicalInformation.MedicalInformationForm.Name.lastName", 
"[MedicalInformation.MedicalInformationForm.Name.firstName]", 
"[MedicalInformation.MedicalInformationForm.Name.lastName]");

推荐阅读