How can I call a SQL Stored Procedure using EntityFramework 7 and Asp.Net 5

Question

For last couple of days I am searching for some tutorials about how to call a Stored Procedure from inside a Web API controller method using EntityFramework 7.

All tutorials I came through are showing it the other way round, i.e. Code First approach. But I already have a database in place and I need to use it to build a Web API. Various business logic are already written as Stored Procedures and Views and I have to consume those from my Web API.

Question 1: Is this at all possible to carry on with Database First approach with EF7 and consume database objects like above?

I installed EntityFramework 6.1.3 to my package by the following NuGet command:

install-package EntityFramework which adds version 6.1.3 to my project but immediately starts showing me error message (please see the screenshot below). I have no clue about how to resolve this.

I have another test project where in project.json I can see two entries like following:

"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final", "EntityFramework.MicrosoftSqlServer.Design": "7.0.0-rc1-final",

However, when I am searching in Nu-Get package manager, I don;t see this version! Only 6.1.3 is coming up.

My main objective is to consume already written Stored Procedures and Views from an existing database.

1) I do not want to use ADO.Net, rather I would like to use ORM using EntityFramework

2) If EntityFramework 6.1.3 has the ability to call Stored Procs and Views from already existing database, how I can resolve the error (screenshot)?

What is the best practice to achieve this?

Answer 1

I hope that I correctly understand your problem. You have existing STORED PROCEDURE, for example dbo.spGetSomeData, in the database, which returns the list of some items with some fields and you need to provide the data from Web API method.

The implementation could be about the following. You can define an empty DbContext like:

public class MyDbContext : DbContext
{
}

and to define appsettings.json with the connection string to the database

{
  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=true"
    }
  }
}

You should use Microsoft.Extensions.DependencyInjection to add MyDbContext to the

public class Startup
{
    // property for holding configuration
    public IConfigurationRoot Configuration { get; set; }

    public Startup(IHostingEnvironment env)
    {
        // Set up configuration sources.
        var builder = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);
            .AddEnvironmentVariables();
        // save the configuration in Configuration property
        Configuration = builder.Build();
    }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        services.AddMvc()
            .AddJsonOptions(options => {
                options.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
            });

        services.AddEntityFramework()
            .AddSqlServer()
            .AddDbContext<MyDbContext>(options => {
                options.UseSqlServer(Configuration["ConnectionString"]);
            });
    }
    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
        ...
    }
}

Now you can implement your WebApi action as the following:

[Route("api/[controller]")]
public class MyController : Controller
{
    public MyDbContext _context { get; set; }

    public MyController([FromServices] MyDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async IEnumerable<object> Get()
    {
        var returnObject = new List<dynamic>();

        using (var cmd = _context.Database.GetDbConnection().CreateCommand()) {
            cmd.CommandText = "exec dbo.spGetSomeData";
            cmd.CommandType = CommandType.StoredProcedure;
            // set some parameters of the stored procedure
            cmd.Parameters.Add(new SqlParameter("@someParam",
                SqlDbType.TinyInt) { Value = 1 });

            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();

            var retObject = new List<dynamic>();
            using (var dataReader = await cmd.ExecuteReaderAsync())
            {
                while (await dataReader.ReadAsync())
                {
                    var dataRow = new ExpandoObject() as IDictionary<string, object>;
                    for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++) {
                        // one can modify the next line to
                        //   if (dataReader.IsDBNull(iFiled))
                        //       dataRow.Add(dataReader.GetName(iFiled), dataReader[iFiled]);
                        // if one want don't fill the property for NULL
                        // returned from the database
                        dataRow.Add(
                            dataReader.GetName(iFiled),
                            dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled] // use null instead of {}
                        );
                    }

                    retObject.Add((ExpandoObject)dataRow);
                }
            }
            return retObject;
        }
    }
}

The above code just execute using exec dbo.spGetSomeData and use dataRader to read all results and save there in dynamic object. If you would make $.ajax call from api/My you will get the data returned from dbo.spGetSomeData, which you can directly use in JavaScript code. The above code is very transparent. The names of the fields from the dataset returned by dbo.spGetSomeData will be the names of the properties in the JavaScript code. You don't need to manage any entity classes in your C# code in any way. Your C# code have no names of fields returned from the stored procedure. Thus if you would extend/change the code of dbo.spGetSomeData (rename some fields, add new fields) you will need to adjust only your JavaScript code, but no C# code.

Answer 2

DbContext has a Database property, which holds a connection to the database that you can do whatever you want with:

context.Database.SqlQuery<Foo>("exec [dbo].[GetFoo] @Bar = {0}", bar);

However, rather than doing this in your Web Api actions, I would suggest either adding a method to your context or to whatever service/repository that interacts with your context. Then just call this method in your action. Ideally, you want to keep all your SQL-stuff in one place.