ROW_NUMBER() 和 nhibernate - 查找项目的页面 [英] ROW_NUMBER() and nhibernate - finding an item's page

问题描述

给定一个 ICriteria 对象形式的查询，我想使用 NHibernate(通过投影?)来查找元素的顺序，相当于使用

given a query in the form of an ICriteria object, I would like to use NHibernate (by means of a projection?) to find an element's order, in a manner equivalent to using

SELECT ROW_NUMBER() OVER (...)

在查询中查找特定项目的索引.(我需要这个用于分页中的跳转到页面"功能)有什么建议吗?

to find a specific item's index in the query. (I need this for a "jump to page" functionality in paging) any suggestions?

注意:我还不想转到有编号的页面 - 我知道该怎么做 - 我想获取项目的 INDEX，以便我可以将其除以页面大小并获取页面索引.

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.

为了避免使用魔法字符串，我从 这个答案.

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 - 查找项目的页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！