今天为了用linq实现 select top 1 * from _table order by newid() 这样一个sql下的常用的方法,折腾了大半天!
网上提供了几种解决办法,最经典的是:
1//在自动生成的mapping code中添加
2[Function(Name = "NEWID", IsComposable = true)]
3public Guid NEWID()
4{
5 return ((Guid)(this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))).ReturnValue));
6}
7
8//在使用中
9var customer = (from c in ctx.Customers orderby ctx.NEWID()).First();
参考:http://www.cnblogs.com/Mirricle/archive/2007/08/16/858260.html
可惜它是linq to sql,我没办法在.edmx下找到通用的做法,而且据说一旦更新了model,整个修改又被覆盖。我总不可能每次修改就复制一次吧?
第二种方式是:
(from p in db.Products).OrderBy(e => Guid.NewGuid()).Take(10);
但是在linq to entity 的时候就报错!linq to entities 没有啥Guid.NewGuid()这类的鸟方法。又行不通。
参考:http://www.cnblogs.com/hanxianlong/archive/2007/11/06/951462.html
第三种方式是在鸟语论坛上找到的:
You can do it with ESql
model.CreateQuery<Products>( |
@"Select VALUE source.entity |
From (Select entity, SqlServer.NewID() as rand |
From Products as entity) as source |
Order by source.rand"); |
on this code you can use Take(20) to get a list of random items, or you can just add "Top 20" to the select statement
我照着敲,运行的时候报错,还是行不通。
第四种方式是:
First, create the following view:
CREATE VIEW RandomView
AS
SELECT NEWID() As ID
Then create the following function that uses the view:
CREATE FUNCTION GetNewId
(
)
RETURNS uniqueidentifier
AS
BEGIN
RETURN (SELECT ID FROM RandomView)
END
The view is required because it's not possible to directly use NEWID in a scalar function.
You can then map the GetNewId user-defined function using LINQ to SQL's Function attribute. Again, see chapter 8 for the details.
That's it! You can now write LINQ queries as usual. Here is an example to pick a random object:
var tool = db.Tools.OrderBy(t => db.GetNewId()).First()
Here is another example that uses GetNewId to sort results randomly:
var tools =
from tool in db.Tools
orderby db.GetNewId()
select tool.Name;
参考:http://crazythief.com/KB/496509ca-6231-473b-b9c8-6bd87fc607ec
依然照着做了遍,然后屁颠屁颠的更新模型,然后去找,就是找不到GetNewId()这个鸟方法。
本来以为是无望了,结果回来问了下rei,他说要写个IComparer,但是我这个菜鸟根本不知道怎么写,只要问有没有简单点的?我就问视图怎么样?结果他告诉我可以用第五种方法。
第五种方法:
添加视图- create view MyView as select t.*, newid() as r from MyTable t order by r;
调用方法- var query = from q in context.MyView select q;
执行转换- xxxxxxxx.
ok,困扰了一天的事情总算解决了,继续将我的毕业设计进行下去。。。