Flitering an object child collection by a property using Fluent nHibernate

Please excuse my ignorance, I am new to nHibernate and am having a conceptual problem trying to filtering an child collection with an nHibernate query

My object model contains two entities User and Task set up like this

public class User
{
    public User()
    {
        this.Tasks = new List<Task>();
    }

    public User(int id): this()
    {
        this.Id = id;
    }

    public virtual int Id { get; private set; }

    public virtual IList<Task> Tasks { get; set; }
}

public class Task
{
    public Task() { }

    public Task(int id, bool active): this()
    {
        this.Id = id;
        this.Active = active;
    }

    public virtual int Id { get; set; }

    public virtual bool Active { get; set; }
}

My nHibernate mappings for these are as follows

public class UserMap: ClassMap&开发者_开发知识库lt;User>
{
    public UserMap()
    {
        Table("user");
        Id(x => x.Id);
        HasMany(x => x.Tasks);
    }
}

public class TaskMap : ClassMap<Task>
{
    public TaskMap()
    {
        Table("task");
        Id(x => x.Id);
        Map(x => x.Active);
    }
}

My database has two tables 'task' and 'user' which I have filled thusly

SELECT * FROM task;
+----+--------+---------+
| Id | Active | User_id |
+----+--------+---------+
|  1 |      1 |       3 |
|  2 |      1 |       3 |
|  3 |      1 |       3 |
|  4 |      0 |       3 |
|  5 |      0 |       3 |
|  6 |      1 |       1 |
|  7 |      1 |       1 |
|  8 |      1 |       1 |
|  9 |      0 |       1 |
| 10 |      0 |       1 |
+----+--------+---------+
10 rows in set

SELECT * FROM user;
+----+
| Id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set

What I am trying to do is run a query that returns a specific user with only the active tasks in its 'Tasks' collection

var query = QueryOver.Of<User>()
    .Where(u => u.Id == 3)
    .JoinQueryOver<Task>(x => x.Tasks)
    .Where(t => t.Active == true);

var results = dataProvider.ExcecuteQuery<User>(query);

When I execture this query I expect to get back a single user object with 3 Task objects in its Tasks collection, instead what I get is 3 copies of the same task object (task.Id = 3) all of which have all 5 Tasks in their respective collections.

Is what I am trying to do actually possible or should I just be querying on the Task entity instead?

I hope this is not the case as it would be nice to be able to just see a users active tasks without having to messing around with manual filtering.

---

First, I think I would break it up into two queries. In making a master-detail it might make more sense to get the user entities, then get the tasks for the users...

I'm not really good using QueryOver.Of<T>(), but here's one way to do it using session.QueryOver<T>():

        var users = session.QueryOver<User>()
            .Where(u => u.Id == 3)
            .Fetch(o => o.Tasks)
            .Lazy()
            .SingleOrDefault();

        users.Tasks.TakeWhile(o => o.Active);

I think you can use filters too, but it didn't sound like what you wanted to do. I don't think QueryOver.Of<T>() does what you want, because the disconnected query would need a filter to correctly pull back the child elements.

---

You can apply .TransformUsing( Transformers.DistinctRootEntity) before List() method to get only 1 user object. After that what you are seeing is issuing another query to the database to get the list of tasks of that user (without filtering the active ones) and this is the meaning of lazy loading. Add .ShowSql().FormatSql() in your db config section to see what's happening.

Also you can add this property to your Task class

public virtual User User { set; get; }

To have queries like this: (what you want in the first place)

 var list = session.QueryOver<Task>()                                                        
                            .Fetch(t=>t.User).Eager
                            .Where(t => t.Active && t.User.Id==3)
                            .TransformUsing(Transformers.DistinctRootEntity)
                            .List();