SubSonic 3.0.0.3 | SimpleRepository - OrderByDescending and .First()

Question

this codes works fine on SQL Express 2008:

    return _repository.GetInstructions()
        .Where(x => x.PublishedDate != null)
        .OrderByDescending(x => x.PublishedDate)
        .First();

but when run using SQL Server 2000 , it breaks. here is the error. I'll post the trace if someone wants to see it.

 FailedSystem.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.

Here's the trace:

TestSqlITInstructionRepository.Should_Get_Latest_ITInstruction : FailedSystem.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at SubSonic.DataProviders.DbDataProvider.ExecuteReader(QueryCommand qry) in DbDataProvider.cs: line 137
at SubSonic.Linq.Structure.DbQueryProvider.Execute<T>(QueryCommand`1 query, Object[] paramValues) in C:\Documents and Settings\nobody\My Documents\subsonic-SubSonic-3.0-8a52a0913ac0b8542d5d041262046c497387223f\subsonic-SubSonic-3.0-8a52a0913ac0b8542d5d041262046c497387223f\SubSonic.Core\Linq\Structure\DbQueryProvider.cs: line 280
at lambda_method(ExecutionScope )
at SubSonic.Linq.Structure.DbQueryProvider.Execute(Expression expression) in C:\Documents and Settings\nobody\My Documents\subsonic-SubSonic-3.0-8a52a0913ac0b8542d5d041262046c497387223f\subsonic-SubSonic-3.0-8a52a0913ac0b8542d5d041262046c497387223f\SubSonic.Core\Linq\Structure\DbQueryProvider.cs: line 131
at SubSonic.Linq.Structure.QueryProvider.System.Linq.IQueryProvider.Execute<S>(Expression expression) in C:\Documents and Settings\nobody\My Documents\subsonic-SubSonic-3.0-8a52a0913ac0b8542d5d041262046c497387223f\subsonic-SubSonic-3.0-8a52a0913ac0b8542d5d041262046c497387223f\SubSonic.Core\Linq\Structure\QueryProvider.cs: line 45
at System.Linq.Queryable.First<TSource>(IQueryable`1 source)
at Kiss.Test.Repositories.TestSqlITInstructionRepository.Should_Get_Latest_ITInstruction() in TestSqlITInstructionRepository.cs: line 73

Answer 1

I don't think SubSonic fully supports MS SQL 2000. My own findings indicate that .First() genarates TOP (1)
For MS SQL 2000 it should be TOP 1 with no parenthesis

Answer 2

The SubSonic docs (http://subsonicproject.com/docs/Supported%5FDatabases) says that it supports "SQL Server (200-2008)", so that means it's probably a bug. I think you should probably submit it as an issue to the SubSonic github site.

Answer 3

It's an issue - I posted a resolution here: http://stackoverflow.com/questions/1397933/subsonic3-method-firstordefault-throws-exception-with-sql-server-2000/3466302#3466302