How do I append Skip and Take to an nHibernate IQueryOver

Question

I want to do this:

NHibernate.IQueryOver<DataAccess.Domain.Product, DataAccess.Domain.Product> query = session.QueryOver<DataAccess.Domain.Product>();
query = query.Where(x => x.Name == "X");
query = query.Take(1).Skip(3);
List<Product> results = query.List().ToList();

I cant find any help on Skip or Take. The tooltip help (yes I'm that desperate) says that Skip and Take return IQueryOver but the error message says something to the effect "Cant implicitly convert IQueryOver{T} to IQueryOver{T,T}. I don't know what IQueryOver{T,T} is. I didn't ask for one of those anyway.

Downcast

Answer 1

Try to change your code like this:

NHibernate.IQueryOver<DataAccess.Domain.Product> query = session.QueryOver<DataAccess.Domain.Product>();
query = query.Where(x => x.Name == "X");
query = query.Take(1).Skip(3);
var results = query.List();

Or, even better:

var results = session.QueryOver<DataAccess.Domain.Product>()
       .Where(x => x.Name == "X")
       .Take(1)
       .Skip(3)
       .List();

You can check my code here downloading NHibernateQueryOver.

UPDATE:

I think you're missing something. I would suggest you to read this article which has been really helpful for me.
In the paragraph about Associations they say:

An IQueryOver has two types of interest; the root type (the type of entity that the query returns), and the type of the 'current' entity being queried. For example, the following query uses a join to create a sub-QueryOver (analagous to creating sub-criteria in the ICriteria API):

IQueryOver<Cat,Kitten> catQuery =
    session.QueryOver<Cat>()
        .JoinQueryOver(c => c.Kittens)
            .Where(k => k.Name == "Tiddles");

The JoinQueryOver returns a new instance of the IQueryOver than has its root at the Kittens collection. The default type for restrictions is now Kitten (restricting on the name 'Tiddles' in the above example), while calling .List() will return an IList. The type IQueryOver inherits from IQueryOver.

This is what I do when I want to build multiple filter:

Domain.OrderAddress addressDestination = null;
Domain.Customer customer = null;
Domain.TermsConditionsOfSale termsConditionsOfSale = null;

ICriterion filter1 = Restrictions.Where<Domain.Order>(t => t.Company == "MYCOMPANY");
ICriterion filter2 = Restrictions.Where<Domain.Order>(t => t.WareHouseDelivery == "DEPXX");
ICriterion filter3 = Restrictions.Where<Domain.Order>(t => t.Status == "X");
ICriterion filter4 = Restrictions.Where(() => addressDestination.AddressType == "99");
ICriterion filter5 = Restrictions.Where(() => addressDestination.Province.IsIn(new string[] { "AA", "BB", "CC" }));
ICriterion filter6 = Restrictions.Where(() => termsConditionsOfSale.ReturnedGoodsCode != "01");

var ordersForProvinces = session.QueryOver<Domain.Order>()
    .Inner.JoinAlias(t => t.OrderAddresses, () => addressDestination)
        .Inner.JoinAlias(t => t.Customer, () => customer)
        .Left.JoinAlias(t => t.TermsConditionsOfSale, () => termsConditionsOfSale);

ordersForProvinces
    .Where(filter1)
        .And(filter2)
        .And(filter3)
        .And(filter4)
        .And(filter5)
        .And(filter6);

var Results = ordersForProvinces.Skip(50).Take(20).List();

UPDATE-UPDATE:

NHibernate.IQueryOver<Domain.Person> person = session.QueryOver<Domain.Person>();
var myList = DoSomething(person);

Method:

private static IList<Domain.Person> DoSomething(NHibernate.IQueryOver<Domain.Person> persons)
{
    ICriterion filter1 = Restrictions.Where<Domain.Person>(t => t.CompanyName.IsLike("Customer%"));

    persons.RootCriteria.Add(filter1);
    var x = persons.Skip(1).Take(3).List();
    return (x);
}

Caldron

Thank you for your reply Lefty - but it does not give me an iqueryover{T} object. I don't want the data, I want the query, and I want the query to include skip/take. Thanks again. – Downcast

@user866293: there are 2 examples there and the first one gives you NHibernate.IQueryOver<DataAccess.Domain.Product> – Caldron

Lefty I'm sorry but example 1 dosn't compile. If I remove the second T in IQueryOver{T,T}, the line query = query.where(...) gives an error msg. If I leave the second T the line query = query.Skip(...) does not compile. Am I missing a using statment? I have: using NHibernate; using NHibernate.Criterion; using NHibernate.Linq; – Downcast

@Sam: Make sure you're using one of the latest Nhibernate version. I am using NHibernate 3.1.0.4000 and it should work, unless there's something else. – Caldron

I am using the same version: 3.1.0.4000. Are you able to actually build and run a similar statement? I get the same error on my home/work machine. – Downcast

@Sam: You can download my simple stupid sample I have attached. It compiles, of course. – Caldron

I really appreciate you taking the time to build that. I actually wired it up to my test database and yes it does build and run. The problem occurs when you try build out the query as you have done in example 1. The following code, taken from your example, does not build: NHibernate.IQueryOver<Models.Product> products = session.QueryOver<Models.Product>(); products = products.Where(x => x.Name != "X"); products = products.Skip(0).Take(12); – Downcast

The reason I need to build the query out like that is I have a ton of parameters that may/may not be added, including the skip/take. Note that when you write IQueryable{T,T} products = yada; the where clause builds but the skip/take does not. When you take out the second T, the where clause fails. – Downcast

@Sam: I've updated again my answer. Hope it helps. I think you should dig more into the documentation. – Caldron

Lefty, based on what you posted it looks like the answer to my question is "It can't be done". I think its odd but my background is Linq to SQL where this is standard stuff. Oh well its not the end of the world. If it in fact cannot be done please add that as an answer to this question. – Downcast

@Sam: I still do not understand what "it can't be done". You're using IQueryOver in a wrong way. I've shown you how to use multi-filters with paging. Everything can be done! – Caldron

>>You're using IQueryOver in a wrong way Hunh?? What, specifically, is wrong with building an IqueryOver object as I have done? Where, Skip, and Take are all just parameters to a query. I should be able to add all my parameters than call to ToList() on the query to get the data. Adding skip/take should not change the type of the IQueryOver object. – Downcast

Lefty, look at the code in my question. Build out an IQueryable as shown, including where, skip, take, and pass it as a parameter to a method. Use query = query.Where(...), query = query.Skip(...) just as I have done. In the method, call ToList() on the query. Can you do that? If you cannot than the answer to my question is "Skip/Take cannot be appended to an IQueryOver object. Adding them creates an object of a different type." – Downcast

@Sam: once again, another update, you can do millions of things with nHibernate. It's sooo powerful. Still don't understand what you're trying to do. bye. – Caldron

I'm currently running into the same problem. The query gets executed in full, and the Skip & Take get executed on the full result in memory. How do I force this to be done Server side ? *(I'm using the Linq provider) – Deciliter

The next time someone asks me "but don't ORMS make things simpler?" I'm going to send them to this answer. – Ariew