asp.net-mvc 来自 EF 映射对象的不兼容数据读取器异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14235783/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Incompatible Data Reader Exception From EF Mapped Objects
提问by Pomster
I am using Entity Framework and have updated a table and its stored procedure but I'm getting the following error when the stored procedure is called.
我正在使用实体框架并更新了一个表及其存储过程，但是在调用存储过程时出现以下错误。
The data reader is incompatible with the specified 'FormValueModel.Valuation'. A member of the type, 'ValuationId', does not have a corresponding column in the data reader with the same name.
数据读取器与指定的“FormValueModel.Valuation”不兼容。'ValuationId' 类型的成员在数据读取器中没有对应的同名列。
ValuationId is my primary key witch i want to auto increment.
ValuationId 是我想要自动递增的主键。
I can execute the stored procedure find from SQL management studio, And when i run my application it writes into the database but then the error message appears.
我可以从 SQL 管理工作室执行存储过程 find，当我运行我的应用程序时，它会写入数据库，但随后会出现错误消息。
I'm unfamiliar with Entity Framework and just have the basics, and i think it may be a mapping issue from the model.edmx.
我不熟悉实体框架，只有基础知识，我认为这可能是model.edmx 的映射问题。
What would be the correct procedure in recreating and mapping the tables and stored procedures in the model?
在模型中重新创建和映射表和存储过程的正确过程是什么？
Stored procedure.
存储过程。
ALTER PROCEDURE [dbo].[ValuationCreate]
@TrackingNumber varchar(100),
@FormMobiValuationId varchar(100),
@ValuationPropertyId int,
@ValuationFileName varchar(50)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @ErrorMessage varchar(1000)
BEGIN TRANSACTION
--Insert to Valuation
INSERT INTO [Valuation]
(
TrackingNumber,
FormMobiValuationId,
ValuationPropertyId, -- new
ValuationFileName,
Date,
ValuationStatus,
IsActive
)
VALUES
(
@TrackingNumber,
@FormMobiValuationId,
@ValuationPropertyId,--new
@ValuationFileName,
GETDATE(),
1, --Created
1
)
IF @@ERROR > 0
BEGIN
SET @ErrorMessage = 'Valuation Insert failed'
GOTO ErrorHandler
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN
END
ErrorHandler:
RAISERROR(@ErrorMessage,16,1);
ROLLBACK TRANSACTION
RETURN -1
C# call where error occurs, The error message appears on the last line.
发生错误的 C# 调用，错误消息出现在最后一行。
public ObjectResult<Valuation> ValuationCreate(global::System.String trackingNumber, global::System.String formMobiValuationId, Nullable<global::System.Int32> valuationPropertyId, global::System.String valuationFileName)
{
ObjectParameter trackingNumberParameter;
if (trackingNumber != null)
{
trackingNumberParameter = new ObjectParameter("TrackingNumber", trackingNumber);
}
else
{
trackingNumberParameter = new ObjectParameter("TrackingNumber", typeof(global::System.String));
}
ObjectParameter formMobiValuationIdParameter;
if (formMobiValuationId != null)
{
formMobiValuationIdParameter = new ObjectParameter("FormMobiValuationId", formMobiValuationId);
}
else
{
formMobiValuationIdParameter = new ObjectParameter("FormMobiValuationId", typeof(global::System.String));
}
ObjectParameter valuationPropertyIdParameter;
if (valuationPropertyId.HasValue)
{
valuationPropertyIdParameter = new ObjectParameter("ValuationPropertyId", valuationPropertyId);
}
else
{
valuationPropertyIdParameter = new ObjectParameter("ValuationPropertyId", typeof(global::System.Int32));
}
ObjectParameter valuationFileNameParameter;
if (valuationFileName != null)
{
valuationFileNameParameter = new ObjectParameter("ValuationFileName", valuationFileName);
}
else
{
valuationFileNameParameter = new ObjectParameter("ValuationFileName", typeof(global::System.String));
}
return base.ExecuteFunction<Valuation>("ValuationCreate", trackingNumberParameter, formMobiValuationIdParameter, valuationPropertyIdParameter, valuationFileNameParameter);
}
回答by Adam Robinson
The message means that the results of the stored procedure do not contain a column named ValudationId. Double check your selectstatement and run it in SSMS to ensure that you're bringing back that column.
该消息意味着存储过程的结果不包含名为 的列ValudationId。仔细检查您的select语句并在 SSMS 中运行它以确保您带回该列。
EDIT:Your procedure does not contain a selectstatement. You need to select the inserted identity value (using the scope_identity()function, for example) so that EF can map it back to the entity.
编辑：您的程序不包含select声明。您需要选择插入的标识值（scope_identity()例如使用函数），以便 EF 可以将其映射回实体。
For example,
例如，
insert into Table
(
Col1,
Col2
)
values
(
1,
2
)
select scope_identity() as IdentityColName
Also, as an aside, you don't need all that transaction business in your insert statement; you only have one statement (your insert) that's modifying data.
另外，顺便说一句，您不需要在插入语句中包含所有交易业务；您只有一个修改数据的语句（您的插入）。
回答by usefulBee
For those who still getting the same error, make sure that you are pointing/connected to the correct database. After spending hours, I found out that I was working on the active database and not the testing one. And of course the changes which I made to the stored procedure in the testing database, did not have equivalence in the active database.
对于那些仍然遇到相同错误的人，请确保您指向/连接到正确的数据库。花了几个小时后，我发现我正在处理活动数据库而不是测试数据库。当然，我对测试数据库中的存储过程所做的更改在活动数据库中并不等效。
回答by ΩmegaMan
In my case it was returning data, but the column name, not provided, due to a CASTstatement which didn't have a column name alias, caused it to become blank. The missing column name error ended up generating the reported mapping failure by EF.
在我的情况下，它正在返回数据，但由于没有列名别名的语句，列名未提供CAST，导致它变为空白。缺少列名错误最终生成了 EF 报告的映射失败。
By doing an actual call in SSMS and viewing the result, that actual result showed this nowobvious mistake:
通过这样做在SSMS的实际通话和查看结果，实际的结果表明这个现在明显的错误：
Naming the column in SQL to what EF expected fixed the issue.
将 SQL 中的列命名为 EF 预期修复了该问题。
回答by Brett Jones
Avinash, good call on ExecuteSQLCommand and 'non-query'. However, Pornster is referring to ExecuteFunction to call a SP and return results. To resolve this issue, remove the 'return' statement from your SPand it will work. When you use a return statement, the SP will return an int and not your select query.
Avinash，对 ExecuteSQLCommand 和“非查询”的良好调用。但是，Pornster 指的是 ExecuteFunction 来调用 SP 并返回结果。要解决此问题，请从您的 SP 中删除“return”语句，它会起作用。当您使用 return 语句时，SP 将返回一个 int 而不是您的选择查询。
回答by FredL
回答by Flexicoder
I fixed my version of this problem, by browsing the Model in VS, finding the Stored Proc under Function Imports and changing the return type of the function to None
我修复了这个问题的版本，通过在 VS 中浏览模型，在函数导入下找到存储过程并将函数的返回类型更改为无
回答by Avinash Goud N J
If you are inserting/deleting/updating (these are considered by EF as 'non-query'), and can be called by our code using
如果您正在插入/删除/更新（这些被 EF 视为“非查询”），并且可以由我们的代码使用
context.Database.ExecuteSqlCommand(insert into Table (Col1,Col2) values (1,2));
But if are doing select query for a raw SQL statement, then use
但是，如果正在为原始 SQL 语句执行选择查询，则使用
context.DbSet<Table_name>.SqlQuery(select * from table_name).ToList();
or context.Database.SqlQuery(select * from table_name).ToList();
或 context.Database.SqlQuery(select * from table_name).ToList();
The SqlQuery() function, in EF, for strange reasons, throw exception Insert/delete/update operation. (The exception thrown is "A member of the type, does not have a corresponding column in the data reader with the same name.") But it has actually performed operation if you open your Sql Management Studio and check for the entries.
EF中的SqlQuery()函数，由于奇怪的原因，抛出异常Insert/delete/update操作。（抛出的异常是“该类型的成员，在数据读取器中没有对应的同名列。”）但是如果您打开Sql Management Studio并检查条目，它实际上已经执行了操作。
回答by QMaster
One of the important scenario is returning different results by different conditions. For example when you use IF Command maybe return different result set from each branch. As EF set the returning collection when import SP, expect a [type one] collection but get [type 2] collection then raise an error does not have a corresponding column in the data reader. So any SP with returning result set must return same collection (as column name and count) from every part on it Like below:
重要的场景之一是根据不同的条件返回不同的结果。例如，当您使用 IF 命令时，可能会从每个分支返回不同的结果集。由于 EF 在导入 SP 时设置了返回集合，因此期望 [type one] 集合但获取 [type 2] 集合然后引发错误，数据读取器中没有相应的列。因此，任何具有返回结果集的 SP 都必须从其上的每个部分返回相同的集合（如列名和计数），如下所示：
IF (Condition 1)
BEGIN
SELECT [column1], [column2], [column3], ... FROM [...]
END
ELSE
BEGIN
SELECT [column1], [column2], [column3], ... FROM [...]
END
This is worked for me in same issue and hope be helpful.
这在同一问题上对我有用，希望有所帮助。
Update:
更新：
Another time I got this error message when I had wrong Return Collection Type in Function Imports. Really I didn't expect Typed or some Collection values and maybe just need an integer as Out Parameter but I forgot to set Returns a Collection Ofto None. So if you don't expect any return result go to your Model and in Model Browser> Function Importsdo right click on SP has issues and click Edit, Then check the Returns a Collection Ofsection and set it to None.
另一次我收到此错误消息时，我在Function Imports. 真的，我没想到 Typed 或一些 Collection 值，也许只需要一个整数作为 Out 参数，但我忘记设置Returns a Collection Of为None. 因此，如果您不希望任何返回结果转到您的模型，然后在Model Browser> 中Function Imports右键单击 SP 有问题并单击编辑，然后检查该Returns a Collection Of部分并将其设置为None.
回答by giagiu
In my case, as Brett Jones suggested, the solution was to remove the "RETURN" statement from my stored procedure, leaving simply only the "SELECT" part. I spend hours and hours trying all kinds of solutions I found online, but it was as simple as that.
就我而言，正如 Brett Jones 所建议的，解决方案是从我的存储过程中删除“RETURN”语句，只留下“SELECT”部分。我花了很多时间尝试在网上找到的各种解决方案，但就是这么简单。
回答by Wayne Feltham
This isn't applicable to this particular case, but I had a similar issue where the same error was thrown but the specified member type was a column in my select post-fixed with 1....this was due to me returning the same column twice in my select....example:-
这不适用于这种特殊情况，但我遇到了类似的问题，其中抛出了相同的错误，但指定的成员类型是我的选择中的一列，后固定为 1....这是由于我返回相同的在我的选择中列两次......例如：-
SELECT
CustomerId,
FirstName,
LastName,
CustomerId
FROM
Customers
