CODEWITHSUNDEEP
nhibernate
Marian Ban
Marian Ban

Reputation: 8178

Nhibernate queryover order by random

I'm trying to write a query which returns randomly ordered results. I've found this post Linq Orderby random ThreadSafe for use in ASP.NET which gave me some basic clue how to do that. But i'm getting following exception:

variable 'x' of type 'Accomodations.DAL.Model.Generated.Accomodation' referenced from scope '', but it is not defined

Here is my query:

                var query = session.QueryOver<Accomodation>()
                                   .OrderBy(x => (~(x.Id & seed)) & (x.Id | seed)).Asc; // this is the problematic line of code                    

                if (searchParams.District != 0)
                    query = query.Where(x => x.District.Id == searchParams.District);

                if (searchParams.Region != 0)
                    query = query.Where(x => x.Region.Id == searchParams.Region);

                if (searchParams.Location != 0)
                    query = query.Where(x => x.Location.Id == searchParams.Location);

                var futureCount = query.Clone().Select(Projections.RowCount()).FutureValue<int>();

                SearchAccomodationResultItem resultItemAlias = null;

                var futurePage = query                        
                    .SelectList(list => list
                        .Select(x => x.Id).WithAlias(() => resultItemAlias.Id)
                        .Select(x => x.AccomodationType.Id).WithAlias(() => resultItemAlias.AccomodationTypeId)
                        .Select(x => x.Region.Id).WithAlias(() => resultItemAlias.RegionId)
                        .Select(x => x.Name).WithAlias(() => resultItemAlias.Title)
                        .Select(x => x.MaxCapacity).WithAlias(() => resultItemAlias.MaxCapacity)
                        .Select(x => x.MinPrice).WithAlias(() => resultItemAlias.MinPrice)
                        .Select(x => x.MinStayLength).WithAlias(() => resultItemAlias.MinStayLength)
                        .Select(x => x.MainImageName).WithAlias(() => resultItemAlias.ImgSrc)                           
                        )                        
                    .TransformUsing(Transformers.AliasToBean<SearchAccomodationResultItem>())                        
                    .Skip(skip)                        
                    .Take(searchParams.PageSize)
                    .Future<SearchAccomodationResultItem>();

                searchResults = futurePage.ToList();
                numberOfResults = futureCount.Value;                    
            });

Any suggestion will be appreciated. Thanks

Upvotes: 4

Views: 1533

Answers (2)

Martin Ernst
Martin Ernst

Reputation: 5679

To use the above approach with paging, you could store the seed for the user (on a per session basis probably) and then use the RAND(seed) function in SQL - because you use the same seed, it will generate the same sequence of pseudo-random numbers and thus allow paging

Upvotes: 1

Cole W
Cole W

Reputation: 15313

Here is a good example of how to do this. This is a technique that I'm currently using.

http://puredotnetcoder.blogspot.com/2011/09/nhibernate-queryover-and-newid-or-rand.html

Edit

Below is taken from the above article and I've modified it slightly to include Skip as well.

public IList<CmsTestimonial> GetRandomTestimonials(int count, int skip) {  
  return Session  
    .QueryOver<CmsTestimonial>()  
    .OrderByRandom()
    .Take(count)  
    .Skip(skip)
    .List();  
}

Upvotes: 2

Related Questions