none
Entity Framework遇到型別轉換及大量資料 RRS feed

  • 問題


  • Entity Framework遇到型別轉換及大量資料

    對於既定的資料格式無法變更下,
    欄位型態設定成字串,但卻存的是日期。
    使用LINQ,無法將字串轉成日期格式,
    如下:CounterDateTime資料型別為char(24),透過轉型成日期來做搜尋條件過濾

            public IEnumerable<CounterDataVM> GetCounterDataforDay1(string inDate)
            {
                DateTime dt1, dt2;
                dt1 = Convert.ToDateTime(inDate + " 00:00:00");
                dt2 = Convert.ToDateTime(inDate + " 23:59:59");
                IEnumerable<CounterDataVM> query = db.CounterDetails.AsQueryable().Join(
                    db.CounterData,
                    cd => cd.CounterID,
                    cda => cda.CounterID,
                    (cd, cda) => new CounterDataVM
                    {
                        MachineName = cd.MachineName,
                        ObjectName = cd.ObjectName,
                        CounterName = cd.CounterName,
                        InstanceName = cd.InstanceName,
                        CounterDateTime = Convert.ToDateTime(cda.CounterDateTime),
                        CounterValue = cda.CounterValue
                    })               
                    .Where(f => f.ObjectName == "PhysicalDisk" || f.ObjectName == "Processor")
                    .Where(f => f.CounterName == "Avg. Disk Bytes/Read" || f.CounterName == "Avg. Disk Bytes/Write" || f.CounterName == "% Processor Time")
                    .Where(f => f.InstanceName == "_Total")
                    .Where(f => f.CounterDateTime >= dt1 && f.CounterDateTime <= dt2)
                    .OrderByDescending(o => o.CounterDateTime);
                return query;
            }

    但出現類型 'System.NotSupportedException' 的未處理例外狀況發生於 EntityFramework.SqlServer.dll

    其他資訊: LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression

    透過查詢 msdn文件Linq to Entities 不支援轉型。

    如果使用LINQ的let結果還是一樣。

                DateTime dt1 = Convert.ToDateTime("2014-09-20 00:00:00");
                dbEntities db = new dbEntities();
                var query = from data in db.CounterData
                                let counterDT = Convert.ToDateTime(data.CounterDateTime)
                                where counterDT >= dt1
                                select data;

    但是如果把它先轉成AsEnumerable()就沒問題。

                var query = from data in db.CounterData.AsEnumerable()
                                let counterDT = Convert.ToDateTime(data.CounterDateTime)
                                where counterDT >= dt1
                                select data;

    我的問題是如果CounterData這個Table裡的資料有千萬筆以上,
    使用.AsEnumerable()或ToList(),會先將資料撈到AP的內存裡再進行查詢,
    這個觀念不知道是不是正確的,如果是的話,
    那又如何透過轉型直接在SQL Server查詢後丟回AP呢?

    另外還有個問題是有個表格的欄位存的是一組物品編號,其中要第5-7碼為特殊用途,
    需要使用Substring擷取出來轉成數字來做Where查詢比較使用,這樣需求該如何達成呢?

    是否有高人可以指點迷津呢?

    在此感激不盡。

    2014年9月21日 上午 07:51

所有回覆

  • 這種需求你寫 SQL 來做比較好。

    Entity Framework 在解析你的 LINQ 的過程中,會試著將你的函數呼叫轉換成 SQL,如果遇到未支援的函數,立馬會擲回 NotSupportedException,這很正常,又因為你的表有千萬筆,用 ToList() 會傳回所有資料,不利效能,所以最好是用 SQL 來處理。

    Substring 本身是受支援的,所以能直接用在 LINQ。


    強力監督SQL Injection問題!!

      • 小朱的技術隨手寫:http://www.dotblogs.com.tw/regionbbs/
      • 雲端學堂Facebook: http://www.facebook.com/studyazure

    • 已提議為解答 Bill ChungMVP 2014年10月21日 上午 09:13
    2014年9月21日 上午 08:43
    版主
  • 除了直接寫SQL撈出資料後再轉成強型別,已別無方法了嗎?

    所以專案在使用以DB-Firsr建立Entity Framework 時,
    資料結構在建立時型別與儲存的資料盡可能一致,不然遇到型別轉換上,
    例如常用的有字串擷取後轉數字,在資料量不大下,還可使用AsEnumerable,
    但又怎知資料往後會成長到如此階段,最好還是只能直接使用SQL語法,
    對於效能來說,會有較好的結果,是這樣的嗎?

    2014年9月21日 上午 09:10
  • 主要是今天你的作法是要由 string -> datetime,剛好 EF 不支援,才要寫 SQL,就這麼簡單。
    一個 Framework 不可能所有用法都涵蓋到,尤其是一般設計上根本不會有用 string 存 datetime 這種事。
    不管是不是 DB First 都一樣。

    之所以說會有效能影響,是因為你說一次要撈回全部資料。
    你可以用 Where 先期過濾再傳回來 (Where 會轉換成 SQL WHERE,但不支援的函數一樣不支援),縮小回傳的資料大小,效能上就不會有太大問題。


    強力監督SQL Injection問題!!

      • 小朱的技術隨手寫:http://www.dotblogs.com.tw/regionbbs/
      • 雲端學堂Facebook: http://www.facebook.com/studyazure

    • 已提議為解答 No.18MVP 2014年9月22日 上午 02:39
    2014年9月21日 上午 09:26
    版主
  • 沒錯,一般設計上根本不會用 string 存 datetime,
    我了解,感謝您的回覆,
    另外想問一下
    string -> int,EF 是否有支援呢?

    2014年9月21日 上午 10:41
  • 標準函式與 CLR 函式的對應:

    http://msdn.microsoft.com/zh-tw/library/vstudio/bb738681(v=vs.100).aspx

    未來 Entity Framework 7 或許會開始支援 Convert,但還不確定。


    強力監督SQL Injection問題!!

      • 小朱的技術隨手寫:http://www.dotblogs.com.tw/regionbbs/
      • 雲端學堂Facebook: http://www.facebook.com/studyazure


    2014年9月21日 上午 10:53
    版主
  • 先利用AsEnumerable處理之後再和Convert.ToDateTime的結果比對是OK的, 這是因為LINQ to Entities未支援Convert.ToDateTime, 但是LINQ to Object支援, 經由AsEnumerable或是ToList處理之後就變成LINQ to Object了, 所以會成功, 這樣做是OK的

    string=>int可以利用相同的技巧處理

    另外如果記錄很多, 記得一定要用AsEnumerable, 勿用ToList

    • 已編輯 tihsMVP 2014年9月22日 上午 04:18
    2014年9月22日 上午 04:15