Support "Take" for DmlExtensionMethods.Delete

[bdbehrho]

I am trying to implement a batched delete using NHibernate's LinqToSql (version 5.2.0.0)

Code that I would expect to work:

public int DeleteWhere(Expression<Func<TInterface, bool>> expression, int? batchSize) {
	int totalDeleteCount = 0;
	int currentDeleteCount = 0;
	do {
		IQueryable<TInterface> deleteQueryable = Session.Query<TInterface>().Where(expression);
		if(batchSize.HasValue) {
			deleteQueryable = deleteQueryable.Take(batchSize.Value);
		}
		currentDeleteCount = deleteQueryable.Delete();
		totalDeleteCount += currentDeleteCount;
	} while (batchSize.HasValue && currentDeleteCount > 0) ;
	return totalDeleteCount;
}

Instead throws an exception "Sequence contains no elements". Stack Trace:

   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at NHibernate.Linq.IntermediateHqlTree.ExecuteAddTakeClause(HqlExpression toTake)
   at NHibernate.Linq.IntermediateHqlTree.GetTranslation()
   at NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter)
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
   at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
   at NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)
   at NHibernate.Linq.DefaultQueryProvider.ExecuteDml[T](QueryMode queryMode, Expression expression)

It seems to be consistent with how the rest of NHibernate LinqToSql works, ideally a queryable with .Take(X) would translate to something like

DELETE TOP X FakeTable FROM FakeTable WHERE ...

instead of throwing an exception.

[FrankLakmann]

Hello, I would like to bump up this issue. It seems to have fallen through the cracks but it is still reproducable in 5.5.2 and 5.6 preview with a simple session.Query<CityEntity>().Where(x => x.PostalCode.StartsWith("5")).Take(100).Delete()

The problem is in this line var hqlQuery = _root.NodesPreOrder.OfType<HqlQuery>().First(); but I know too little about the internals of HQL to analyse. Being able to delete via IQueryable in chunks is important to avoid MSSQL lock escalations.

So please have a look at it and tell, if this is fixable. Thank you all

Edit: Here is the log NHibernate.Linq|Expression (partially evaluated): value(NHibernate.Linq.NhQueryable`1[Fs.Ikaros.Core.Data.Domain.Entity.CourtDirectoryCityEntity]).Where(x => x.PostalCode.StartsWith("5")).Take(100)

NHibernate.Engine.Query.QueryPlanCache|unable to locate HQL query plan in cache; generating (DELETE .Take[Fs.Ikaros.Core.Data.Domain.Entity.CourtDirectoryCityEntity, Fs.Ikaros.Core.Data, Version=2024.1.2001.0, Culture=neutral, PublicKeyToken=null](.Where[Fs.Ikaros.Core.Data.Domain.Entity.CourtDirectoryCityEntity, Fs.Ikaros.Core.Data, Version=2024.1.2001.0, Culture=neutral, PublicKeyToken=null](NHibernate.Linq.NhQueryable`1[Fs.Ikaros.Core.Data.Domain.Entity.CourtDirectoryCityEntity], Quote((x, ) => (x.PostalCode.StartsWith(p1, ))), ), p2, ))