使用NHibernate结合IQueryable.Take批量删除时抛出异常的问题咨询

我尝试用以下代码从IQueryable中分块删除记录：

session.Query<CityEntity>().Where(x => x.PostalCode.StartsWith("5")).Take(100).Delete()

但触发了如下异常：

Sequence contains no elements
at System.Linq.ThrowHelper.ThrowNoElementsException()
at System.Linq.Enumerable.First[TSource](IEnumerable1 source) at NHibernate.Linq.IntermediateHqlTree.ExecuteAddTakeClause(HqlExpression toTake) at NHibernate.Linq.IntermediateHqlTree.AddPendingHqlClausesToRoot() at NHibernate.Linq.IntermediateHqlTree.GetTranslation() at NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root, Nullable1 rootReturnType)
at NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter)
at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary2 filters, ISessionFactoryImplementor factory) at NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary2 enabledFilters, ISessionFactoryImplementor factory)
at NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary2 enabledFilters, ISessionFactoryImplementor factory) at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary2 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)
at NHibernate.Linq.DmlExtensionMethods.Delete[TSource](IQueryable`1 source)

不使用Take直接删除全部记录可正常执行，但为避免SQL Server锁升级，我必须分块删除。此前相关资料显示该操作应可行，我测试了NHibernate 5.6.0-dev.4903和5.5.2版本，日志信息如下：

NHibernate.Linq|Expression (partially evaluated): value(NHibernate.Linq.NhQueryable1[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.NhQueryable1[Fs.Ikaros.Core.Data.Domain.Entity.CourtDirectoryCityEntity], Quote((x, ) => (x.PostalCode.StartsWith(p1, ))), ), p2, ))

另外发现NHibernate有一个2019年的已知未处理问题，请问这是NHibernate的Bug还是我的操作有误？求解决指引。

结论 #

这是NHibernate的已知Bug，并非你的操作错误。该Bug表现为当对带有Take的IQueryable执行批量Delete时，NHibernate的LINQ解析器无法正确处理Take子句，导致内部调用First()时触发"Sequence contains no elements"异常，且该问题自2019年记录后至今未修复。

解决指引 #

1. 改用HQL批量删除
   直接编写HQL绕开LINQ解析的问题，SQL Server环境下示例：

   string hql = "DELETE FROM CityEntity c WHERE c.PostalCode LIKE '5%' AND c.Id IN (SELECT TOP 100 Id FROM CityEntity WHERE PostalCode LIKE '5%')";
   session.CreateQuery(hql).ExecuteUpdate();
   

2. 先查询ID再删除
   先获取待删记录的ID列表，再批量删除对应记录：

   var idsToDelete = session.Query<CityEntity>()
                            .Where(x => x.PostalCode.StartsWith("5"))
                            .Take(100)
                            .Select(x => x.Id)
                            .ToList();
   if (idsToDelete.Any())
   {
       session.Query<CityEntity>()
              .Where(x => idsToDelete.Contains(x.Id))
              .Delete();
   }
   

3. 循环分块直到删除完毕
   结合上述方法循环执行，确保所有符合条件的记录都被删除：

   int deleted;
   do
   {
       var idsToDelete = session.Query<CityEntity>()
                                .Where(x => x.PostalCode.StartsWith("5"))
                                .Take(100)
                                .Select(x => x.Id)
                                .ToList();
       if (!idsToDelete.Any()) break;
       deleted = session.Query<CityEntity>()
                        .Where(x => idsToDelete.Contains(x.Id))
                        .Delete();
       session.Flush();
       session.Clear();
   } while (deleted > 0);
   

内容的提问来源于stack exchange，提问作者Gigiwig