.NET Core Entity Framework stored procedures

10,536

At the moment, the way to execute stored procedures that return data is to use the DbSet.FromSql method.

using (var context = new SampleContext())
{
    var data= context.MyEntity
        .FromSql("EXEC GetData")
        .ToList();
}

This has certain limitations:

  • It must be called on a DbSet
  • The returned data must map to all properties on the DbSet type
  • It does not support ad hoc objects.

Or you can fall back to plain ADO.NET:

using (var context = new SampleContext())
using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "GetData";
    command.CommandType = CommandType.StoredProcedure;
    context.Database.OpenConnection();
    using (var result = command.ExecuteReader())
    {
        // do something with result
    }
}

There are plans to introduce support for returning ad hoc types from SQL queries at some stage.

Share:
10,536

Related videos on Youtube

DKhanaf
Author by

DKhanaf

I'm a software engineer with years of experience in web, real time, and high performance cloud computing industries. I have worked on PCI compliant payments gateways for industry leaders such as Verifi, scaled out backend infrastructure for highly concurrent MVNO with over 3 million active users, designed, architected and implemented from the ground up a sophisticated business rules engine using Akka Actor model and Scala, capable of making artificially intelligent business decisions. Currently I am involved in leading initiatives to globalize a micro services backend core infrastructure for OpenTable to be able to support seamless restaurant search and booking user experience across the globe for over 100 million active users, via mobile platforms such as iOS, Android, Mobile Web and traditional web applications. Additionally, I'm working on building a data pipeline for applying machine learning algorithms across a suite of services for optimized realtime search and availability results. Colleagues know me as a highly creative engineer who can always be trusted to come up with a new, optimized approach to solving problems. I spend a lot of time understanding the business and the audience and apply heavily Business Drive Design principles to new application architecture. I am a true team player, love to collaborate with others as I believe a good engineering team is a priceless tool in any business. I have a Masters of Science degree in Computer Science with an emphasis on cloud computing and Reactive software application design. I was developing my thesis on Reactive software before it was cool. Prior to my MS degree, I received my Bachelors of Science degree in Computer Science and focused heavily on mathematics, NP completeness, automata, non-deterministic Turing machines etc... I’m always interested in hearing from former colleagues, managers, or just interesting creative folk, so feel free to contact me if you’d like to connect

Updated on September 14, 2022

Comments

  • DKhanaf
    DKhanaf over 1 year

    I'm trying to port a ASP.NET 4.5 application to .NET Core and I have one real issue I can't seem to figure out.

    My existing application executes stored procs which return a dataset with multiple datatables. The Entity Framework can automatically map the returned fields to my entity properties but only works with the first datatable in the dataset (naturally).

    So I'm just trying to figure out if its at all possible to somehow intercept the model building process and use custom code to process the dataset and look into the other datatables to set entity fields.

    I know I can use the normal way of executing the stored procedure directly using the SqlConnection but I'm wondering if the Entity Framework has some way to do this already.

  • David Buck
    David Buck over 3 years
    Please read the editing help and learn how to format your answers and questions correctly. Also, please don't paste the same answer on multiple questions. If two questions can be answered with the same answer, you should answer one and flag the other as a duplicate.
  • Nilay Vishwakarma
    Nilay Vishwakarma almost 3 years
    context.Database.GetDbConnection().CreateCommand() return DbCommand object which is not compatible with SqlDataAdapter