实体框架Oracle和Sql Server - 如何构建一个独立于数据库的应用程序 [英] Entity Framework Oracle and Sql Server - how to build a database independent application

问题描述

我们正在尝试建立一个使用Oracle和SQL Server（不是同时）的数据访问层。

我们使用EF Model-first创建模型并创建用于构建数据库的SQL脚本。我们的第一个想法是创建2个EDMX文件，每个类型一个，并根据客户的需要使用适当的一个。我们使用Oracle和SQL Server数据库生成工作流和DDL生成模板为每个数据库创建脚本。

我们的主要问题是当数据库模式更改时想要删除并重新创建数据库，但只创建迁移脚本来更新我们的模型的数据库基础（客户端有很多数据将丢失）。

我们使用EF

我们需要帮助找到一个好的数据层（1个EDMX适用于Oracle和SQL Server如果可能而不复杂），并且是一个很好的方式来生成数据库更改从我们的模型更新现有的客户端数据库在新的应用程序版本

我们发现这个作为起点
http://msdn.microsoft.com/en-us/data/ff830362 但是没有提到Oracle支持。

我们尝试过代码优先和EF迁移，但是Oracle在数据库创建和迁移方面再次失败了。 p>

有关如何完成此任务的任何建议吗？

谢谢

解决方案

没有办法为SQL Server和Oracle提供单个EDMX。 EDMX由三部分CSDL（实体定义），SSDL（数据库定义），MSL（这些定义之间的映射）组成。 SSDL必须始终指向具体的数据库，所以你需要至少单独的SSDL为Oracle和SQL Server，如果你幸运的是，你不需要单独的MSL（映射必须完全相同，如果你正在使用任何工具生成数据库）。

所以你总是需要至少一部分EDMX文件用于第二个DB并手动维护它。

如果您需要支持Oracle的数据库迁移，您必须从Oracle（或第三方）寻找工具。例如， RedGate 提供了支持模式迁移的工具， SQL Server 和 Oracle 。

Visual Studion Premium和Ultimate版本还提供了比较数据库模式。它默认只支持SQL Server，但 Toad Extensions 也应该添加对Oracle的支持。

一旦你有了这些工具，你只需要比较部署在客户服务器上的模式与你的新模式，该工具应为您创建迁移脚本。

We are trying to build a data access layer for using both Oracle and SQL Server (not at the same time).

We use EF Model-first for creating the model and the create the SQL scripts for building the database. Our first thought was to create 2 EDMX files, one for each type, and use the appropriate one depending on the client's need. We are using the Oracle and SQL Server database generation workflow and DDL generation template to create the scripts for each database.

Our main problem is when the database schema changes we do not want to drop and recreate the DB but only create the migration scripts to update the DB base on our model (clients have many data that will be lost).

We use EF power pack for extracting the migration scripts for SQL Server but there is nothing like it for Oracle.

We want help to find a good data layer (1 EDMX for both Oracle and SQL Server if it's possible and not complicated) and a good way to generate database changes from our model to update existing client DBs in case of a new application release

We found this as a starting point http://msdn.microsoft.com/en-us/data/ff830362 but there is not mention for Oracle support.

We have tried code-first and EF Migrations but Oracle failed us again on the DB creation and migration.

Any recommendation on how we can accomplish this?

Thank You

解决方案

There is no way to have single EDMX for both SQL Server and Oracle. EDMX consists of three parts CSDL (entity definition), SSDL (database definition), MSL (mapping between those definitions). SSDL must always target concrete database so you need at least separate SSDL for Oracle and SQL Server and if you are lucky you will not need separate MSL as well (mapping must be exactly same which will probably not happen if you are using any tool to generate the database).

So you always need at least part of EDMX file for second DB and manually maintain it.

If you need DB migration supporting Oracle you must look for tool from Oracle (or third party). For example RedGate offers tools supporting schema migration for both SQL Server and Oracle.

Visual Studion Premium and Ultimate edition also offers tools for comparing database schemas. It by default supports only SQL Server but Toad Extensions should add support for Oracle as well.

Once you have any of these tools you just need to compare schema deployed on customer server with your new schema and the tool should create migration script for you.

这篇关于实体框架Oracle和Sql Server - 如何构建一个独立于数据库的应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！