SubSonic3: Method "FirstOrDefault" throws exception with SQL Server 2000

Question

I am using SubSonic3 with SQL Server 2000.

I have problem with the method "FirstOrDefault" - it always throws an exception = "Line 1: Incorrect syntax near '('." from the SubSonic.Linq dll

EDIT (Added code from comment):

InventoryDAL = DAL project name (dll) 
Inventort= Subsonic3 Gnerated classes 
Name space WHWarehouses = gnerated object 

Dim WareH = (From Wh In InventoryDAL.Inventort.WHWarehouses.All _ 
  Where Wh.WarehouseID = 1 ).FirstOrDefault

Answer 1

Hi, I am getting the same error. The SQL that is generated looks like this:

1. SELECT
2. [Limit1].[C1] AS [C1],
3. [Limit1].[Field1] AS [Field1]
4. FROM ( SELECT TOP (1)
5.    [Extent1].[Field1] AS [Field1]
6.    1 AS [C1]
7.    FROM [Table] AS [Extent1]
8.    WHERE [Extent1].[FieldA] = 'some value'
9. ) AS [Limit1]

And the problem is in line 4: "TOP ( 1 )". SQL Server 2000 does not like the parenthesis. And I don't know if there is a solution... except moving to SQL Server 2005/2008

---

Mickey

Answer 2

I don't know SubSonic, but Hibernate has different dialects of SQL you can tell it to use, might want to see if there is any way to tell it to use a dialect for SQL 2000

Answer 3

This is a problem I've encountered too and unfortunately the code to generate the TOP is generated in the SubSonic Dependency and cannot be changed in the TT templates.

In the SubSonic source under TSqlFormatter.cs class is a method

protected override Expression VisitSelect(SelectExpression select)

...

if (select.Take != null)
            {
                sb.Append("TOP (");
                this.Visit(select.Take);
                sb.Append(") ");
            }

....

Removing the brackets in db.Append should fix the issue for SQL but I'm not sure whether this may break other providers such as SQL Compact Edition?

I raised it here: http://stackoverflow.com/questions/3447372/subsonic-bug-with-top-keyword before I read you query.

Hope this helps.