[BUG] Using .Limit() fails due to subquery missing a column name

ServiceStack.OrmLite
1

I’m getting a SQL Server error while trying to execute a query that has a Limit() function applied to it.

The SqlExpression get written as:

SELECT * FROM (
SELECT "LRAPAZIENTI"."IDAPAZIENTE" AS "APazienteId", 
       "LRAPAZIENTI"."COGNOME" AS "APazienteCognome", 
       "LRAPAZIENTI"."NOME" AS "APazienteNome",
       "LRAPAZIENTI"."SESSO" AS "APazienteSesso", 
       "LRAPAZIENTI"."DATADINASCITA" AS "APazienteDataDiNascita", 
       "LRAPAZIENTI"."PID" AS "APazientePID", 
       "LRAPAZIENTI"."CODICEFISCALE" AS "APazienteCodiceFiscale", 
       "LRAPAZIENTI"."TESSERASANITARIA" AS "APazienteTesseraSanitaria", 
       "LRARICHIESTE"."IDARICHIESTA" AS "ARichiestaId", 
       "LRARICHIESTE"."NUMERORICOVERO" AS "ARichiestaNumeroRicovero", 
       "LRARICHIESTE"."NUMERORICHIESTA" AS "ARichiestaNumeroRichiesta", 
       "LRARICHIESTE"."DREPARTOID" AS "ARichiestaRepartoId", 
       "LRARICHIESTE"."DATAORAACCETTAZIONE" AS "ARichiestaDataOraAccettazione", 
       "LRARICHIESTE"."DATAORAPRELIEVO" AS "ARichiestaDataOraPrelievo", 
       "LRARICHIESTE"."STATO" AS "ARichiestaStato", 
       "LRARICHIESTE"."DLABORATORIORICHIEDENTEID" AS "ARichiestaLaboratorioRichiedenteId", 
       "LRACONTENITORI"."IDACONTENITORE" AS "AContenitoreId", 
       "LRACONTENITORI"."BARCODE" AS "AContenitoreBarcode", 
       "LRACONTENITORI"."DCONTENITOREID" AS "DContenitoreId", 
       COALESCE("LRACONTENITORI"."DATAORAPRELIEVO",@0), -- No Column Name!!!
       "LRACONTENITORI"."DATAORAPRIMOCHECKIN" AS "AContenitoreDataOraPrimoCheckin", 
       "LRACONTENITORI"."STATO" AS "AContenitoreStato", 
       "LRDCONTENITORI"."EMATOLOGIA" AS "DContenitoreEmatologia", 
       ROW_NUMBER() OVER (ORDER BY "LRAPAZIENTI"."IDAPAZIENTE") As RowNum  
FROM "LRAPAZIENTI" INNER JOIN "LRARICHIESTE" ON ("LRAPAZIENTI"."IDAPAZIENTE" = "LRARICHIESTE"."APAZIENTEID") 
INNER JOIN "LRACONTENITORI" ON ("LRARICHIESTE"."IDARICHIESTA" = "LRACONTENITORI"."ARICHIESTAID") 
INNER JOIN "LRDCONTENITORI" ON ("LRACONTENITORI"."DCONTENITOREID" = "LRDCONTENITORI"."IDDCONTENITORE")
WHERE ("LRAPAZIENTI"."COGNOME" = @1)
) AS RowConstrainedResult WHERE RowNum > 15 AND RowNum <= 30

The error is due to the COALESCEd column not having a name.
The actual error is: Msg 8155, Level 16, State 2, Line 30 Per la colonna 20 di 'RowConstrainedResult' non è specificato alcun nome di colonna.

2

There are limitations with the base implementation of SQL Server before 2012 which needs to rely on a windowing function hack to simulate paging. If your SQL Server version is later than 2012+ you should change your OrmLiteConnectionFactory to use the SqlServer2012Dialect.Provider, e.g:

var dbFactory = new OrmLiteConnectionFactory(
    connectionString,  
    SqlServer2012Dialect.Provider);

Which will use SQL Server’s new paging support instead of the windowing function hack.

If you’re using SQL Server 2008 or lower this may be a limitation that can’t be fixed. We’d need a complete stand-alone repro (e.g. on GitHub) that we can build and run locally to repro the issue.