NHibernate Criteria QueryByExample stuck with SQL in the middle

Question

I am using Criteria to speed up a query, and I am almost there.

Using Query By Example to match up rows in a table, remove duplicate rows with the same id, and then paginate.

Of course I can't paginate until I remove the duplicate rows, and I don't know how to do that. It can be done in SQL, but then that does fit in with the fluent code, the ISQLQuery doesn't 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;
    }

Advice I've read is to write the SQL query in NHibernate, but I can't think how to convert the nifty "ROW_NUMBER() over partition SQL". I would like to get it working end to end first, then make it more elegant.

I'd like to get this spike into production and prove the ~90% speed up.

Answer 1

I'm not sure about performance, but you could use LINQ:

Change:

.List<DealSearch>();

To:

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