使用Database First方式
需要安装mysql-for-visualstudio-1.2.7.msi，以及mysql-connector-net-6.9.9.msi。
在VS2013中执行如下步骤：
- 添加ADO.NET实体数据模型，下一步
- 选择“来自数据库的Code First”，下一步
- 使用数据提供程序“.Net Framework Data Provider for MySQL”建立数据连接
- 完成后选择需要映射成实体类的数据库表。
使用Load()和Local
性能考量
参考资料[1]，主要讲了include和Load的区别，前者会使用Join生成复杂的SQL语句，后者则是简单的Select语句。对于远程SQL访问大数据，前者可能对于性能很有好处。
As a quick rule-of-thumb, I try to avoid having any more than three Include calls in a single query. I find that EF's queries get to ugly to recognize beyond that; it also matches my rule-of-thumb for SQL Server queries, that up to four JOIN statements in a single query works very well, but after that it's time to consider refactoring.
问题集
MySQL的设置问题
Entity Framework连接MySQL时，出现如下错误：
'System.Data.StrongTypingException: The value for column 'IsPrimaryKey' in table 'TableDetails' is DBNull . ---> System.InvalidCastException: Specified cast is not valid.
由于出现以下异常,无法生成模型:“表“TableDetails”中列“IsPrimaryKey”的值为DBNull.
Entity Framework (version 6.1.3) and MySQL Server (>= 5.7.6)
One way to resolve the issue is,
1. Open Services (services.msc) and restart MySQL57 service.
2. Execute the following commands in MySQL.
use <<database name>>;
set global optimizer_switch='derived_merge=OFF';
这样就可以解决问题了。
其中derived_merge的含义是“Controls merging of derived tables and views into outer query block”，默认情况下是打开的。
The
derived_mergeflag controls whether the optimizer attempts to merge derived tables and view references into the outer query block, assuming that no other rule prevents merging; for example, an ALGORITHM directive for a view takes precedence over the derived_merge setting. By default, the flag is on to enable merging. For more information, see Section 8.2.2.3, “Optimizing Derived Tables and View References”.
参考
[1] c# - .Include() vs .Load() performance in EntityFramework
