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

Question

最近几天，我一直在寻找一些关于如何使用 EntityFramework 7 在 Web API 控制器方法内调用存储过程的教程。

我看到的所有教程都是展示另一种方式，即采用 Code First 方法。但我已经有了一个数据库，并且需要使用它来构建一个 Web API。各种业务逻辑已经编写为存储过程和视图，我必须从 Web API 中使用这些内容。

问题1：是否可能使用 EF7 的 Database First 方法并像上述那样消费数据库对象？

我通过以下的 NuGet 命令将 EntityFramework 6.1.3 安装到我的项目中：

install-package EntityFramework 这将把版本6.1.3添加到我的项目中，但会立即显示错误消息（请参见下面的截图），我不知道该如何解决这个问题。

我还有另一个测试项目，在其中的 project.json 中，我可以看到类似以下的两个条目：

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

但是，在搜索 Nu-Get 包管理器时，我没有看到这个版本！ 只有6.1.3可用。

我的主要目标是从现有的数据库中消费已经编写好的存储过程和视图。

1）我不想使用 ADO.Net，而是想使用使用 EntityFramework 的 ORM。

2）如果 EntityFramework 6.1.3 可以调用来自已存在的数据库的 Stored Procs 和 Views，那么我该如何解决错误（截图）？

实现这个的最佳实践是什么？

- Subrata Sarkar

---

你目前如何使用Entity Framework？能否提供一个小例子来说明你目前的使用情况？我个人也使用存储过程，主要在控制器操作中调用并从Web Api返回结果。这样可以避免为表定义任何类（实体）。我的JavaScript代码依赖于从存储过程返回的列名，而不是我的C#代码。请参见答案。因此有许多实现选项。如果您能发布一个当前代码的示例，那么更好地展示如何帮助您。 - Oleg

你可能无法在NuGet中看到Entity Framework的7版本，因为你勾选了“仅限稳定版”复选框。这将排除预发布版本，“EF7 rc1”中的“rc”代表“发布候选版”（即预先发布）。 - Gary McGill

@Oleg：我以前从未使用过EntityFramework的存储过程。但是这里数据库已经创建好了，所以我必须使用相同的。我的任务是为正在进行的移动版本开发Web API。如果您能给我提供一个示例代码，那将非常有帮助。 - Subrata Sarkar

@Gary - 我勾选了“包括预发行版”复选框，但仍未获得EntityFramework的“rc”版本。 - Subrata Sarkar

Answer 1

希望我正确理解了您的问题。您在数据库中有一个现有的存储过程，例如dbo.spGetSomeData，它返回一些项目的列表以及一些字段，并且您需要提供来自Web API方法的数据。

实现可能涉及以下内容。您可以定义一个空DbContext，如下：

public class MyDbContext : DbContext
{
}

定义 appsettings.json 中的连接字符串以连接到数据库。

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

您应该使用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)
    {
        ...
    }
}

现在，您可以将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;
        }
    }
}

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

- Oleg

---

嗨，Oleg，非常感谢您快速而详细的回复。现在我有了一些启示！我会尝试这个并与您分享我的经验。 - Subrata Sarkar

问题：我如何直接调用一个“视图”？或者我应该回到旧日子，即创建一个“存储过程”，在其中调用“视图”，最后从我的控制器中使用这个SP？ - Subrata Sarkar

@NiladriSarkar：SELECT语句在cmd.CommandText中的使用是非常普遍的。你可以使用cmd.CommandText = "SELECT * FROM vwMyViewFromDatabase"。你只需要移除cmd.CommandType = CommandType.StoredProcedure;即可。你可以使用带参数的SELECT语句，而且设置参数值的方式与使用存储过程的情况完全相同。 - Oleg

@ 哎呀！我真是个傻瓜。:P - Subrata Sarkar

@user1447718：我不是很理解你的意思。在我的方法中，可以使用任何带有任何参数的存储过程。最主要的目标是使用Entity Framework Core函数而不需要在C#代码中定义任何实体。通过HTTP Get返回指定存储过程返回的所有字段的完整SELECT结果将作为JSON数据返回。因此，只需要维护STORED PROCEDURE和JavaScript文件的代码。C#代码返回new List<dynamic>，对于数据库模型的任何更改（添加新列，重命名列等），它都是透明的。 - Oleg

Answer 2

DbContext类有一个Database属性，其中保存了连接到数据库的内容，您可以随意处理该内容：

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

然而，我建议不要在您的Web Api操作中这样做，而是建议将方法添加到您的上下文或与您的上下文交互的任何服务/存储库中。然后只需在操作中调用此方法。理想情况下，您希望将所有SQL相关内容放在一个地方。

- Chris Pratt

---

我想以更EF-ish的方式完成它，通过在EDM设计工具上放置一个SP，标记其类型（Scalar、Complex等），最后像EntityFramework对象一样调用它。如果可以使用ASP.NET和EntityFramework 7完成所有这些将会很棒。如果7不是解决方案，我可以回到以前的版本，如6.1.3，但这给我带来了一个错误，我不知道如何解决。此外，我在VS 2015 Community版中没有看到任何可用于添加到我的项目中的“实体数据模型”（EDM）模板！ - Subrata Sarkar

这不是“EF式”的方式。EF在EF7中停止支持EDMX。EDM设计器已被弃用，好事将近。 - Chris Pratt

是的，EF7已经放弃了EDM。你有没有想法，这是永久性的还是暂时的措施？ - Subrata Sarkar

Answer 3

使用MySQL连接器和Entity Framework Core 2.0

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

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

使用参数的OR。

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

- chri3g91

Answer 4

与上面的答案一样，您可以简单地使用FromSQL()而不是SqlQuery<>()。

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

- TGarrett

---

我刚刚收到了一个错误信息：“无法从使用中推断出 DbContext.Set<TEntity>() 的类型参数”。 - Douglas Gaskell

请提供您使用的代码，还有您正在使用的.NET框架是什么？ - TGarrett

.net core 1.1.0。没有context.Set().FromSql()方法。 - wtf512

Answer 5

对于数据库优先方法，您必须使用Scaffold-DbContext命令

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

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

但是这样做无法获取您的存储过程。它仍在进行中，跟踪问题#245

但是，要执行存储过程，请使用FromSql方法执行原始SQL查询

例如：

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

使用带参数的方式

var productCategory= "Electronics";

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

或者

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

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

执行原始SQL查询或存储过程存在某些限制。您不能将其用于INSERT / UPDATE / DELETE。如果要执行INSERT，UPDATE，DELETE查询，请使用ExecuteSqlCommand。

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

- Rohith