ROW_NUMBER() 和 nhibernate - 查找项目的页面

NOTE: I don't want to go to a page given it's number yet - I know how to do that - I want to get the item's INDEX so I can divide it by page size and get the page index.推荐答案在查看 NHibernate 的源代码后，我相当确定不存在这样的功能.After looking at the sources for NHibernate, I'm fairly sure that there exists no such functionality.不过，我不介意有人证明我是错的.在我的特定设置中，我确实通过编写一个方法解决了这个问题，该方法采用了几个 lambdas(代表关键列，以及一个可选列来过滤 - 特定域的所有属性实体).然后此方法构建 sql 并调用 session.CreateSQLQuery(...).UniqueResult(); 我并不是说这是一个通用解决方案.In my specific setting, I did solve this problem by writing a method that takes a couple of lambdas (representing the key column, and an optional column to filter by - all properties of a specific domain entity). This method then builds the sql and calls session.CreateSQLQuery(...).UniqueResult(); I'm not claiming that this is a general purpose solution.为了避免使用魔法字符串，我从 PropertyHelper<T>-the-propertyinfo-of-a-specific-property/491486#491486">这个答案.To avoid the use of magic strings, I borrowed a copy of PropertyHelper<T> from this answer.代码如下:public abstract class RepositoryBase<T> where T : DomainEntityBase{ public long GetIndexOf<TUnique, TWhere>(T entity, Expression<Func<T, TUnique>> uniqueSelector, Expression<Func<T, TWhere>> whereSelector, TWhere whereValue) where TWhere : DomainEntityBase { if (entity == null || entity.Id == Guid.Empty) { return -1; } var entityType = typeof(T).Name; var keyField = PropertyHelper<T>.GetProperty(uniqueSelector).Name; var keyValue = uniqueSelector.Compile()(entity); var innerWhere = string.Empty; if (whereSelector != null) { // Builds a column name that adheres to our naming conventions! var filterField = PropertyHelper<T>.GetProperty(whereSelector).Name + "Id"; if (whereValue == null) { innerWhere = string.Format(" where [{0}] is null", filterField); } else { innerWhere = string.Format(" where [{0}] = :filterValue", filterField); } } var innerQuery = string.Format("(select [{0}], row_number() over (order by {0}) as RowNum from [{1}]{2}) X", keyField, entityType, innerWhere); var outerQuery = string.Format("select RowNum from {0} where {1} = :keyValue", innerQuery, keyField); var query = _session .CreateSQLQuery(outerQuery) .SetParameter("keyValue", keyValue); if (whereValue != null) { query = query.SetParameter("filterValue", whereValue.Id); } var sqlRowNumber = query.UniqueResult<long>(); // The row_number() function is one-based. Our index should be zero-based. sqlRowNumber -= 1; return sqlRowNumber; } public long GetIndexOf<TUnique>(T entity, Expression<Func<T, TUnique>> uniqueSelector) { return GetIndexOf(entity, uniqueSelector, null, (DomainEntityBase)null); } public long GetIndexOf<TUnique, TWhere>(T entity, Expression<Func<T, TUnique>> uniqueSelector, Expression<Func<T, TWhere>> whereSelector) where TWhere : DomainEntityBase { return GetIndexOf(entity, uniqueSelector, whereSelector, whereSelector.Compile()(entity)); }}public abstract class DomainEntityBase{ public virtual Guid Id { get; protected set; }}你像这样使用它:...public class Book : DomainEntityBase{ public virtual string Title { get; set; } public virtual Category Category { get; set; } ...}public class Category : DomainEntityBase { ... }public class BookRepository : RepositoryBase<Book> { ... }...var repository = new BookRepository();var book = ... a persisted book ...// Get the index of the book, sorted by title.var index = repository.GetIndexOf(book, b => b.Title);// Get the index of the book, sorted by title and filtered by that book's category.var indexInCategory = repository.GetIndexOf(book, b => b.Title, b => b.Category);正如我所说，这对我有用.随着我的前进，我肯定会调整它.天啊.As I said, this works for me. I'll definitely tweak it as I move forward. YMMV.现在，如果 OP 自己解决了这个问题，那么我很乐意看到他的解决方案！:-)Now, if the OP has solved this himself, then I would love to see his solution! :-) 这篇关于ROW_NUMBER() 和 nhibernate - 查找项目的页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！