圖1為Count結果,用了35秒,哇塞!
圖2為Where(條件).Count()結果,同樣的數據只用了4秒鐘,差了10倍!
然後為了取值方面我還是加入三元運算,ContentStatus=Product_Maintain.Where(C => C.CompanyID == company.ID && C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4)).Count()>0?"產品有更新":""。結果如下:
真的是Count()與 Where()區別,不可能這麼大差距吧?於是我單寫
Product_Maintain.Where(C => C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4)).Count() 與
Product_Maintain.Count(C => C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4))
發現速度差不多,生成的代碼是一樣的。
代碼如下:
SELECT COUNT(*) AS [value]
FROM [GasSNS_Company_Equipment_Maintain] AS [t0]
WHERE ([t0].[IsDeleted] = @p0) AND (([t0].[AuditStatus] = @p1) OR ([t0].[AuditStatus] = @p2))
原來是我如果在Select中取某表的數量並且條件中使用了之前from後的某個變量時,這時用Count(條件)和Where(條件).Count()產生代碼才會不同,查詢速度才會出現數量級的差別。
代碼
代碼如下:
//效率低版本:
from company in Company
select new
{
contacter = v.ContacterID,
count = Product_Maintain.Count(C => C.CompanyID == company.ID &&C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4))
}
與
代碼如下:
//效率高版本:
from company in Company
select new
{
contacter = v.ContacterID,
count = Product_Maintain.Where(C =>C.CompanyID == company.ID && C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4)).Count()
}
否則,Count()與Where().Count()生成的SQL語句是相同的,效率也一樣。
總結到此,望各位看官以後要注意!本人入園兩年來,第一發在首頁,請各位看官不吝賜教!
謝謝各位看官的指點,聲明下以上查詢圖都LinqPad查詢結果截圖。至於為啥4秒左右為LinqPad查詢時間,Linq生成Sql語句在Sql Server中執行不到1秒,以下截圖作解釋: