c# - 将存储过程 PIVOT 表转换为 LINQ 查询

Question

我的想法是转换我之前定义的当前存储过程。目的是，我无法使用存储过程从数据库返回数据。这样做的原因是在查询中留下。我需要将现有表转换为数据透视表，然后我必须通过 ASP.NET WebAPI 返回数据。此数据透视表是动态的，这意味着当用户添加新文章时，它将作为列添加到数据透视表中。

普通表如下所示:

datum      | rate     | article
--------------------------------
2013-01-03 |  97,766..|  DE011
2013-01-05 |  90.214..|  DE090
2013-01-10 |  97,890..|  DE011
2013-01-13 |  65,023..|  DE220
2013-01-13 |  97,012..|  DE300
2013-01-15 |  97,344..|  DE300
....

数据透视表应该如下所示:

rate | DE011 | ... | DE090 | ... | DE220 | ... | DE300
-------------------------------------------------------
100  |   0   | ... |   1   | ... |   0   | ... |   0
 98  |   2   | ... |   0   | ... |   1   | ... |   0
 97  |   0   | ... |   0   | ... |   0   | ... |   2
 90  |   0   | ... |   1   | ... |   0   | ... |   4
...

datum 列对于数据透视表很重要，因为用户必须在角度 View 中进行一些输入。在此示例中，用户选择 dateFrom 和 dateTo 输入。费率将四舍五入您在数据透视列 rate 中看到的数字。文章描述位于新表格列标题中，费率将计入每篇文章。

我的存储过程在 SQL Server 中运行良好。但在将 SP 导入 EDM 模型后， Entity Framework 定义了一个返回类型 INT，这对我来说是不可能的。

这是 EF 的代码:

 public virtual int getMonthIsin(Nullable<System.DateTime> fromDate, Nullable<System.DateTime> toDate)
        {
            var fromDateParameter = fromDate.HasValue ?
                new ObjectParameter("fromDate", fromDate) :
                new ObjectParameter("fromDate", typeof(System.DateTime));

            var toDateParameter = toDate.HasValue ?
                new ObjectParameter("toDate", toDate) :
                new ObjectParameter("toDate", typeof(System.DateTime));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("getMonthIsin", fromDateParameter, toDateParameter);
        }

我还在我的 WebAPI Controller 中尝试了 .SqlQuery()，如下所示:

return db.Database.SqlQuery<IQueryable>("EXEC getMonthIsin @fromDate, @toDate", fromDate, toDate).AsQueryable();

但它不起作用。

好吧，现在的想法是尝试 LINQ 查询并获取返回值。我不知道要实现这个:(

目前我已经尝试了大约这个 LINQ 查询:

public IQueryable getDatas(DateTime fromDate, DateTime toDate)
{
   var query = from t in db.table1
               where t.datum >= fromDate && t.datum <= toDate
               group t by t.article
                 into grp
                 select new
                 {
                    articles = grp.Key,
                    rate = grp.Select(g => g.rate),
                    total = grp.Select(g => g.rate).AsQueryable()
                 };

   return query;
}

但这并不是真正正确的返回。如果有人能帮助我，那将非常有帮助!!我会给每个好的答案投赞成票!

Answer 1

Entity Framework 不适合获取动态数据结构。 Dapper是这里使用的工具。它基本上是关于 IDbConnection 的扩展方法的集合。 , 其中之一是 Query返回 IEnumerable<dynamic> , 其中dynamic是一个实现 IDictionary<string, object> 的对象.获取数据非常简单:

IEnumerable<IDictionary<string, object>> result;

using (var cnn = new SqlConnection(connectionString))
{
    cnn.Open();

    var p = new DynamicParameters();
    p.Add(" @fromDate", fromDate, DbType.DateTime);
    p.Add(" @toDate", toDate, DbType.DateTime);

    result = (IEnumerable<IDictionary<string, object>>)
                cnn.Query(sql: "getMonthIsin", 
                          param: p, 
                          commandType: CommandType.StoredProcedure);
}

现在你有一个 IEnumerable<IDictionary<string, object>>其中一项 ( IDictionary<string, object> ) 表示存储过程结果集中的一行键/值对:

Key    Value
-----  ----
rate   100
DE011  0
...    ...
DE090  1
...    ...
DE220  0
...    ...
DE300  0

如何从这里开始取决于你。例如，您可以将结果转换为 DataTable如此处所示:Dictionary<string, object> to DataTable .

顺便说一句，Dapper 不仅简单，而且非常快。