2

How do I retrieve N random entities using Fluent NHibernate?

Desired query:

SELECT TOP 5 * FROM MyTable ORDER BY newid()

I'd like to be able to use the Linq repo's for this, but I'm not sure if the result will be optimal. I am not familiar with HQL.

Petrus Theron
  • 27,855
  • 36
  • 153
  • 287

3 Answers3

2

SQL Server-specific Solution

Where Word is the random entity:

IQuery q = _unitOfWork.CurrentSession
                      .CreateQuery("from Word order by newid()")
                      .SetMaxResults(5);

var randomWords = q.List<Word>();
Petrus Theron
  • 27,855
  • 36
  • 153
  • 287
1

Side note: FluentNHibernate is not for querying, it is for mapping and configuration only. Querying is part of "pure" NHibernate.

You can't do it using LINQ directly. The function newid() is SQL Server-specific, so there's no direct equivalent in NHibernate. What you can do here is to query using native SQL by using ISession's CreateSQLQuery method.

See also here for more "deep into NHibernate" method: How do I select a Random Row using NHibernate's ICriteria API?

Community
  • 1
  • 1
NOtherDev
  • 9,542
  • 2
  • 36
  • 47
0

You can use Take/Skip linq extensions:

var resultSet = session.Query<TestTable>().Skip(5).Take(10);

With PostgreSql DB this expands to the following SQL statement:

select TestTable.Id, TestTable.Field from TestTable
LIMIT :p0 OFFSET :p1;
Jhony
  • 1