none
Issue with mapping stored procedures using Entity Framework Code First Approach RRS feed

  • 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

    • Moved by CoolDadTx Thursday, July 12, 2018 2:35 PM EF related
    Thursday, July 12, 2018 3:24 AM

Answers

  • 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 MSDNFSF@microsoft.com.

    Friday, July 13, 2018 3:08 AM
    Moderator

All replies

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

    Thursday, July 12, 2018 9:39 AM
  • 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

    Thursday, July 12, 2018 1:54 PM
  • 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 MSDNFSF@microsoft.com.

    Friday, July 13, 2018 3:08 AM
    Moderator
  • Thanks, for the suggestion. This actually works. 

    Sandeep

    Friday, July 13, 2018 2:21 PM
  • I was wondering if this was the only way to call a stored procedure when we want only selected columns to be displayed. 


    Sandeep

    Friday, July 13, 2018 2:25 PM
  • 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.
    Friday, July 13, 2018 8:09 PM
  • 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

    Friday, July 13, 2018 8:18 PM