Incompatible Data Reader Exception From EF Mapped

Question

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

Answer 1

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 2

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

Answer 3

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

Answer 4

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

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.