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?

Solution 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.

Solution 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.

Solution 3

Just as the above answer, you could simply use the FromSQL() instead of SqlQuery<>().

context.Set().FromSql("[dbo].[GetFoo] @Bar = {0}", 45);

Solution 4

Using MySQL connector and Entity Framework core 2.0

My issue was that I was getting an exception like fx. Ex.Message = "The required column 'body' was not present in the results of a 'FromSql' operation.". So, in order to fetch rows via a stored procedure in this manner, you must return all columns for that entity type which the DBSet is associated with, even if you don't need all the data for this specific call.

var result = _context.DBSetName.FromSql($"call storedProcedureName()").ToList(); 

OR with parameters

var result = _context.DBSetName.FromSql($"call storedProcedureName({optionalParam1})").ToList(); 

View more solutions

Comments

[Subrata Sarkar]

I would like to do it in more EF-ish way, by dropping an SP on a EDM designer tool, marking its type (Scalar, Complex etc.) and finally calling it just like an object of EntityFramework. And it would be great if I can do all these using ASP.NET and EntityFramework 7. If 7 is not the solution I can get back to previous versions like 6.1.3, but this is giving me an error which I have no idea bout how to resolve. Also I don't see any Entity Data Model (EDM) template available to add to my project in VS 2015 Community edition!

[Subrata Sarkar]

Hi Oleg, thank you very much for your quick and detailed reply. Now I have some light! I will try this and get back to you with my experience.

[Oleg]

@NiladriSarkar: You are welcome! You can see that the code of the Get action very common and one can share the code just using different name of the stored procedure (dbo.spGetSomeData) and the list of parameters. You can use cmd.Parameters.AddRange and provide array of SqlParameter filled for the specific stored procedure. I think that you understand what I mean. I just want to make the code of Get action mostly small and simple to show the main idea writing C# code, which calls stored procedure and returns transparently the results which can be used in JavaScript code.

[Subrata Sarkar]

Great! You have made my day :) I am now able to call a very simple store procedure which lists a set of records using Inner Join. I should now be able to carry on with more complex parts. In case of any doubt, guess what.. I will give you a bit of more trouble.

[Subrata Sarkar]

Question: How can I call a View directly instead? Or I should get back to the old days i.e. by creating a Stored Procedure, calling the view inside it and finally consume this SP from my controller?

[Oleg]

@NiladriSarkar: SELECT statement, which you can use in cmd.CommandText is absolutely common. You can use cmd.CommandText = "SELECT * FROM vwMyViewFromDatabase". You need just remove cmd.CommandType = CommandType.StoredProcedure;. You can use SELECT with parameters and just set the value of the parameter exactly like in case of usage STORED PROCEDURE.

[Subrata Sarkar]

@ Opps! I have been a fool. :P

[Chris Pratt]

That's not the "EF-ish" way. EF dropped support for EDMX in EF7. The EDM designer is deprecated, and good riddance.

[Subrata Sarkar]

Yes, EF7 has dropped EDM. Do you have any idea whether this been taken out permanently or it's a temporary catch?

[Douglas Gaskell]

I just get The type arguments for DbContext.Set<TEntity>() cannot be inferred from the usage

[TGarrett]

Can you please provide the code that you used, also, what .NET framework are you working with?

[user1447718]

@Oleg what you have above is more about using ado.net to call sp. we can use below statement to call the sp in EFCore way Set<sp_return_type>().FromSql("[sp_name]").AsNoTracking();

[Oleg]

@user1447718: I'm not full understand what you mean. In my approach one can use any stored procedure with any parameter. The most goal is the usage of Entity Framework Core function without defining any entity in C# code. The full SELECT results with all fields, which returns the specified stored procedure, will be return as JSON data from HTTP Get. As the result one need to maintain only the code of STORED PROCEDURE and JavaScript file. The C# code returns new List<dynamic> and it's transparent to any changes of database model (adding new column, rename columns, ...).

[wtf512]

.net core 1.1.0. No context.Set().FromSql() method