实体框架 - 架构升级、多个 DBMS 和代码优先
我正在考虑在即将推出的项目中使用 Microsoft 的实体框架，该项目是现有产品的单点版本。我们当前的产品支持两个 DBMS（Oracle 和 SQL Server），每个 DBMS 的架构都在单独的 .sql 脚本文件中维护。
实体框架（4.1）看起来很吸引人，因为它允许通过代码生成、反射等自动实现各种场景。但是，据我所知，其中一些好处似乎是相互排斥的。
例如，为了支持多个 DMBS，我推断我需要使用模型或代码优先设计，在这种情况下 EF 将根据模型为每个模型生成架构（我几乎没有看到任何关于此的帖子或文档） ，所以我可能是错的）。这意味着我们现有的模式需要被放弃（模型优先）或映射（代码优先）。此外，更新架构需要手动脚本，因为 EF 似乎不支持架构升级（不擦除数据）。
- 模型优先和代码优先是在 EF 中支持多个 DBMS 的唯一可行方法吗？我意识到从技术上讲不可能保证两个任意模式相同，所以我认为这是真的。
- 代码优先和映射到多个 DBMS 系统是否存在任何潜在的陷阱？例如，Oracle没有自动增量列；你必须使用序列。这是如何映射到 DbContext 中的？我需要为每个 DBMS 创建单独的映射吗？
- EF 是否支持任何机制来将现有 DBMS 架构升级到其中一个代表 EF 模型的架构（架构重新创建 =/= 升级），还是我仅限于手动执行此操作？
- 我确实想出了一种首先使用数据库并支持多个 DBMS 的可能方法，但这是维护的噩梦。这个想法是向两个生成的数据模型添加另一层抽象，并为每个 EF 生成的模型创建转换器类。这似乎是最好的方法，这样每个 DBMS 都可能有自己的模型，但我的代码将处理映射。但在这样做的过程中，我真正从 EF 中获得了什么？也许查询生成，但这值得吗？
I'm looking into using Microsoft's Entity Framework in an upcoming project which is a point release of an existing product. Our current product supports two DBMS (Oracle and SQL Server), the schema of each is maintained in separate .sql script files.
The entity framework (4.1) looks appealing because it allows various scenarios to be implemented automatically via code generation, reflection, etc. However, as far as I can tell, some of these benefits appear to be mutually exclusive of others.
For example, to support multiple DMBSes, I am inferring that I would need to use a model or code first design, in which case EF would generate the schema for each according to the model (I have seen little to no posts or documentation on this, so I may be wrong). This means that our existing schema would need to be either abandoned (model-first), or mapped (code-first). Additionally, updating the schema would require manual scripts as EF does not appear to support schema upgrades (without wiping out data).
- Are model-first and code-first the only viable means of supporting multiple DBMSes in EF? I realize that technically it would be impossible to guarantee that two arbitrary schemae are the same, so I am thinking this is true.
- Are there any potential pitfalls of code-first and mapping to multiple DBMS systems? For example, Oracle does not have auto-increment columns; you have to use sequences. How is this mapped in the DbContext? Do I need to create separate maps for each DBMS?
- Does EF support any mechanism to upgrade an existing DBMS schema to one of which is representative of the EF model (schema recreation =/= upgrade), or am I limited to doing this manually?
- I did come up with one possible way to use database first and support multiple DBMSes, however it is a maintenance nightmare. The idea was to add another layer of abstraction to the two generated data models and create converter classes for each of the EF generated models. This seems like the best way of doing it so that each DBMS could potentially have its own model, yet my code would handle the mapping. But in doing this, what am I really gaining from EF? Maybe query generation, but is that worth it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
实际上模型优先和数据库优先都有相同的约束。这两种方法都使用 EDMX 文件，其中包含与单个数据库提供程序直接相关的 SSDL（存储描述 = 数据库层）部分，因此如果您想要有两个不同的数据库提供程序，则必须有两个不同的 SSDL 部分并将它们保存在同步。您可以使用单个 CSDL（概念层的描述 = 您的模型类）和一个或两个 MSL（SSDL 和 CSDL 之间映射的描述 - 仅当表和列在两个 SSDL 中具有完全相同的名称时，才可以使用单个文件）。据我所知，EDMX 文件只能由单个 SSDL、CSDL 和 MSL 部分组成，因此我预计设计人员不支持这种情况，您将必须手动修改第二个 SSDL 或使用两个 EDMX = 模型每个更改两次。
代码优先方法可以使这变得更加简单，但问题是 Oracle 提供商在使用代码优先和数据库生成时有多好。提供者负责正确解释所需的功能，例如自动增量列的序列。
EF本身目前不支持升级现有数据库。使用 EDMX 时，数据库生成过程由 T4 模板或工作流控制，因此可以对其进行自定义，并且已经有一个名为 Entity Designer Database Generation Power Pack，允许使用模型优先方法增量构建数据库。问题是这个功能使用的是VS数据库工具。我认为这些工具仅适用于 SQL Server。我从来不喜欢这些自动化工具，所以我仍然认为应该借助一些工具来手动控制数据库升级，以获得当前和最后部署的数据库版本之间的差异脚本。仅当将新版本部署到生产环境时才需要 diff 脚本。在测试和开发环境中，您始终可以重新创建整个数据库。
使用两个 EDMX 模型时不应需要任何抽象。模型必须产生相同的概念层。在这种情况下，您只需要一组按约定映射的 POCO 类（与实体相同的类名、具有相同类型和可访问性的相同属性），因此它们将适用于两种模型。
编辑：
根据 @Tridus 的回答，我只是补充说，您可以首先创建数据库，然后使用 EF 4.1 中的 FluentAPI 来映射它们。您的数据库必须具有完全相同的架构（表名、列名等），它们不能使用任何特定功能（我希望序列不会成为问题，因为这只是 Oracle 处理自动增量列的方式）。
Actually both the model-first and the database-first have same constraints. Both these approaches are using an EDMX file which contains SSDL (a description of store = a database layer) part related directly to a single database provider so if you want to have two different database providers you must have two different SSDL parts and keep them in sync. You can use single CSDL (a description of conceptual layer = your model classes) and a single or two MSLs (a description of mapping between SSDL and CSDL - a single file is possible only if tables and columns will have exactly same names in both SSDLs). As I know EDMX file can consists only from single SSDL, CSDL and MSL parts so I expect that the designer has no support for this scenario and you will have to modify second SSDL manually or use two EDMXs = model each change twice.
The code-first approach can make this much more simple but the question is how good is Oracle provider when using the code-first and the database generation. The provider is responsible for correctly interpreting needed features like sequences in case of auto increment columns.
EF itself currently has no support for upgrading existing DB. When using EDMX the process of the database generation is controlled either by T4 template or Workflow so it can be customized and there is already separate feature called Entity Designer Database Generation Power Pack which allow incremental building of the database with the model-first approach. The problem is that this feature is using VS Database tools. I think these tools works only with SQL server. I never like these automated tools so I still think that database upgrade should be controlled manually with help of some tools to get difference script between the current and the last deployed database versions. You should need diff script only when deploying new the new version to a production environment. In a testing and a development environment you can always recreate the whole database.
There should be no abstraction needed when working with two EDMX models. Models must produce the same conceptual layer. In such case you need only a single set of POCO classes which are mapped by conventions (same class name as the entity, same properties with same types and accesibility) so they will work with both models.
Edit:
Based on @Tridus answer I'm just adding that you can create databases first and use fluentAPI from EF 4.1 to map them. Your databases must have exactly the same schema (table names, column names, etc.), they can't use any specific features (I hope sequences will not be the problem because it is just the way how Oracle handles auto increment columns).
对于数据库优先设计来说，这实际上是相当可行的，但是由于数据库处理事物的方式不同，有一些警告您将无法轻松解决。
序列就是其中之一（因为它们完全被 EF 忽略）。你可以在 Oracle 中伪造这一点，方法是在表上放置一个触发器，在插入时填充它，但我还发现，如果你稍后必须更新模型，那么 EF 会“忘记”该列是一个标识列，它会尝试再在里面加个0。我还发现，如果您使用触发器，在 Oracle 中尝试获取新 ID 是不可靠的。我们只是在执行插入之前从序列中进行选择并在对象上设置 ID，因为这就是在 Oracle 中通常执行的操作方式。您还可以使用处理它的存储过程。
数字的处理方式不同。 SQL Server 使用映射到 Int32、Int64 等的数字格式。Oracle 的数字格式完全不同，SQL Server 中的全范围 Int32 是 Oracle 中的 Number(10,0)...这实际上是 EF 中的 Int64，因为它是比 Int32 更大。我还发现 Oracle 的 EF 提供程序喜欢大量使用 Decimal，即使并非必须如此，但这可能只是一个测试版问题。
Oracle 中的存储过程需要将一些值放入 app.config/web.config 中才能在 EF 中工作。我不确定这是否会在 SQL Server 中造成混乱，或者是否会导致问题。
最后，EF Code First 还相当不成熟，根据文档，该版本不支持更改数据库结构。我也不确定 Oracle 的提供商是否支持它（可能，还没有尝试过）。
其中大部分内容都是您可以绕过的，但是您需要做一些工作来隐藏与其余代码的差异，并且可能需要一个包装层来完成它。
编辑 - 关于您的 #4 - EF 4.1 可以生成部分 POCO 类。您可以创建另一个在更新模型时不会重新生成的部分类代码文件，然后添加隐藏差异的属性/方法，而不是围绕每个生成的模型编写包装器来隐藏任何差异。您的应用程序代码只需要注意使用它们，它们就会处理问题（就像我提到的数字问题，您可以使用另一个可以为 Oracle 进行必要转换的属性来完全隐藏它）。
This is actually fairly doable with a database first design, but there's some caveats you won't be able to get around easily due to how the databases handle things differently.
Sequences are one (in that they're just ignored by EF entirely). You can fake that in Oracle by putting a trigger on the table that populates it on Insert, but I also found that if you have to update the model later then EF "forgets" that the column is an identity column and it'll try to stick a 0 in it again. I also found it unreliable in Oracle to try and get the new ID if you use a trigger. We just wound up selecting from the sequence and setting the ID on the object before doing the insert because that's how you usually do it in Oracle. You could also use a stored procedure that handles it.
Numbers aren't handled the same way. SQL Server uses number formats that map to Int32, Int64, etc. Oracle's number format is totally different and a full range Int32 in SQL Server is a Number(10,0) in Oracle... which is actually an Int64 in EF because it's bigger then an Int32. I also found that Oracle's EF provider likes to use Decimal a lot even when it doesn't have to, but that's probably just a beta issue.
Stored Procedures in Oracle require some values to be put in app.config/web.config in order to work in EF. I'm not sure if that's going to just be clutter in SQL Server or if it'll cause problems.
Finally, EF Code First is pretty immature and according to the docs doesn't support changing the database structure in this version. I'm not sure if Oracle's provider supports it either (it might, haven't tried it).
Most of this is stuff you can get around, but you're going to need to do some work to hide the differences from the rest of your code and it'll probably take a wrapper layer to do it.
edit - In regards to your #4 - EF 4.1 can generate partial POCO classes. Instead of writing a wrapper around each of the generated models to hide any differences, you can create another partial class code file that won't be regenerated when you update the model, and then add properties/methods that hide the differences. Your app code would just have to be aware to use those instead, and they'd handle the issue (like the number issue I mentioned, you could completely hide it with another property that can do the necessary casting for Oracle).