星期三, 8月 25, 2010

用Linq to SQL亂數取一筆

之前有一篇文章用Linq to SQL亂數取一筆,今天在寫這個需求的時候,看到有更簡單的寫法,直接看 code 吧!

void Main()
{
var data = Products.OrderBy(a=>Guid.NewGuid()).Take(1);
foreach(var item in data)
{
Console.WriteLine(string.Concat(item.ProductID,"--", item.ProductName));
}
}

這樣就可以很簡單做出亂數取出一筆資料了,我們來看一下生出來的 SQL Statement

SELECT TOP (1)
[Project1].[ProductID] AS [ProductID],
[Project1].[ProductName] AS [ProductName],
[Project1].[SupplierID] AS [SupplierID],
[Project1].[QuantityPerUnit] AS [QuantityPerUnit],
[Project1].[UnitPrice] AS [UnitPrice],
[Project1].[UnitsInStock] AS [UnitsInStock],
[Project1].[UnitsOnOrder] AS [UnitsOnOrder],
[Project1].[ReorderLevel] AS [ReorderLevel],
[Project1].[Discontinued] AS [Discontinued],
[Project1].[CategoryID] AS [CategoryID]
FROM ( SELECT
NEWID() AS [C1],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
) AS [Project1]
ORDER BY [Project1].[C1] ASC

Linq to SQL 幫我們用 NEWID 生出子查詢,然後再用排序的方式來抓出來,讚吧,簡單多了..^^..

PS: 經過一些測試,好像是.net framework 4.0才有效過ㄟ,^^||..喵滴..

參考:
http://msdn.microsoft.com/zh-tw/library/bb386976.aspx

沒有留言: