locked
Unable to fetch the data with SQL Server Stored Procedure With Entity Framework RRS feed

  • Question

  • User1691775361 posted

    I am writing a  simple query in Entity Framework to call a stored procedure in Northwind Sample database.

    Executing this stored procedure in Sql Server  as below:

    exec custordersdetail 10248

    This return Five Columns - ProductName,UnitPrice,Quantity,Discount,ExtendedPrice

    In my ASP.NET web application i am trying to call this stored procedure and bind the gridview with the results.

    protected void Page_Load(object sender, EventArgs e)
    {
    using (var context = new NorthwindEntities())
    {

    DataGridView1.DataSource = context.CustOrdersDetail(10248);
    DataGridView1.DataBind();
    }

    }

    While Executing this is going to following function in NorthwindContext.

    public virtual ObjectResult<CustOrdersDetail_Result> CustOrdersDetail(Nullable<int> orderID)
    {
    var orderIDParameter = orderID.HasValue ?
    new ObjectParameter("OrderID", orderID) :
    new ObjectParameter("OrderID", typeof(int));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<CustOrdersDetail_Result>("CustOrdersDetail", orderIDParameter);

    }

    This is giving error as:

    The type parameter 'CustOrdersDetail_Result' in ExecuteFunction is incompatible with the type 'NorthwindModel.CustOrdersDetail_Result' returned by the function.

    I have also checked the NorthwindMOdel.edmx file for the CustOrdersDetail_Result type. This is as below:

    <ComplexType Name="CustOrdersDetail_Result">
    <Property Type="String" Name="ProductName" Nullable="false" MaxLength="40" />
    <Property Type="Decimal" Name="UnitPrice" Nullable="false" Precision="19" />
    <Property Type="Int16" Name="Quantity" Nullable="false" />
    <Property Type="Int32" Name="Discount" Nullable="true" />
    <Property Type="Decimal" Name="ExtendedPrice" Nullable="true" Precision="19" />
    </ComplexType>


    Please help me in resolving the issue.

     

    Thursday, September 26, 2013 1:40 AM

Answers

  • User-933407369 posted

    hi gauravggn ,

    According your description, I understand you can not fetch the data with SQL Server Stored Procedure.

    I would suggest you try the next workaround:

    1. As always with manual creation of the POCO classes -- it was an inconsistency between the ComplexType definition in the EDMX file and your POCO class.

    Property name inconsistency and a property data inconsistency (class was expecting datatype but the stored procedure returned datatype different).You need to check ComplexType definition in the EDMX file and your POCO class.

     2.if it is not working for you, you can try to use ExecuteStoreQuery and SqlParameter instead.

    http://stackoverflow.com/questions/8332565/how-to-use-entity-framework-with-stored-procedures-and-pocos

    I hope it helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 27, 2013 5:42 AM

All replies

  • User1409124252 posted

    If you are using Code First, here is a link that might help.

    http://www.codeproject.com/Articles/179481/Code-First-Stored-Procedures

    Thursday, September 26, 2013 2:13 PM
  • User-933407369 posted

    hi gauravggn ,

    According your description, I understand you can not fetch the data with SQL Server Stored Procedure.

    I would suggest you try the next workaround:

    1. As always with manual creation of the POCO classes -- it was an inconsistency between the ComplexType definition in the EDMX file and your POCO class.

    Property name inconsistency and a property data inconsistency (class was expecting datatype but the stored procedure returned datatype different).You need to check ComplexType definition in the EDMX file and your POCO class.

     2.if it is not working for you, you can try to use ExecuteStoreQuery and SqlParameter instead.

    http://stackoverflow.com/questions/8332565/how-to-use-entity-framework-with-stored-procedures-and-pocos

    I hope it helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 27, 2013 5:42 AM