LINQ To Entity同時使用OrderBy與Distinct的小技巧

  • 13153
  • 0
  • LINQ
  • 2011-04-20

LINQ To Entity同時使用OrderBy與Distinct的小技巧

在LINQ To Entity中若先使用OrderBy方法對資料進行排序後,再用Distinct方法去除重複資料,轉成TSQL命令時OrderBy敘述會被取消,導致排序效果失效。

using (CTSEntities context = new CTSEntities())
{
   ObjectQuery<conference_data> oqConferenceData = context.conference_data;
   var result = (from p in oqConferenceData
                orderby p.Times ascending
                select p.Times).Distinct();  
}

使用SQL Profiler錄製TSQL敘述結果如下:

SELECT 
[Distinct1].[Times] AS [Times]
FROM ( SELECT DISTINCT 
    [Extent1].[Times] AS [Times]
    FROM [dbo].[conference_data] AS [Extent1]
)  AS [Distinct1]

問題癥結點出在這兩個方法的呼叫順序,只要改成先做Distinct再做OrderBy即可解決無法排序的問題,修改後的LINQ to Entity語法如下:

using (CTSEntities context = new CTSEntities())
{
    ObjectQuery<conference_data> oqConferenceData = context.conference_data;
    var result = (from p in oqConferenceData
                 select p.Times).Distinct().OrderBy(o=>o);    
}

其對應TSQL的OrderBy敘述就可以正常被產生,TSQL敘述如下:

SELECT 
[Distinct1].[Times] AS [Times]
FROM ( SELECT DISTINCT 
    [Extent1].[Times] AS [Times]
    FROM [dbo].[conference_data] AS [Extent1]
)  AS [Distinct1]
ORDER BY [Distinct1].[Times] ASC

參考資料:http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx