如何配置 DbContext 以使用 Oracle ODP.Net 和 EF CodeFirst？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9729750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to configure DbContext to work with Oracle ODP.Net and EF CodeFirst?
提问by fcaldera
I'm trying to work with EF CodeFirst under Oracle with ODP.net. This is my DbContext class:
我正在尝试使用 ODP.net 在 Oracle 下使用 EF CodeFirst。这是我的 DbContext 类：
public class MyCEContext : DbContext {
public DbSet<Person> Persons { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder) {
modelBuilder.Entity<Person>().ToTable("PERSONS","myce");
}
public MyCEContext() :
base(new OracleConnection(
"Data Source=cebd; User ID=myce; Password=****;"), true) {}
}
Problem is that when I try to do something like this:
问题是，当我尝试做这样的事情时：
MyCEContext context = new MyCEContext();
Person p = context.Persons.Find(1);
I get this inner error:
我收到这个内部错误：
{"ORA-00942: table or view does not exist"}
And the table exists.
并且该表存在。
What am I doing wrong?
我究竟做错了什么？
采纳答案by fcaldera
As Nick wrote in his answer, the issue is related with the quotes and case of the generated query, but not with the table's names but with schema's name:
正如尼克在他的回答中所写的那样，问题与生成的查询的引号和大小写有关，但与表的名称无关，而是与模式的名称有关：
SELECT *
FROM "myce"."PERSONS" "Extent1"
So the solution is very simple, just to uppercase the user id and the schema name:
所以解决方法很简单，只需将用户id和schema名称大写即可：
modelBuilder.Entity<Person>().ToTable("PERSONS","MYCE");
In general, all must be in uppercase: tables, schema and field's names. But it is better annotate each mapped property with the Column attribute instead of uppercase the property name:
通常，所有内容都必须大写：表、架构和字段的名称。但是最好使用 Column 属性而不是大写属性名称来注释每个映射的属性：
[Column("FIRST_NAME")]
public string FirstName { get; set; }
Thus the names will be easier to read in both database and classes.
因此，名称在数据库和类中都更易于阅读。
回答by Nick
Your issue is most likely because EF passes the query to Oracle in quotes, which means that the case on your tables and your fields has to match that of the database.
您的问题很可能是因为 EF 用引号将查询传递给 Oracle，这意味着您的表和字段的大小写必须与数据库的大小写相匹配。
So if you had the following:
所以如果你有以下几点：
select name from persons;
The EF code will probably be firing the following SQL:
EF 代码可能会触发以下 SQL：
select "NAME" from "PERSONS";
Add this to your OnModelCreating function:
将此添加到您的 OnModelCreating 函数中：
modelBuilder.Conventions.Remove<ColumnTypeCasingConvention>();
...and construct your POCO object with uppercase property names rather than the normal sentance case.
...并使用大写的属性名称而不是普通的句子来构造您的 POCO 对象。
If you want to see the SQL, break the code and take a look at the DbContext.Persons object. You should see the actual sql command it will use to query the whole table (quite larger)
如果您想查看 SQL，请打破代码并查看 DbContext.Persons 对象。您应该会看到它将用于查询整个表的实际 sql 命令（相当大）
Note
笔记
We use Oracle EF Code First in production. Although not officially supported, there doesn't seem to be anything missing from the latest ODAC release that will prevent you.
我们在生产中使用 Oracle EF Code First。虽然没有得到官方支持，但最新的 ODAC 版本似乎没有遗漏任何会阻止你的东西。
回答by Barry
You can call ToString on the linq query that you run against your dbcontext object. This will show you the SQL being generated. That should help you find the problem
您可以在针对 dbcontext 对象运行的 linq 查询上调用 ToString。这将显示正在生成的 SQL。这应该可以帮助您找到问题
My problem was two fold:
我的问题有两个方面：
- My table names were being pluralized
- My table names were being prefaced with "dbo."
- 我的表名被复数化
- 我的表名以“dbo”开头。
回答by Barry
If you don't want to map every column of your application as a workaround for the quoting problem, mentioned by @fcaldera, you can use the "Devart's dotConnect for Oracle" provider.
如果您不想将应用程序的每一列都映射为@fcaldera 提到的引用问题的解决方法，则可以使用“Devart 的 dotConnect for Oracle”提供程序。
And just the following code is needed:
只需要以下代码：
var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.Workarounds.DisableQuoting = true;
Now you can map a class "MyObject" to a table MYOBJECT without problems. And it's the same for the columns.
现在，您可以毫无问题地将类“MyObject”映射到表 MYOBJECT。列也是一样的。
Note:NuGet version of "dotConnect for Oracle" doesn't support Entity Framework. It's necessary to download the trial or professional version from Devart's site.
注意：“dotConnect for Oracle”的 NuGet 版本不支持实体框架。有必要从 Devart 的站点下载试用版或专业版。
