使用Database First方式

需要安裝mysql-for-visualstudio-1.2.7.msi，以及mysql-connector-net-6.9.9.msi。

在VS2013中執(zhí)行如下步驟：

• 添加ADO.NET實(shí)體數(shù)據(jù)模型，下一步
• 選擇“來自數(shù)據(jù)庫的Code First”，下一步
• 使用數(shù)據(jù)提供程序“.Net Framework Data Provider for MySQL”建立數(shù)據(jù)連接
• 完成后選擇需要映射成實(shí)體類的數(shù)據(jù)庫表。

使用Load()和Local

性能考量

參考資料[1]，主要講了include和Load的區(qū)別，前者會(huì)使用Join生成復(fù)雜的SQL語句，后者則是簡(jiǎn)單的Select語句。對(duì)于遠(yuǎn)程SQL訪問大數(shù)據(jù)，前者可能對(duì)于性能很有好處。

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的設(shè)置問題

Entity Framework連接MySQL時(shí)，出現(xiàn)如下錯(cuò)誤：

'System.Data.StrongTypingException: The value for column 'IsPrimaryKey' in table 'TableDetails' is DBNull . ---> System.InvalidCastException: Specified cast is not valid.
由于出現(xiàn)以下異常,無法生成模型:“表“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”，默認(rèn)情況下是打開的。

The derived_merge flag 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