Hi, for 1. and 2. the situation will be easier in next version of SQL Server but it's clear that it will take a remarkable amount of time until it is available and really used in a big portion of the projects so using a tokenizer that leads to better recognition of the SQL string including support for the different SQL comment formats would be a good thing.
And I agree with your third point that there could be - despite the workarounds with explicit application specific paging - some *transparent* support for paging in the loaders for situations where it cannot be expressed in the SQL string. NHib could read only the keys for the skipped items (maybe create proxies but do not create the full entity instances) to know where to effectively start and prepare the final result for the requested page. But on the other hand that could be dangerous if the system falls back to "client side" paging in situations where that is not intended. Anyway I'd say that can be good thing. On 16 Dez., 10:54, Gerke Geurts <[email protected]> wrote: > Hello all, > > There are a number of issues in the way the SQL Server dialects try to > apply limits to SQL queries. For any query that does not start with a > SELECT a NotSupportedException is thrown, which prevents the following > queries from being executed with limits: > > 1. Queries starting with WITH to define a common table expression; > 2. Queries starting with comments; > 3. Queries that use stored procedures to retrieve results. > > For scenario's 1 and 2 the SQL server dialects can be modified to become > smarter in their query analysis, maybe assisted by returning more > information from the SqlTokenizer that already is used by these dialects. > However scenario 3 requires modification of the Loader and/or CustomLoader > classes, as in this case the limits will have to be applied client-side by > NHibernate. > > The simplest solution I can currently think of is to allow > Dialect.GetLimitString() to return a null value to indicate that a limit > string cannot be generated, in which case the Loader should enforce the > application of limist by NHibernate itself. Any other ideas? > > Regards, > Gerke.
