Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Issues

Go to all issues
Select view

Select search mode

 
34 of 34

Linq OrderBy NewID()

Fixed

Description

In order to select random rows people usually order by NEWID(). Doing this in Linq generates invalid SQL

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.

Environment

None

depends on

Details

Assignee

Reporter

Labels

Components

Fix versions

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created January 30, 2013 at 7:29 AM
Updated September 19, 2017 at 11:14 PM
Resolved May 7, 2017 at 12:54 PM
Configure

Activity

Show:

Frédéric DelaporteMay 7, 2017 at 1:00 PM
Edited

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.

aims at more explicitly marking an extension as not evaluatable in memory, in order to:

  1. Avoid any throwing.

  2. 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 PM
Edited

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 AM
Edited

This PR for 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 PM
Edited

This looked amazing and promising..

But running the test shows the same behavior - precalculated Guid is still sent as @p0

Who's Looking?
Loading...