如何使用EntityFramework 7和Asp.Net 5调用SQL存储过程

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.

在过去的几天里,我正在寻找一些关于如何使用EntityFramework 7从Web API控制器方法中调用存储过程的教程。

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.

我所经历的所有教程都反过来展示了它,即Code First方法。但我已经有了一个数据库,我需要用它来构建一个Web API。各种业务逻辑已经编写为存储过程和视图,我必须使用来自Web API的那些逻辑。

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

问题1:这是否可以继续使用EF7的Database First方法并使用上面的数据库对象?

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

我通过以下NuGet命令将EntityFramework 6.1.3安装到我的包中:

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.

install-package EntityFramework,它将版本6.1.3添加到我的项目中,但立即开始显示错误消息(请参阅下面的屏幕截图)。我不清楚如何解决这个问题。

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

我有另一个测试项目,在project.json中我可以看到两个如下的条目:

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

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

但是,当我在Nu-Get包管理器中搜索时,我看不到这个版本!只有6.1.3即将到来。

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

1)我不想使用ADO.Net,而是想使用EntityFramework来使用ORM

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)?

2)如果EntityFramework 6.1.3能够从现有数据库调用存储过程和视图,我如何解决错误(截图)?

What is the best practice to achieve this?

实现这一目标的最佳做法是什么?

5 个解决方案

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

我希望我正确理解你的问题。您在数据库中有现有的STORED PROCEDURE,例如dbo.spGetSomeData,它返回包含某些字段的某些项的列表,您需要从Web API方法提供数据。

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

实施可以是以下几点。您可以定义一个空的DbContext,如:

public class MyDbContext : DbContext
{
}

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

并使用数据库的连接字符串定义appsettings.json

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

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

您应该使用Microsoft.Extensions.DependencyInjection将MyDbContext添加到

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:

现在,您可以按以下方式实施WebApi操作:

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

上面的代码只使用exec dbo.spGetSomeData执行,并使用dataRader读取所有结果并保存在动态对象中。如果您要从api / My进行$ .ajax调用,您将获得从dbo.spGetSomeData返回的数据,您可以直接在JavaScript代码中使用它。上面的代码非常透明。 dbo.spGetSomeData返回的数据集中的字段名称将是JavaScript代码中属性的名称。您无需以任何方式管理C#代码中的任何实体类。您的C#代码没有从存储过程返回的字段名称。因此,如果您要扩展/更改dbo.spGetSomeData的代码(重命名某些字段,添加新字段),则只需调整JavaScript代码,但不需要调整C#代码。

#2

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

DbContext有一个Database属性,它保存与数据库的连接,您可以随意执行以下操作:

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.

但是,我建议您不要在Web Api操作中执行此操作,而是向上下文或与上下文交互的任何服务/存储库添加方法。然后在您的操作中调用此方法。理想情况下,您希望将所有SQL内容保存在一个位置。

#3

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

正如上面的答案,你可以简单地使用FromSQL()而不是SqlQuery <>()。

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

#4

For Database first approach , you have to use Scaffold-DbContext command

对于Database first方法,您必须使用Scaffold-DbContext命令

Install Nuget packages Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.SqlServer.Design

安装Nuget包Microsoft.EntityFrameworkCore.Tools和Microsoft.EntityFrameworkCore.SqlServer.Design

Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

but that will not get your stored procedures. It is still in the works,tracking issue #245

但这不会得到你的存储过程。它仍处于工作中,跟踪问题#245

But, To execute the stored procedures, use FromSql method which executes RAW SQL queries

但是,要执行存储过程,请使用执行RAW SQL查询的FromSql方法

e.g.

var products= context.Products
    .FromSql("EXECUTE dbo.GetProducts")
    .ToList();

To use with parameters

用于参数

var productCategory= "Electronics";

var product = context.Products
    .FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)
    .ToList();

or

var productCategory= new SqlParameter("productCategory", "Electronics");

var product = context.Product
    .FromSql("EXECUTE dbo.GetProductByName  @productCategory", productCategory)
    .ToList();

There are certain limitations to execute RAW SQL queries or stored procedures.You can’t use it for INSERT/UPDATE/DELETE. if you want to execute INSERT, UPDATE, DELETE queries, use the ExecuteSqlCommand

执行RAW SQL查询或存储过程有一些限制。您不能将它用于INSERT / UPDATE / DELETE。如果要执行INSERT,UPDATE,DELETE查询,请使用ExecuteSqlCommand

var categoryName = "Electronics";
dataContext.Database
           .ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName);

#5

Using MySQL connector and Entity Framework core 2.0

使用MySQL连接器和Entity Framework核心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.

我的问题是我得到了像fx这样的例外。 Ex.Message =“'FromSql'操作的结果中没有所需的列'body'。”因此,为了以这种方式通过存储过程获取行,您必须返回与DBSet关联的实体类型的所有列,即使您不需要此特定调用的所有数据。

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

OR with parameters

或与参数

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