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

Adam Robinson · Answer 1 · 2013-01-09T13:42:40.533

16

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.

edited Jan 09 '13 at 13:42

answered Jan 09 '13 at 13:02

Adam Robinson

182,639
35
285
343

ValudationId is the primary key and is on auto increment, its a insert statement. – Pomster Jan 09 '13 at 13:04
I get return value 0 when i execute the stored procedure in SSMS but it inserts into my database but it is an insert procedure. – Pomster Jan 09 '13 at 13:06
You have to pass the information back to EF so that it can update your entity with the key that was created when the row was inserted. At a minimum, you have to select the newly-inserted primary key. – Adam Robinson Jan 09 '13 at 13:12
Could you explain more on how to do this and what you mean? – Pomster Jan 09 '13 at 13:15
The primary key was not the column i added, but has become the problem now, the stored procedure worked before i change the table and updated the stored procedure, i added the column to the table and the stored procedure i think my problem is mapping in the entity. framework model – Pomster Jan 09 '13 at 13:17
Just to be clear, your stored procedure includes both an `insert` and a `select` statement, correct? If so, you can go into the EF designer, select your table, go to the stored procedure mapping, and assign the column you're returning in the select statement to the primary key. – Adam Robinson Jan 09 '13 at 13:20
Here is my stored procedure, When i edited the table and stored procedure, i went into my entity frame work model, deleted the table and stored procedure and right clicked and said update from database. – Pomster Jan 09 '13 at 13:27
I have added the Scope_identity and i get the same error message but it column that it is complaining about is the next one, the @Trackingnumber – Pomster Jan 10 '13 at 07:44
@Pommy: Then add whatever columns it's asking for to your select statement. – Adam Robinson Jan 10 '13 at 14:00
@AdamRobinson can you help on this? http://stackoverflow.com/questions/24291588/incompatible-data-reader-type-while-using-entity-framework-for-multiple-tables – Volatil3 Jun 18 '14 at 17:48

score 12 · Answer 2 · answered Sep 27 '13 at 15:29

12

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.

answered Sep 27 '13 at 15:29

usefulBee

9,250
10
51
89

1

This is especially true when working in a team and although the solution normally points at (localdb), someone checks in a change to point at the dev box. – John Warlow Dec 03 '15 at 14:38

ΩmegaMan · Answer 3 · 2020-11-22T22:06:59.010

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.

score 4 · Answer 4 · answered May 19 '14 at 04:34

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.

score 2 · Answer 5 · answered Oct 11 '13 at 07:10

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.

Why do you have to use Select * ? It won't work if you begin specifying certain columns like so: Select name, email . . . I'm getting an exception thrown regarding the primary key as described in the original post by Pomster. — Doug, Jan 05 '16 at 16:51

score 2 · Answer 6 · answered Jan 15 '15 at 16:34

2

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

answered Jan 15 '15 at 16:34

FredL

1,035
9
23

score 2 · Answer 7 · answered Feb 27 '15 at 17:44

2

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

answered Feb 27 '15 at 17:44

Flexicoder

8,251
4
42
56

QMaster · Answer 8 · 2015-01-05T07:28:31.010

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.

Again I faced with this issue and found additional SELECT for testing inside query, removed it and now every things OK. — QMaster, Oct 14 '16 at 18:19

score 1 · Answer 9 · answered Dec 09 '15 at 11:06

1

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.

answered Dec 09 '15 at 11:06

giagiu

19
1

Hi welcome to Stack Overflow. Glad it was sorted. You can add these comments to your original answer by editing it. See http://meta.stackexchange.com/a/21789/289042 – micstr Dec 09 '15 at 11:13

Walter Stabosz · Answer 10 · 2017-07-28T14:13:50.703

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

score 0 · Answer 11 · answered Aug 13 '15 at 11:35

0

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

answered Aug 13 '15 at 11:35

Deependra Kushwah

71
2
10

score 0 · Answer 12 · answered Aug 10 '16 at 11:15

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

score 0 · Answer 13 · answered Nov 20 '19 at 01:15

0

BEWARE the "select *" at the end of your stored procedures! Only ruin lies down that dark path.

answered Nov 20 '19 at 01:15

SteveCav

6,649
1
50
52

Incompatible Data Reader Exception From EF Mapped Objects

13 Answers13

Model.edmx

Model.Designer.cs

Linked