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.

但是我不介意有人证明我做错了.

在我的特定设置中，我确实解决了此问题，方法是编写一个方法，该方法需要几个lambda(代表键列，以及一个可选列，用于过滤-特定域的所有属性)实体).然后，该方法将生成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);

正如我所说，这对我有用.我一定会在前进时对其进行调整. YMMV.

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屋！