Linq OrderBy NewID()
Description
Environment
depends on
Activity
Frédéric DelaporteMay 7, 2017 at 1:00 PMEdited
The current state of resolution is: "Any extension throwing exception will not be evaluated in memory but db-side", thus allowing to force db side execution by throwing anything. So in case someone has implemented a buggy in memory evaluation, this may go unnoticed.
https://nhibernate.jira.com/browse/NH-4009#icft=NH-4009 aims at more explicitly marking an extension as not evaluatable in memory, in order to:
Avoid any throwing.
Avoid swallowing what could be a bug.
It will be then required to use that marking on extensions which must be evaluated db side only.
Default behavior has been changed for disabling partial evaluation of LinqExtensionMethod.
It can be re-enabled by using parameters on the attribute.
Frédéric DelaporteMay 7, 2017 at 12:54 PMEdited
Fixed by side effect of fixing NH-3961. A better fix may be added with NH-4009.
Frédéric DelaporteMarch 18, 2017 at 8:17 AMEdited
This PR for https://nhibernate.jira.com/browse/NH-3961#icft=NH-3961 happens to accidentally solve this issue. Some additional work for handling such a feature more explicitly may be added, see its comments.
By the way, sorting by NewID may cause bugs, especially if combined with eager fetching. Read more on that here.
Oskar BerggrenFebruary 3, 2013 at 9:15 AM
Yes, what happens is that Relinq will evaluate the function call and replace it with its return value, as the method invocation does not depend on any value from the database. It's the same basic issue as experienced at: http://stackoverflow.com/questions/14396166/lambda-string-as-varchar/14399760#comment20398068_14399760 You can work around it by having your method take some dummy parameter such as e.g. user.Id which you will just ignore when generating the HQL (need to implement IHqlGeneratorForMethod).
Also see page 141 forward for some other ways of grabbing a random row: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back
Stefan HessFebruary 2, 2013 at 5:54 PMEdited
This looked amazing and promising..
But running the test shows the same behavior - precalculated Guid is still sent as @p0
[Test]
public void OrderByGuidSqlFunctions()
{
var query = (from user in db.Users
orderby SqlFunctions.NewID()
select user).ToList();
Assert.That(query.Count, Is.EqualTo(3));
}
public class SqlFunctions
{
[LinqExtensionMethod("NEWID")]
public static Guid NewID() { return Guid.NewGuid(); }
}Details
Details
Assignee
Reporter
Labels
Components
Fix versions
Affects versions
Priority
Trivial
In order to select random rows people usually order by NEWID(). Doing this in Linq generates invalid SQL
[Test] public void OrderByGuid() { var query = (from user in db.Users orderby Guid.NewGuid() select user).ToList(); Assert.That(query.Count, Is.EqualTo(3)); }Generated SQL:
exec sp_executesql N'select user0_.UserId as UserId40_, user0_.Name as Name40_, user0_.InvalidLoginAttempts as InvalidL3_40_, user0_.RegisteredAt as Register4_40_, user0_.LastLoginDate as LastLogi5_40_, user0_.Enum1 as Enum6_40_, user0_.Enum2 as Enum7_40_, user0_.Features as Features40_, user0_.RoleId as RoleId40_, user0_.Property1 as Property10_40_, user0_.Property2 as Property11_40_, user0_.OtherProperty1 as OtherPr12_40_ from Users user0_ order by @p0 asc',N'@p0 uniqueidentifier',@p0='44381DF7-8A67-4EC9-9EAB-E38F3481A05E'
Error message:
WARN ADOExceptionReporter:0 - System.Data.SqlClient.SqlException (0x80131904): The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
The Guid is always passed in as a constant expression. NEWID() is a non-deterministic function (at least in SQL-Server) and returns a different result for each row.
As a workaround the query could be done via Hql.
I've downloaded the source and tried to get this working in many ways for days but got to the limit of my knowledge.