Incompatible Data Reader Exception From EF Mapped

问题:

I am using entity frame work and have updated a table and its stored procedure but am 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.

ValuationId is my primary key witch i want to auto increment.

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.

I'm unfamiliar with entity frame work and just have the basics, and i think it may be a mapping issue from the 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.

 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);
        }

回答1:

The message means that the results of the stored procedure do not contain a column named ValudationId. Double check your select statement and run it in SSMS to ensure that you're bringing back that column.

EDIT: Your procedure does not contain a select statement. 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.

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.

回答2:

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.

回答3:

In my case it was returning data, but the column name, not provided, due to a CAST statement 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.

By doing an actual call in SSMS and viewing the result, that actual result showed this now obvious mistake:

Naming the column in SQL to what EF expected fixed the issue.

回答4:

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 SP and it will work. When you use a return statement, the SP will return an int and not your select query.

回答5:

If you are inserting/deleting/updating (these are considered by EF as 'non-query'), and can be called by our code using

context.Database.ExecuteSqlCommand(insert into Table (Col1,Col2) values (1,2));

But if are doing select query for a raw SQL statement, then use

context.DbSet<Table_name>.SqlQuery(select * from table_name).ToList();

or 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.

回答6:

Strange, I solved this by adding the GO command to the end of by stored procedure.

回答7:

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

回答8:

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 (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 Of to None. So if you don't expect any return result go to your Model and in Model Browser > Function Imports do right click on SP has issues and click Edit, Then check the Returns a Collection Of section and set it to None.

回答9:

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.

回答10:

May be you are using select without giving the alias name for column.

回答11:

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:-

SELECT
    CustomerId,
    FirstName,
    LastName,
    CustomerId
FROM
    Customers

回答12:

Also watch out for instances where your model's EDMX file remaps the database column name to something different from the corresponding class object.

In my case, the stored procedure was returning columns with spaces in the names [Monthly Bill Rate]. In order to handle that, the column name was remapped in the model.

At some point, the SP return column had been renamed to [MonthlyBillRate], but the model wasn't updated to correspond. When I searched the code for MonthlyBillRate, there it was in the Designer.cs file as MonthlyBillRate. I couldn't understand why I was getting the exception. Upon closer inspection, I found the remapped name in the EDMX.

Model.edmx

The remapping occurs in the ScalarProperty tag via the ColumnName attribute.

 <FunctionImportMapping FunctionImportName="BillingReport" FunctionName="Model.Store.BillingReport">
  <ResultMapping>
    <ComplexTypeMapping TypeName="Model.BillData">
      <ScalarProperty Name="MonthlyBillRate" ColumnName="Monthly Bill Rate" />
    </ComplexTypeMapping>
  </ResultMapping>

Model.Designer.cs

    [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
    [DataMemberAttribute()]
    public Nullable<global::System.Decimal> MonthlyBillRate
    {
      ... etc ...
    }