从LINQ访问动态创建的存储过程

Question

我正在MS SQL存储过程中透视数据。数据透视的列是使用存储过程参数（例如："location1，location2，location3"）动态创建的，因此将生成的列数未知。输出应该看起来像（位置取自存储过程参数）：

订单时间|Location1|Location2|位置3

这有可能被用在LINQ to SQL中吗？当我把这个过程拖到dbml文件时，它显示这个过程返回int类型。

我在log_sales表中使用的列为：

• 位置（我正在旋转的各种位置）
• 费用（金额）
• 订单时间

存储过程：

CREATE PROCEDURE [dbo].[proc_StatsDay] @columns NVARCHAR(64) AS
DECLARE @SQL_PVT1 NVARCHAR(512), @SQL_PVT2 NVARCHAR(512), @SQL_FULL NVARCHAR(4000);
SET @SQL_PVT1 =  'SELECT OrderTime, ' + LEFT(@columns,LEN(@columns)-1) +'
FROM (SELECT ES.Location, CONVERT(varchar(10), ES.OrderTime, 120),ES.Charge
        FROM dbo.log_sales ES
        ) AS D (Location,OrderTime,Charge)
        PIVOT (SUM (D.Charge) FOR D.Location IN
            (';
SET @SQL_PVT2 = ') )AS PVT
ORDER BY OrderTime DESC';
SET @SQL_FULL = @SQL_PVT1 + LEFT(@columns,LEN(@columns)-1) + 
@SQL_PVT2;       
EXEC sp_executesql @SQL_FULL, N'@columns NVARCHAR(64)',@columns = @columns

在dbml designer.cs文件中，我的存储过程代码的一部分：

[Function(Name="dbo.proc_StatsDay")]
public int proc_EasyDay([Parameter(DbType="NVarChar(64)")] string columns)
{
    IExecuteResult result = this.ExecuteMethodCall(this,((MethodInfo)MethodInfo.GetCurrentMethod())), columns);
    return ((int)(result.ReturnValue));
}

Answer 1

假设确实存在严重的动态需求，您可以使用DataContext。执行查询

只需生成一个覆盖结果空间的类型（属性名称必须与查询中的列名匹配）：

public class DynamicResult
{
  public DateTime OrderDate {get;set;}
  public decimal? Location1 {get;set;}
  public decimal? Location2 {get;set;}
//..
  public decimal? Location100 {get;set;}
}

然后呼叫

IEnumerable<DynamicResult> result =
  myDataContext.ExecuteQuery<DynamicResult>(commandString, param1);

Answer 2

您可以创建linq对象，以便在返回数据集后进行访问。

但这真的有用吗。Linq可用于类型安全调用，而不是动态结果。您可能不知道该在编译时查找什么。

Answer 3

您可以运行选择语句

SELECT ES.Location, DateAdd(dd, DateDiff(dd, 0, ES.OrderTime), 0),ES.Charge
FROM dbo.log_sales ES

并将结果捕获到类似于这种的类型

public class LogSale
{
  public string Location {get;set;}
  public DateTime OrderDate {get;set;}
  public decimal Charge {get;set;}
}

然后在内存中"旋转"/组织

List<LogSale> source = LoadData();
var pivot = source
  .GroupBy(ls => ls.OrderDate)
  .OrderBy(g => g.Key)
  .Select(g => new {
     Date = g.Key,
     Details = g
       .GroupBy(ls => ls.Location)
       .Select(loc => new {
          Location = loc.Key,
          Amount = loc.Sum(ls => ls.Charge)
       })
     });

这里有第二个pivotry Linq，它将数据推送到XML中，而不是匿名类型中。

var pivot = source
  .GroupBy(ls => ls.OrderDate)
  .OrderBy(g => g.Key)
  .Select(g => new XElement("Date",
    new XAttribute("Value", g.key),
    g.GroupBy(ls => ls.Location)
     .Select(loc => new XElement("Detail",
       new XAttribute("Location", loc.Key),
       new XAttribute("Amount", loc.Sum(ls => ls.Charge))
     ))
  ));