使用Database First方式
需要安裝mysql-for-visualstudio-1.2.7.msi孩革，以及mysql-connector-net-6.9.9.msi。
在VS2013中執(zhí)行如下步驟：
- 添加ADO.NET實(shí)體數(shù)據(jù)模型瞬雹，下一步
- 選擇“來(lái)自數(shù)據(jù)庫(kù)的Code First”躯砰，下一步
- 使用數(shù)據(jù)提供程序“.Net Framework Data Provider for MySQL”建立數(shù)據(jù)連接
- 完成后選擇需要映射成實(shí)體類(lèi)的數(shù)據(jù)庫(kù)表活箕。
使用Load()和Local
性能考量
參考資料[1]魂仍，主要講了include和Load的區(qū)別，前者會(huì)使用Join生成復(fù)雜的SQL語(yǔ)句磁滚，后者則是簡(jiǎn)單的Select語(yǔ)句。對(duì)于遠(yuǎn)程SQL訪問(wèn)大數(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.
問(wèn)題集
MySQL的設(shè)置問(wèn)題
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)以下異常,無(wú)法生成模型:“表“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';
這樣就可以解決問(wèn)題了。
其中derived_merge的含義是“Controls merging of derived tables and views into outer query block”晒他，默認(rèn)情況下是打開(kāi)的吱型。
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
