使用Entity Framework计算列使用存储过程获取多个结果集 [英] Get multiple result set with stored procedure using Entity Framework calculated column

问题描述

我想使用实体框架从存储过程中获取多个结果集。表的结果获得成功，但是当我想获取 Balance 列时，它不能，将不胜感激。

I want to get multiple result sets from a stored procedure using Entity Framework. Result of table get successfully, but when I want to get a Balance column, it could not, any help will be appreciated.

谢谢

public ViewModel GetTwoResultSetsForUserId(string Date, string FromDate, string ToDate, int userId)
{
    using (var db = new CuumiEntities())
    {
        // Create a SQL command and add parameter
        var cmd = db.Database.Connection.CreateCommand();
        cmd.CommandText = "getTransactionDatewisetesting";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@Date", Date));
        cmd.Parameters.Add(new SqlParameter("@FromDate", FromDate));
        cmd.Parameters.Add(new SqlParameter("@ToDate", ToDate));
        cmd.Parameters.Add(new SqlParameter("@UserId", userId));

        // execute your command
        db.Database.Connection.Open();
        var reader = cmd.ExecuteReader();
        double Balance = reader.GetDouble(0);

        var transactions = ((IObjectContextAdapter)db)
                                .ObjectContext
                                .Translate<transaction>(reader)
                                .ToList();

        reader.NextResult();

        var Expenses = ((IObjectContextAdapter)db)
                        .ObjectContext
                        .Translate<spendingClass>(reader, "spendings", MergeOption.AppendOnly).ToList();

        var balance = ((IObjectContextAdapter)db).ObjectContext.Translate<PrevBalance>(reader);

        return new ViewModel
               {
                   transactions = transactions,
                   Expenses = Expenses,
                   Balance = Balance
                };
    }
}

这是我要退货的模型

，这是我的存储过程结果：

and here is my stored procedure result:

推荐答案

SQL Server和.NET Framework基于不同的类型系统，这就是为什么在.NET端读取数据时，您需要仔细使用数据映射的原因。

SQL Server and the .NET Framework are based on different type systems, that is why while reading data on .NET side you need to use data mapping carefully.

在这里您可以找到SQL Server和.NET Framework之间的类型映射或类型等效列表 https://docs.microsoft.com/zh-cn/dotnet/framework/data/adonet/sql -server-data-type-mappings

Here you can find list of type mapping or type equivalents between SQL Server and .NET Framework https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings

在您的问题中， Balance的类型不清楚列或您的存储过程来自什么类型。在您的代码中，您尝试将 Balance 列读为 Double ，这就是您获得<$ c $的地方c>指定的转换在SQL Server中无效。根据Microsoft的文档（上面的共享链接），声明列类型在SQL Server端必须为 Float ，以便在.NET端您可以将其读取为 Double 类型。

In your question, it wasn't clear what was the type of Balance column on SQL side or what type was coming from your stored procedure. In your code, you are trying to read Balance column as Double, that is where you are getting Specified cast is not valid in sql server. According to Microsoft's document(link shared above), it is stated that column type must be Float on SQL Server side so that on .NET side you can read it as Double type.

当您遇到类型转换错误时，我想您返回的是非 Float 来自数据库的值。作为解决方案，您可以更新存储过程，将 Balance 转换为 Float 类型。

As you are getting type cast error, I guess you are returning non Float value from database. As a solution, you can update your stored procedure to cast your Balance to Float type.

示例用法：选择投射（余额为浮动）

最后，只是作为建议，使用使用 Connection 和 Reader 对象>语句，以便在执行结束时对它们进行处理，而不是保留打开的连接，这最终可能导致所有合并的连接都在使用中。

Finally, just as a suggestion, wrap your Connection and Reader objects with using statement so that at the end of your execution, they are disposed, not to leave open connections, which eventually can result in all pooled connections are in use exception.

这篇关于使用Entity Framework计算列使用存储过程获取多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！