如何使用SQL Server视图与EF代码第一次迁移 [英] How to use SQL Server Views with EF Code First Migrations

问题描述

当我这样做时，我会得到一组我的每个表的模型和映射类，当我尝试使用生成的 DbContext 查询数据库时，这些工作很好。

它也为我的每个视图生成POCO类，但是当我运行启用迁移和添加迁移使用包管理器控制台，迁移不会重新创建SQL Server视图。

我已经尝试手动更新迁移以使用自定义SQL创建视图：

  Sql（CREATE VIEW [dbo]。[viewUsersDeactivated]
 +AS
 +SELECT ...）; 
  

当我运行更新数据库，但是从 Update-Database -Script 命令输出的SQL脚本似乎无效，导致以下错误：

'CREATE VIEW'必须是批中唯一的陈述。

在每个自定义SQL语句之间插入 Sql（GO）; 适用于脚本生成，但是我不能再使用更新-Database ，我找不到一种适用于这两种方式。

所以我的问题归结为：我在代码第一个迁移脚本中包含SQL Server视图，以便我可以使用 Update-Database 创建数据库，并通过使用 Update-数据库-Script ？

谢谢！

解决方案

不完全确定这是最佳实践ce，其他人可能会有一个更好的解决方案，但是我通过将整个视图创建脚本包裹在Execute中来解决这个问题，因此：

  var sql =CREATE VIEW [dbo]。[TheViewName] AS .......
 sql = sql.Replace（'，'）; //清理sql string 
 sql = string.Format（EXECUTE sp_executesql N{0}，sql）; 
 Sql（sql）; 
  

I've reverse engineered a small database that contains a number of tables and views into EF 5 code first.

When I do this, I get a set of models and mapping classes for each of my tables and these work well when I try to query the database using the generated DbContext.

It also generates POCO classes for each of my views, but when I run Enable-Migrations and Add-Migration using the Package Manager Console, the migration does not re-create the SQL Server views.

I have tried updating the migration manually to create the views using custom SQL like so:

Sql("CREATE VIEW [dbo].[viewUsersDeactivated] "
          + "AS "
          + "SELECT ... ");

This works fine when I run Update-Database, but the SQL script that is output from the Update-Database -Script command does not seem to be valid, resulting in the following error:

'CREATE VIEW' must be the only statement in the batch.

Inserting Sql("GO"); between each of the custom SQL statements works for the script generation, but then I can no longer update using Update-Database, and I can't find a way that works for both.

So my question boils down to: How can I include SQL Server views in a code first migration script such that I can create a database both using Update-Database and via production scripts generated using Update-Database -Script?

Thanks!

解决方案

Not entirely sure this is best practice, and someone else might have a better solution, but I got around this by wrapping the entire view creation script in an Execute, thus:

var sql = "CREATE VIEW [dbo].[TheViewName] AS ......."
sql = sql.Replace("'", "''"); // sanitize the sql string
sql = string.Format("EXECUTE sp_executesql N'{0}'", sql);
Sql(sql);

这篇关于如何使用SQL Server视图与EF代码第一次迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！