Linq causes syntax error with Top (1)

Question

I have an EF context using linq that is being executed on multiple sql servers of varying types. What I have found is that linq is causing a syntax error on a sql server 2000 box because the linq translation for .FirstOrDefault() is being translated into sql that uses SELECT TOP (1) instead of SELECT TOP 1 and it would seem that the parenthesis are causing this syntax error as they are not supported in this context in sql server 2000.

Is it possible to force linq to use SELECT TOP 1 instead of SELECT TOP (1) ?

Note that it doesn't appear that SQL Server 2000 is supported by EF http://stackoverflow.com/questions/1600630/entity-framework-v2-doesnt-support-sql-2000 — Levi Botelho, Nov 16 '12 at 14:34
See: http://stackoverflow.com/questions/452050/which-versions-of-sql-server-does-linq-to-sql-support — Sam Shiles, Nov 16 '12 at 14:34
See also http://msdn.microsoft.com/en-us/library/bb896273.aspx — J. Steen, Nov 16 '12 at 14:35
see http://stackoverflow.com/questions/192203/whats-the-linq-to-sql-equivalent-to-top — mikemurf22, Nov 16 '12 at 14:35

score 3 · Accepted Answer · edited Nov 16 '12 at 15:30

3

you can use ToList().Take(1) method after ordering.

edited Nov 16 '12 at 15:30

J. Steen

15,470
15
56
63

answered Nov 16 '12 at 14:35

IT ppl

2,626
1
39
56

2

That won't translate the query properly, it will just fetch all of the items. Also, if you really did want to just translate the `Take` into linq to objects you could use `AsEnumerable` rather than `ToList` to avoid bringing the entire query into a data structure just so that you could throw it away. – Servy Nov 16 '12 at 14:44
@J.Steen Well, it was approved by `SchmitzIT` first, and rejected by no one, so still a poor reflection on the review system, but yeah... Thanks for mentioning it though; I didn't look at the edit history until now. – Servy Nov 16 '12 at 15:32
@Servy Still, my bad for not going *deep* enough. ;) – J. Steen Nov 16 '12 at 15:33
To completely duplicate `.FirstOrDefault()` without the `FirstOrDefault()` being passed to the query parser, one would want `.Take(1).AsEnumerable.FirstOrDefault()` which may be the general idea the editor was thinking of. That all is assuming that `Take(1)` doesn't have the same issue. – Jon Hanna Nov 16 '12 at 15:33
@JonHanna Well, given that the comments state that the database provider isn't supported by EF, even if this one example could be made to work the OP should probably still stop using it; he won't *always* be able to get it to work and be able to rely on that fact. – Servy Nov 16 '12 at 15:35

Linq causes syntax error with Top (1)

1 Answers1