实体框架与mysql，linux和windows之间的表资本化问题

We are currently developing a product using Code First Entity Framework and Mysql. The development database is hosted in a Windows environement while the production mysql is on Linux.

我们目前正在使用Code First Entity Framework和Mysql开发产品。开发数据库托管在Windows环境中,而生产mysql在Linux上。

The issue I am running into is that tables in mysql are named like this:

我遇到的问题是mysql中的表命名如下:

 mydatabase.industry
 mydatabase.account
 ...

Entity framework creates a query like this:

实体框架创建一个这样的查询:

  Select * FROM mydatabase.Industry;

Notice the capitalized letter. This works fine on mysql in Windows, but on Linux I get this error:

注意大写字母。这在Windows中的mysql上工作正常,但在Linux上我收到此错误:

  Table 'mydatabase.Industry' doesn't exist

Any Ideas?

4 个解决方案

#1

Entity Framework will use the same name (capitalization, etc) as is declared for the object. So, for example, if you declare a model object as:

实体框架将使用与为对象声明的相同名称(大小写等)。因此,例如,如果您将模型对象声明为:

public class Industry
{
  public int IndustryID { get; set; }
}

Entity Framework will look for a table of Industry with a column of IndustryID.

实体框架将查找具有IndustryID列的Industry表。

You can change this by adding annotations to your models. Do the following:

您可以通过向模型添加注释来更改此设置。请执行下列操作:

[Table("industry")]
public class Industry
{
  public int IndustryID { get; set; }
}

By doing this, your objects will still use the appropriate .NET naming scheme, but it will match your corresponding database. You can also change the name of the colunns using ColumnAttribute.

通过这样做,您的对象仍将使用适当的.NET命名方案,但它将匹配您的相应数据库。您还可以使用ColumnAttribute更改colunns的名称。

Alternatively, you could change the table names in MySQL.

或者,您可以更改MySQL中的表名。

#2

Entity framework creates a query like this:

实体框架创建一个这样的查询:

Select * FROM mydatabase.Industry; Notice the capitalized letter. This works fine on mysql in Windows, but on Linux I get this error:

选择* FROM mydatabase.Industry;注意大写字母。这在Windows中的mysql上工作正常,但在Linux上我收到此错误:

Table 'mydatabase.Industry' doesn't exist Any Ideas?

表'mydatabase.Industry'不存在任何想法?

This has nothing to do with entity framework itselfs.
It is a variation of the classic different default configuration on two different OS'es problem.

这与实体框架本身无关。它是两种不同操作系统问题的经典不同默认配置的变体。

The solution may not be necessary anymore, but here's the reason for your problem:

可能不再需要解决方案了,但这是您遇到问题的原因:

MySQL stores tables as files.
And MySQL was originally developped on Linux, where the filenames and therefor the tablenames were case-sensitive (because most Linux filesystems are case-sensitive).

MySQL将表存储为文件。 MySQL最初是在Linux上开发的,其中文件名和表名都区分大小写(因为大多数Linux文件系统都区分大小写)。

Then MySQL was ported to Windows, where the filesystem is case-insensitive.
On windows, the file-system is case-insensitive, and hence cannot distinguish lower&upper case files.
To resolve the resulting problems, there needed to be added the option to ignore the case of the table names.

So the identifier case-senstivity configuration variable "lower_case_table_names" was created, with which one can switch those settings.

Further information on the possible values [0, 1, 2] here: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

然后MySQL被移植到Windows,其中文件系统不区分大小写。在Windows上,文件系统不区分大小写,因此无法区分大小写文件。要解决由此产生的问题,需要添加选项以忽略表名的大小写。因此创建了标识符case-senstivity配置变量“lower_case_table_names”,可以使用它来切换这些设置。有关可能值[0,1,2]的更多信息,请访问:http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

On Windows, you can configure it in the my.ini file, found in or around:

在Windows上,您可以在my.ini文件中配置它,可在以下位置找到:

C:\Program Files\MySQL\MySQL Server X.Y

, depending on which version you are running. If you don't find the setting, you can just add it to the end of the my.ini file.

,具体取决于您运行的版本。如果找不到该设置,只需将其添加到my.ini文件的末尾即可。

On Linux, this config file is usually located in /etc/my.cnf or /etc/mysql/my.cnf but that may vary on the various Linux-dristributions and distribution versions.

在Linux上,此配置文件通常位于/etc/my.cnf或/etc/mysql/my.cnf中,但可能因各种Linux-dristributions和分发版本而异。

So the default for this value is

所以这个值的默认值是

on Linux: Case-Senstivie
on Windows: Case-Insensitive

Therefore, entity-framework generated SQL which was generated from a model with wrong casing works on Windows, but not on Linux.

因此,实体框架生成的SQL是从具有错误外壳的模型生成的,适用于Windows,但不适用于Linux。

All you need to do is to change the value of lower_case_table_names on Linux to 1 in the my.cnf file so that it is case-insensitive as well.

您需要做的就是在my.cnf文件中将Linux上的lower_case_table_names的值更改为1,以便它也不区分大小写。

Note that while you could also make MySQL on Windows case-sensitive, this is not a good idea.

请注意,虽然您也可以使Windows上的MySQL区分大小写,但这不是一个好主意。

Then, remember to restart the MySQL service/mysqld before you test whether or not it works.

然后,记住重新启动MySQL服务/ mysqld,然后再测试它是否有效。

In all cases, always remember to check whether it works on Linux as well, as the behaviour of MySQL is not always identical on those two systems.

在所有情况下,始终要记住检查它是否也适用于Linux,因为MySQL的行为在这两个系统上并不总是相同。

The reason for the different defaults is that on Linux you have a higher performance when you are in case-sensitive mode, because then you don't always have to lowercase names. On Windows on the other hand, having a case-insensitive configuration is better, because you can't have two files with the same name that only differ in casing in the same folder. This would lead to malfunctions. So the default is case-insensitive on Windows.

不同默认值的原因是,在Linux上,当您处于区分大小写模式时,您可以获得更高的性能,因为这样您就不必总是使用小写名称。另一方面,在Windows上,使用不区分大小写的配置会更好,因为您不能拥有两个具有相同名称的文件,这些文件只在同一文件夹中的大小写不同。这会导致故障。因此,Windows上的默认值不区分大小写。

Incidentially, this is why the performance of MySQL on Linux is better than the performance on Windows (amongst other things, like better scheduling and faster IO/filesystem on Linux)

从本质上讲,这就是为什么MySQL在Linux上的性能优于Windows上的性能(除了其他方面,比如在Linux上更好的调度和更快的IO /文件系统)

Note that you can also change the case-sensitity of queries (string comparison) by doing

请注意,您还可以通过执行更改查询的大小写敏感度(字符串比较)

CREATE DATABASE test_database CHARACTER SET utf8 COLLATE utf8_general_cs;

CREATE DATABASE test_database CHARACTER SET utf8 COLLATE utf8_general_ci;

respectively

And you can also set case-sensitivity on a single table, like this

您还可以在单个表上设置区分大小写,如下所示

DROP TABLE IF EXISTS single_test_table;
CREATE TABLE single_test_table(
  single_test_id int unsigned NOT NULL auto_increment,
  ...

  PRIMARY KEY PK_single_test_id (single_test_id ),
  ...
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_cs;

#3

Here is my answer taken from the comments:

以下是我对这些评论的回答:

usr: Why don't you change the configured table name to lowercase? The SQL table name and the entity name are independently configurable.

usr:为什么不将配置的表名更改为小写? SQL表名和实体名是可独立配置的。

AFrieze: Do you mean change the entity name to lowercase? So instead of a C# class "Industry" I would have a class "industry"?

AFrieze:你的意思是将实体名称改为小写吗?因此,我不会使用C#类“行业”,而是拥有一个“行业”类?

usr: No the two are different. You can have table name xyz mapped to entity Abc. – usr just now edit

usr:不,两者不同。您可以将表名xyz映射到实体Abc。 - 我们刚刚编辑

#4

with EF6 :

使用EF6:

      protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Types().Configure(entity => entity.ToTable(entity.ClrType.Name.ToLower()));
    }

#1