Incompatible Data Reader Exception From EF Mapped Objects

Question

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.

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

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

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

Answer 3

In my case it was returning data, but the column name, was 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.

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

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

Answer 6

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