Execute stored procedure with Entity Framework Core and OData

Stored procedures mapping in Entity Framework Core is still not supported out-of-the-box however with few tricks you can do this in your application.

Define the returned data type in your models and contextlink

NorthwindContext.cs

  public partial class NorthwindContext : DbContext
  {
    public NorthwindContext(DbContextOptions<NorthwindContext> options):base(options)
    {
    }

    public NorthwindContext()
    {
    }

    public DbSet<CustOrderHist> CustOrderHists
    {
      get;
      set;
    }

    ...
  }

CustOrderHists.cs

  [Table("CustOrderHist", Schema = "dbo")]
  public class CustOrderHist
  {
    [Key]
    public string ProductName
    {
      get;
      set;
    }
    public int? Total
    {
      get;
      set;
    }
  }

Table and Key attributes are necessary because of OData

Use FromSql() method to execute raw SQL query in your controllerlink

CustOrderHistsController.cs

  public partial class CustOrderHistsController : Controller
  {
    private NorthwindContext context;

    public CustOrderHistsController(NorthwindContext context)
    {
      this.context = context;
    }

    [HttpGet]
    [ODataRoute("CustOrderHistsFunc(CustomerID={CustomerID})")]
    public IActionResult CustOrderHistsFunc(string CustomerID)
    {
        return Ok(this.context.CustOrderHists.AsNoTracking().FromSql("EXEC [dbo].[CustOrderHist] {0}", CustomerID));
    }
  }

Use AsNoTracking() method to tell EF to not track entities. ODataRoute attribute is the OData URL path template. You cannot use FromSql() method for INSERT/UPDATE/DELETE - for such cases use the ExecuteSqlCommand().

Define your controller method as function using ODataConventionModelBuilderlink

Startup.cs

    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
      ...
      var provider = app.ApplicationServices.GetRequiredService<IAssemblyProvider>();

      app.UseMvc(builder =>
      {
        var northwindBuilder = new ODataConventionModelBuilder(provider);
        northwindBuilder.ContainerName = "NorthwindContext";

        var custOrderHists = northwindBuilder.Function("CustOrderHistsFunc");
        custOrderHists.Parameter<string>("CustomerID");
        custOrderHists.ReturnsCollectionFromEntitySet<CustOrderHist>("CustOrderHists");

        builder.MapODataRoute("odata/Northwind", northwindBuilder.GetEdmModel());
      }

      ...
    }

Run your app and check the resultlink

Of course using Radzen all steps are automated and you will get beautiful Angular app with OData and Microsoft SQL Server stored procedures support. Please visit this article for more info.

Enjoy!