EntityFramework 常見問題

相信很多人對EntiyFramework 愛不釋手，但因為本人最近發生某些悲劇, 在不瞭解entityFramework 某些特性下，卻時容易踩到地雷… 所以先寫下來提醒自已囉!!!

一、設定linq 像SQL一樣的with(nolock)的語法

context.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");// like with(nolock) sql, avoid deadlock

二、設定延長讀取SQL的TimeOut時間

context.Database.CommandTimeout = 1800;

三、EntityFramework 做了分頁處理，但仍然會出現SystemOutMemory ?

public override void DoJob() { context.Database.CommandTimeout = 1800; context.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");// like with(nolock) sql, avoid deadlock _db = new MallDbContext(); int totalCount = context.v_orderinfo4back.Count(); int settingPerCount = 1000000; int totalPageingCount = countPaging(totalCount, settingPerCount); var oders = (from b in context.v_orderinfo4back select new {     ORDERID = b.ORDERID,     SUPPLIERID = b.supplierid }).AsEnumerable().Select(q=> new vOrderInfo4Back() {     ORDERID = Convert.ToDouble(q.ORDERID),     SUPPLIERID = Convert.ToDouble(q.SUPPLIERID) }); for (int i = 0; i <= totalPageingCount - 1; i++) {     intSkipNumber = (i * settingPerCount);     if (surplusCount < settingPerCount)       {         settingPerCount = surplusCount;         var orders = oders.OrderBy(q => q.CREATEDDATE).Skip(intSkipNumber).Take(settingPerCount).AsQueryable();         surplusCount -= settingPerCount;     } }

有分頁處理，但該資料表將近1千萬筆，卻出現了SystemOutMemory等錯誤訊息，於是做了微幅調整

for (int i = 0; i <= totalPageingCount - 1; i++) {   intSkipNumber = (i * settingPerCount);   if (surplusCount < settingPerCount)    {     settingPerCount = surplusCount;     var oders = (from b in context.v_orderinfo4back.AsNoTracking().OrderBy(q =>     q.CREATEDDATE).Skip(intSkipNumber).Take(settingPerCount)     select new     {       ORDERID = b.ORDERID,       SUPPLIERID = b.supplierid,     }).AsEnumerable().Select(q => new vOrderInfo4Back()     {       ORDERID = Convert.ToDouble(q.ORDERID),       SUPPLIERID = Convert.ToDouble(q.SUPPLIERID),     }).AsQueryable();     surplusCount -= settingPerCount;            }

將實體資料分頁加上 AsNoTracking() 後就不會再出現systemOutMemory的錯誤訊息，傑克… 真神奇…

四、如何手動編修*.edmx

如果想手動修改 *.edmx的檔案，可按滑鼠右鍵-->開啟方式

 選擇XML 文件

五、如何取得驗證失敗的訊息

catch (DbEntityValidationException dbex)                     {                         foreach (var validationErrors indbex.EntityValidationErrors)                         {                             foreach (var validationError invalidationErrors.ValidationErrors)                             {                                 Exception errorMsg = newException(validationError.PropertyName + ":" + validationError.ErrorMessage);                                 _log.WarnException("【大批維護商品】更新關聯商品時發生錯誤", errorMsg);                             }                         }                     }

以上謝謝各位的參觀~