Using Odata enabled ASP.NET Web-API to call a Stored Procedure

Question

All, Looking for guidance on exposing a Stored Procedure using OData.

I'm modifying an existing WEB-API app that already exposes OData. It currently exposes tables and views from SQL SERVER, and now I'm required to have it also expose Stored procedures. The current Stored Procedure will accept a bunch of parameters, and push changes to a handful of tables on the back-end database.

Here are the packages used by the project:

I've worked to follow this guide: Actions and Functions in OData v4 Using ASP.NET Web API 2.2, as I understand that naively supported Stored Procedures is not possible in OData.

I've modified my existing application, adding the model, and the wiring inside of WebApiConfig. On my first run I got the following error:

Goes without saying... There shouldn't be a primary key defined for a stored procedure.

Note that I'm trying to access "vw_tickets_all_tables", and still the error is thrown... inferring that the error breaks the entire application.

Here's the code I have thus far:

MODEL:

public class InsertUpdateTicketsPJMTickets : DbContext
{
    public InsertUpdateTicketsPJMTickets()
            : base("name=InsertUpdateTicketsPJMTickets")
    {
    }
    public DbSet InsertUpdateTicketsPJMTickets { get; set; }
}
public partial class InsertUpdateTicketsPJMTicketsBase
{
    string ticketType{set; get;}
    string category{set; get;}
    string title{set; get;}
    string details{set; get;}
    Nullable isHtml{set; get;}
    // bunches of fields left out for brevity
    string lastUpdateBy{set; get;}
    string createdBy{set; get;}
    string creationName{set; get;}
    string revisionName{set; get;}
}

WepApiConfig:

public static IEdmModel GetUOMSModel()
{
    ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
    builder.ContainerName = "UOMSContext";
    builder.EntityType()
        .Action("Execute");

    var function = builder.Function("GetParameters");

    function.Parameter("ticketType");
    function.Parameter("category");
    function.Parameter("title");
    function.Parameter("details");
    // bunches of fields left out for brevity
    function.Parameter("inputTicketId");
    function.Parameter("lastUpdateBy");
    function.Parameter("createdBy");
    function.Parameter("creationName");
    function.Parameter("revisionName");
    function.ReturnsCollectionFromEntitySet("InsertUpdateTicketsPJMTickets");
}

I'll add a CONTROLLER once the "Key" issue is resolved.

Thank you for reading.

Using Odata enabled ASP.NET Web-API to call a Stored Procedure

Answers (1)

Related Questions