Why does this Nhibernate query return an empty list?

Question

Why wouldn't this query work it always returns a empty list (Mapping is ok and i m sure the database has the data !) any ideas ?

var ids = //IList<int> of ids 


var result = _session.CreateCriteria(typeof (User))
            .Add(Restrictions.InG("Id", ids))
            .CreateCriteria("Posts")
            .AddOrder(Order.Asc("CreatedOn"))
            .SetMaxResults(20)
           .List<Post>();

Answer 1

Try using something like this instead:

List<User> users = _session.CreateQuery("FROM User ORDER BY CreatedOn").List<User>().Where(u => ids.Contains(u.Id));

var posts = new List<Post>();
foreach(User user in users) {
    posts.AddRange(user.Posts);
}

I guess that would depend on your User already having a Posts collection, but most hibernate implementations should have something like that in there.

You could also compact it a bit like this:

List<Post> posts = _session.CreateQuery("FROM Post ORDER BY CreatedOn").List<Post>().Where(p => ids.Contains(p.User.Id));

OR a third option:

List<Post> posts = _session.CreateQuery("FROM Post ORDER BY CreatedOn WHERE User.Id IN (" + ids.ToArray().Join(",") + ")").List<Post>();

Answer 2

Well i just used HQL instead of the criteria api :

var result = _session.CreateQuery("from Post p order by p.CreatedOn where p.PostedBy.Id IN (" + sb + ")").List<Post>();

everything works just fine :)