Issue with mapping stored procedures using Entity Framework Code First Approach

Question

Hi, 

I'm calling a stored procedure in entity framework code first approach. Basically I have four columns for my table but using stored procedure I'm only returning two columns. As, im returning only two columns.

I'm getting following error:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The data reader is incompatible with the specified 'CodeFirstDepartment.Models.Department'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.

If I use the same stored procedure and return all columns its working fine. 

Is it something to do with mapping.

Following is  my code:

DepartmentContext db = new DepartmentContext();

SqlParameter param1 = new SqlParameter("@FilterExpression", "Sandeep");

var list = db.Department.SqlQuery("sp_test_Ef @FilterExpression", param1).ToList();

Any help would be appreciated. 

Thanks,

Sandeep

​

Hi,

I'm calling a stored procedure in entity framework code first approach. Basically I have four columns for my table but using stored procedure I'm only returning two columns. As, im returning only two columns.

I'm getting following error:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The data reader is incompatible with the specified 'CodeFirstDepartment.Models.Department'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.

If I use the same stored procedure and return all columns its working fine. 

Is it something to do with mapping.

Following is  my code:

DepartmentContext db = new DepartmentContext();

SqlParameter param1 = new SqlParameter("@FilterExpression", "Sandeep");

var list = db.Department.SqlQuery("sp_test_Ef @FilterExpression", param1).ToList();

Any help would be appreciated. 

Thanks,

Sandeep

​

Hi,

I'm calling a stored procedure in entity framework code first approach. Basically I have four columns for my table but using stored procedure I'm only returning two columns. As, im returning only two columns.

I'm getting following error:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The data reader is incompatible with the specified 'CodeFirstDepartment.Models.Department'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.

If I use the same stored procedure and return all columns its working fine. 

Is it something to do with mapping.

Following is  my code:

DepartmentContext db = new DepartmentContext();

SqlParameter param1 = new SqlParameter("@FilterExpression", "Sandeep");

var list = db.Department.SqlQuery("sp_test_Ef @FilterExpression", param1).ToList();

Any help would be appreciated. 

Thanks,

Sandeep

​

Hi,

I'm calling a stored procedure in entity framework code first approach. Basically I have four columns for my table but using stored procedure I'm only returning two columns. As, im returning only two columns.

I'm getting following error:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The data reader is incompatible with the specified 'CodeFirstDepartment.Models.Department'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.

If I use the same stored procedure and return all columns its working fine. 

Is it something to do with mapping.

Following is  my code:

DepartmentContext db = new DepartmentContext();

SqlParameter param1 = new SqlParameter("@FilterExpression", "Sandeep");

var list = db.Department.SqlQuery("sp_test_Ef @FilterExpression", param1).ToList();

Any help would be appreciated. 

Thanks,

Sandeep

---

Sandeep

Answer 1

https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

It's where you can post for help.

You have a mapping problem where the return results of the sproc and the column names retuned can't be mapped to all properties in the Department object. 

Answer 2

Hi,

I'm calling a stored procedure in entity framework code first approach. Basically I have four columns for my table but using stored procedure I'm only returning two columns. As, im returning only two columns.

I'm getting following error:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The data reader is incompatible with the specified 'CodeFirstDepartment.Models.Department'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.

If I use the same stored procedure and return all columns its working fine. 

Is it something to do with mapping.

Following is  my code:

DepartmentContext db = new DepartmentContext();
SqlParameter param1 = new SqlParameter("@FilterExpression", "Sandeep");
var list = db.Department.SqlQuery("sp_test_Ef @FilterExpression", param1).ToList();

Any help would be appreciated. 

Thanks,

Sandeep

---

Sandeep

Answer 3

Hi SandeepApsingekar,

>>I'm getting following error:
An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
Additional information: The data reader is incompatible with the specified 'CodeFirstDepartment.Models.Department'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.

Because when entity framework want to map to model object, but store procedure does not provide related field, I would suggest that you could create a custom model to retrieve the store procedure's records, like this:

 DepartmentContext db = new DepartmentContext();
            SqlParameter param1 = new SqlParameter("@FilterExpression", "A");
            var list = db.Database.SqlQuery<DeptDto>("sp_test_Ef @FilterExpression", param1).ToList();

#DeptDto

public class DeptDto
    {
        public string DcGuid { get; set; }

        [StringLength(50)]
        public string DeptName { get; set; }
    }

Best regards,

Zhanglong

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Thanks, for the suggestion. This actually works. 

---

Sandeep

Answer 5

I was wondering if this was the only way to call a stored procedure when we want only selected columns to be displayed. 

---

Sandeep

Answer 6

I was wondering if this was the only way to call a stored procedure when we want only selected columns to be displayed. 

---

Sandeep

I gave you the other way, which use the EF backdoor.

Answer 7

Oh yeah, That's correct. I felt that approach is bit complex for me. As my motive is to just render selected columns of a table.

Thanks, 

Sandeep 

---

Sandeep