实体框架6从存储过程获取复杂返回值 [英] Entity Framework 6 get complext return value from a stored procedure

问题描述

我的ASP.Net MVC应用程序需要执行一个存储过程，以便根据一组参数检查重复的工单。存储过程既大又复杂，因此将其转换为Linq to SQL查询是不可能的。另外，我们不为此使用实体数据模型（EDMX）文件；我们只有领域模型，视图模型和数据上下文。存储过程具有以下签名：

My ASP.Net MVC app needs to execute a stored procedure in order to check for duplicate work orders based upon a set of parameters. The stored procedure is large and complex, so converting it to a Linq to SQL query is out of the question. Also, we do not use Entity Data Model (EDMX) files for this; we only have the domain models, view models and a data context. The stored procedure has the following signature:

CREATE PROCEDURE [dbo].[spGetDupWOs]
    -- Add the parameters for the stored procedure here
    @ProjectName VARCHAR(200) = '""',
    @City VARCHAR(100) = '',
    @State VARCHAR(100) = '',
    @ProjectNumber VARCHAR(50) = '',
    @County VARCHAR(50) = '',
    @Customer VARCHAR(400) = '',
    @QuoteRecipients VARCHAR(400) = NULL,
    @ProjectID BIGINT = NULL
    ...

    SELECT DuplicateID, ProjectNameMatchRank, ProjectNameMatch, ProjectName ...

我试图在控制器中调用它，例如：

I have tried to call it in my controller like:

IEnumerable<DuplicateProjects> duplicateCheckResults = db.Database.SqlQuery< DuplicateProjects>("spGetDupWOs", new { ProjectName = ProjectName, City = City, State = propal.State, ProjectNumber = ProjectNumber, County = County, Owner = Owner, BID_QuoteRecipients = QuoteRecipients, ProjectID = -1 });

在运行如下命令时出现异常：

And get an exception when I run this like:

对象类型
<> f__AnonymousType6`8 [[System.String，mscorlib，Version = 4.0.0.0，
Culture = neutral，PublicKeyToken = b77a5c561934e089]，[System.String，
mscorlib，版本= 4.0.0.0，Culture = neutral，
PublicKeyToken = b77a5c561934e089]，[System.String，mscorlib，
Version = 4.0.0.0 ，Culture = neutral，
PublicKeyToken = b77a5c561934e089]，[System.String，mscorlib，
版本= 4.0.0.0，Culture = neutral，
PublicKeyToken = b77a5c561934e089]，[System.String，mscorlib ，
Version = 4.0.0.0，Culture = neutral，
PublicKeyToken = b77a5c561934e089]，[System.String，mscorlib，
Version = 4.0.0.0，Culture = neutral，
PublicKeyToken = b77a5c561934e089]，[System.String，mscorlib，
版本= 4.0.0.0，文化=中性，
PublicKeyToken = b77a5c561934e089]，[System.Int32，mscorlib，
版本= 4.0.0.0 ，文化=中性，PublicKeyT oken = b77a5c561934e089]]转换为
a已知托管提供程序的本机类型。

No mapping exists from object type <>f__AnonymousType6`8[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type.

我在做什么错了？

更新：当我将所有传递的参数更改为ObjectParamter时，我仍然收到相同的错误消息。

UPDATE: When I change all of the passed parameters to ObjectParamter I still get the same error message.

更新2：

我稍微重构了代码。这是现在的样子：

I refactored the code a bit. Here is what it looks like now:

            List<SqlParameter> spl = new List<SqlParameter>();

            var ProjectNameParameter = new SqlParameter("ProjectName", project.ProjectName);
            spl.Add(ProjectNameParameter);
            var CityParameter = new SqlParameter("City", project.City);
            spl.Add(CityParameter);
            var StateParameter = new SqlParameter("State", project.State);
            spl.Add(StateParameter);
            var ProposalNumberParameter = new SqlParameter("ProjectNumber", project.ProjectNumber);
            spl.Add(ProposalNumberParameter);
            var CountyParameter = new SqlParameter("County", project.County);
            spl.Add(CountyParameter);
            var OwnerParameter = new SqlParameter("Owner", project.Owner);
            spl.Add(OwnerParameter);
            var BidRecipientParameter = QuoteRecipients != null ?
                new SqlParameter("QuoteRecipients", QuoteRecipients) :
                new SqlParameter("QuoteRecipients", "");
            spl.Add(BidRecipientParameter);
            var ProjectIDParameter = new SqlParameter("ProjectID", typeof(long));
            spl.Add(ProposalIDParameter);

            IEnumerable<DuplicateProposals> duplicateCheckResults = db.Database.SqlQuery<DuplicateProposals>("spGetDupWOs ProjectName = {0} City = {1}, State = {2}, ProjectNumber = {3}, County = {4}, Owner = {5}, QuoteRecipients = {6}, ProjectID = {7}",
                spl.ToArray());

现在我得到了错误：

SqlParameter已被另一个SqlParameterCollection包含。

The SqlParameter is already contained by another SqlParameterCollection.

推荐答案

在查询字符串构成后， SqlQuery< T> 的其他参数一个 params 数组，该方法将使用编号参数参数回填查询字符串，就像 String.Format 一样。由于您传递的是匿名对象，因此该方法试图将其视为一个参数，并且无法将其转换为字符串。因此，是的，您需要使用 SqlParameter 手动添加参数，或者更改您的调用以与该方法兼容的方式来处理它：

Further arguments to SqlQuery<T> after the query string constitute a params array, which the method will use to backfill the query string with numbered parameter arguments, much as String.Format does. Since you're passing an anonymous object, the method is trying to treat that as one single parameter and can't convert it into a string. So, yes, you either need to use SqlParameter to manually add the params or alter your call to handle it in a way compatible with this method:

db.Database.SqlQuery< DuplicateProjects>(
    "spGetDupWOs @ProjectName = {0}, @City = {1}, @State = {2}, @ProjectNumber = {3}, @Country = {4}, @Owner = {5}, @BID_QuoteRecipients = {6}, @ProjectID = {7}",
    ProjectName,
    City,
    propal.State,
    ProjectNumber,
    County,
    Owner,
    QuoteRecipients,
    -1
);

添加了空格间隔以提高可读性

这篇关于实体框架6从存储过程获取复杂返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！