Radzen Professional is now 20% off until January 4th 2019! Learn more

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 context

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 controller

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 ODataConventionModelBuilder

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 result

stored-procedure-odata.png

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!

by Vladimir Enchev

Radzenā€Š1.11 update - Active Directory, Migrating from Microsoft LightSwitch and more!

Latest update of Radzen (1.11) is here!
Read more