NHibernate QueryByExample criteria stuck in SQL in the middle

Question

I use criteria to speed up the request, and I'm almost there.

Using a query In the example, to match rows in a table, delete duplicate rows with the same identifier, and then click paginate.

Of course, I cannot break pages until I delete duplicate lines, and I do not know how to do this. This can be done in SQL, but when it really fits into the free code, ISQLQuery does not return an ICriteria object.

    public IList<EntitySearch> CriteriaSearch(EntitySearch exampleEntitySearch, int startingPage, int pageSize)
    {
        var startRow = startingPage * pageSize;

        // Query By Example.
        var example = Example.Create(exampleEntitySearch)
            .IgnoreCase()
            .EnableLike(MatchMode.Anywhere)
            .ExcludeZeroes();

        var results = this.Session.CreateCriteria(typeof(EntitySearch))
                                .Add(example)
        // select * from (SELECT ROW_NUMBER()OVER (partition by Id order by Id) As rankOrder, * FROM EntitySearch) as original where original.rankOrder = 1
                                .SetFirstResult(startRow)
                                .SetMaxResults(pageSize)
                                .List<DealSearch>();

        return results;
    }

The tip I read is to write an SQL query in NHibernate, but I can’t imagine how to convert the excellent ROW_NUMBER () over the SQL section. "I would like to run it to the end first and then make it more elegant.

90%.

Answer 1

, LINQ:

:

.List<DealSearch>();

To:

.List<DealSearch>().Distinct().ToList();