Linq to SQL error: expression order can only contain volatile scalars
I am using GridView and LinqDataSource to view category table. I have set the Gridview to enable sorting. Sorting usually works, except when I clicked on the Description column header.
"An expression order can only contain non-constant scalars that are matched in order to the server. An expression of type 'NText' is not matched in order."
The description is called using ntext, but can someone explain to me what is going on? why isn't NText sorting when nvarchar?
source to share
SQL Server simply won't let you order by NText fields (see also error 420 in SQL Server Error List ). I assume this is for efficiency reasons, but I couldn't tell for sure.
Now the solution in the linked article has been added to nvarchar
... but this is obviously quite difficult to do in LINQ.
Is your description field required ntext
?
source to share
I clicked on this question a dozen times and finally found the answer for it. @neo answered this here.
My SQL example before:
SELECT *
FROM [tblRoom]
WHERE [Building] = <%= bldgdbid %>
AND [Floor] LIKE CAST('<%= flr %>' AS NVARCHAR(127))
ORDER BY CAST([RoomName] AS NVARCHAR(255))
which I turned into this LINQ query:
(From zz In tblRooms
Where zz.Building = bldgdbid
Select zz
).Where(Function(x) Convert.ToString(x.Floor).ToLower() = flr.ToLower()
).OrderBy(Function(y) Convert.ToString(y.RoomName))
which LINQPad generates:
SELECT [t0].[DBID], [t0].[Building], [t0].[ID], [t0].[Floor], [t0].[RoomName]
WHERE (LOWER(CONVERT(NVarChar(MAX),[t0].[Floor])) = @p0) AND ([t0].[Building] = @p1)
ORDER BY CONVERT(NVarChar(MAX),[t0].[RoomName])
so it uses CONVERT
not CAST
, but that's good enough for me! And that should be enough for you, because CAST
it's ANSI, and it's CONVERT
SQL Server specific, but more powerful.
It looks sloppy, but I am switching to Entity Framework and all LINQ is simpler.
source to share