Entity Framework 4.1 自定义数据库初始化器策略

发布于 2024-11-24 15:28:25 字数 132 浏览 8 评论 0 原文

我想实现自定义数据库初始化策略，以便我可以：

如果不存在，则生成数据库
如果模型更改仅创建新表
如果模型更改仅创建新字段而不删除表并丢失数据。

提前致谢

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦巷 2024-12-01 15:28:26

您需要实现 IDatabaseInitializer 接口。

例如

public class MyInitializer : IDatabaseInitializer<MyDbContext>
{
    public void InitializeDatabase(MyDbContext context)
    {
        //your logic here
    }
}

，然后在应用程序启动时设置初始化程序

Database.SetInitializer<ProductCatalog>(new MyInitializer());

这是一个示例

您将必须手动执行命令来更改数据库。

context.ObjectContext.ExecuteStoreCommand("ALTER TABLE dbo.MyTable ADD NewColumn VARCHAR(20) NULL");

您可以使用 SQL Compare 等工具来更改脚本。

You need to implement IDatabaseInitializer interface.

public class MyInitializer : IDatabaseInitializer<MyDbContext>
{
    public void InitializeDatabase(MyDbContext context)
    {
        //your logic here
    }
}

And then set your initializer at your application startup

Database.SetInitializer<ProductCatalog>(new MyInitializer());

Here's an example

You will have to manually execute commands to alter the database.

context.ObjectContext.ExecuteStoreCommand("ALTER TABLE dbo.MyTable ADD NewColumn VARCHAR(20) NULL");

You can use a tool like SQL Compare to script changes.

回复收藏 0 原文

爱*していゐ 2024-12-01 15:28:26

这尚不存在是有原因的。它非常复杂，而且 IDatabaseInitializer 接口还没有做好准备（没有办法使这种初始化与数据库无关）。您的问题“太宽泛”，无法得到令您满意的答复。根据您对 @Eranga 正确答案的反应，您只是希望有人会一步一步告诉您如何做到这一点，但我们不会 - 这意味着我们将为您编写初始化程序。

做你想做的事需要什么？

您必须非常了解 SQL Server。您必须知道 SQL Server 如何存储有关数据库、表、列和关系的信息 = 您必须了解 sys 视图，并且必须知道如何查询它们以获取有关当前数据库结构的数据。
您必须非常了解 EF。你必须知道EF是如何存储映射信息的。您必须能够探索元数据以获取有关预期表、列和关系的信息。
一旦有了旧数据库描述和新数据库描述，您必须能够编写代码来正确探索更改并创建用于更改数据库的 SQL DDL 命令。即使这看起来像是整个过程中最简单的部分，但这实际上是最难的部分，因为 SQL Server 中还有许多其他内部规则，您的命令不能违反这些规则。有时您确实需要删除表来进行更改，如果您不想丢失数据，则必须首先将它们推送到临时表，然后在重新创建表后必须将它们推回。有时您正在对约束进行更改，这可能需要暂时关闭约束等。在 SQL 级别（比较两个数据库）上执行此操作的工具可能都是商业的，这是有充分理由的。

即使 ADO.NET 团队没有实现这一点，他们将来也不会实现它。相反，他们正在致力于所谓的迁移。

编辑：

ObjectContext 确实可以返回用于数据库创建的脚本 - 这正是默认初始值设定项所使用的。但它对你有什么帮助呢？您要解析该脚本以查看发生了什么变化吗？您是否要在另一个连接中执行该脚本以使用与当前数据库相同的代码来查看其结构？

是的，您可以创建一个新数据库，将数据从旧数据库移动到新数据库，删除旧数据库并重命名新数据库，但这是您能想象到的最愚蠢的解决方案，没有数据库管理员会允许这样做。即使这个解决方案仍然需要分析更改以创建正确的数据传输脚本。

自动升级是一个错误的方式。您应该始终在某些工具的帮助下手动准备升级脚本，对其进行测试，然后手动执行它或作为某些安装脚本/包的一部分。在进行任何更改之前，您还必须备份数据库。

There is a reason why this doesn't exist yet. It is very complex and moreover IDatabaseInitializer interface is not very prepared for such that (there is no way to make such initialization database agnostic). Your question is "too broad" to be answered to your satisfaction. With your reaction to @Eranga's correct answer you simply expect that somebody will tell you step by step how to do that but we will not - that would mean we will write the initializer for you.

What you need to do what you want?

You must have very good knowledge of SQL Server. You must know how does SQL server store information about database, tables, columns and relations = you must understand sys views and you must know how to query them to get data about current database structure.
You must have very good knowledge of EF. You must know how does EF store mapping information. You must be able to explore metadata get information about expected tables, columns and relations.
Once you have old database description and new database description you must be able to write a code which will correctly explore changes and create SQL DDL commands for changing your database. Even this look like the simplest part of the whole process this is actually the hardest one because there are many other internal rules in SQL server which cannot be violated by your commands. Sometimes you really need to drop table to make your changes and if you don't want to lose data you must first push them to temporary table and after recreating table you must push them back. Sometimes you are doing changes in constraints which can require temporarily turning constrains off, etc. There is good reason why tools which do this on SQL level (comparing two databases) are probably all commercial.

Even ADO.NET team doesn't implemented this and they will not implement it in the future. Instead they are working on something called migrations.

Edit:

That is true that ObjectContext can return you script for database creation - that is exactly what default initializers are using. But how it could help you? Are you going to parse that script to see what changed? Are you going to execute that script in another connection to use the same code as for current database to see its structure?

Yes you can create a new database, move data from the old database to a new one, delete the old one and rename a new one but that is the most stupid solution you can ever imagine and no database administrator will ever allow that. Even this solution still requires analysis of changes to create correct data transfer scripts.

Automatic upgrade is a wrong way. You should always prepare upgrade script manually with help of some tools, test it and after that execute it manually or as part of some installation script / package. You must also backup your database before you are going to do any changes.

回复收藏 0 原文