NH: 2.1.2.4
mapping:
<class name="Export">
<id column="Id" type="guid">
<generator class="guid.comb" />
</id>
<property name="BatchNumber" not-null="true"
index="Export_BatchNumber_IN" />
<property name="ShippingNumber" not-null="true"
index="Export_Shipping_IN" />
<property name="Company" not-null="true" />
<property name="ItemNumber" not-null="true" />
</class>
<session-factory>
<property
name="connection.driver_class">NHibernate.Driver.SqlClientDriver</
property>
<property name="connection.connection_string_name">default</property>
<property name="dialect">NHibernate.Dialect.MsSql2000Dialect</
property>
<property name="query.substitutions">true 1, false 0, yes 'Y', no
'N'</property>
<property
name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory,
NHibernate.ByteCode.LinFu</property>
<property name="prepare_sql">true</property>
<property name="adonet.batch_size">100</property>
<property name="use_proxy_validator">false</property>
<property name="current_session_context_class">web</property>
<mapping assembly="The.Domain" />
</session-factory>
queries (a page of data):
//this is what is causing problems. trying to page the sub query
var orderNumbers = DetachedCriteria
.For<Export>()
.AddOrder(Order.Asc("ShippingNumber"))
.SetProjection(Projections.Distinct(Projections.Property("ShippingNumber")))
.SetMaxResults(20)
.SetFirstResult(40); // page 3
var orders = session
.CreateCriteria<Export>()
.Add(Subqueries.PropertyIn("ShippingNumber", orderNumbers))
.AddOrder(Order.Asc("ShippingNumber"))
.Future<Export>()
.ConvertToViewModel;
var totalOrders = session
.CreateCriteria<Export>()
.SetProjection(Projections.CountDistinct("ShippingNumber"))
.FutureValue<int>();
exception:
SQL Server does not support an offset
System.NotSupportedException: SQL Server does not support an offset
at NHibernate.Dialect.MsSql2000Dialect.GetLimitString(SqlString
querySqlString, Int32 offset, Int32 limit)
at NHibernate.Criterion.SubqueryExpression.ToSqlString(ICriteria
criteria, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters)
at
NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetWhereCondition(IDictionary`2
enabledFilters)
at
NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable
persister, CriteriaQueryTranslator translator,
ISessionFactoryImplementor factory, ICriteria criteria, String
rootEntityName, IDictionary`2 enabledFilters)
at
NHibernate.Loader.Criteria.CriteriaLoader..ctor(IOuterJoinLoadable
persister, ISessionFactoryImplementor factory, CriteriaImpl
rootCriteria, String rootEntityName, IDictionary`2 enabledFilters)
at NHibernate.Impl.MultiCriteriaImpl.CreateCriteriaLoaders()
at NHibernate.Impl.MultiCriteriaImpl.List()
at
NHibernate.Impl.FutureCriteriaBatch.GetResultsFrom(IMultiCriteria
multiApproach)
at NHibernate.Impl.FutureBatch`2.GetResults()
at NHibernate.Impl.FutureBatch`2.get_Results()
at NHibernate.Impl.FutureBatch`2.GetCurrentResult[TResult](Int32
currentIndex)
at
NHibernate.Impl.FutureBatch`2.<>c__DisplayClass4`1.<GetEnumerator>b__3()
at
NHibernate.Impl.DelayedEnumerator`1.<get_Enumerable>d__0.MoveNext()
at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
at MillardExport.Core.Controllers.HomeController.Archives(Int32
batchesPage, Int32 ordersPage)
right now this is my hack to get it working:
var orderNumbers = session
.CreateCriteria<Export>()
.AddOrder(Order.Asc("ShippingNumber"))
.SetProjection(Projections.Distinct(Projections.Property("ShippingNumber")))
.SetMaxResults(20)
.SetFirstResult(40)
.List<string>();
//HACK: the SqlServer 2K dialect doesn't support sub query paging.
var orders = orderNumbers.Count == 0
? new Export[0]
: session
.CreateCriteria<Export>()
.Add(Restrictions.InG("ShippingNumber", orderNumbers))
.AddOrder(Order.Asc("ShippingNumber"))
.Future<Export>()
.ConvertToViewModel();
var totalOrders = session
.CreateCriteria<Export>()
.SetProjection(Projections.CountDistinct("ShippingNumber"))
.FutureValue<int>();
is there a way to create a paged sub query (overriding the dialect?)
for Sql Server 2000?
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
