ICriteria and querying a sub-collection

jambrose · **Joined:** Fri Nov 16, 2007 6:20 pm **Posts:** 15

I have an object Program that has a collection called Managers(many2many) that is a generic list of type User (IList<User> Managers).

I want to create a ICriteria query that returns a list of programs that a user is a manager of. For example:

public IList<Program> GetByManager(User user)
{
// Stuff
}

I've been fiddling with DetachedCriteria, but I haven't been able to get anything close to working. It feels like I need a "sub-query" that:

Selects a distinct Program from Managers where Manager.ID = user.ID

EDIT: That doesn't seem quite right. More
select a distinct program where user in program.Managers

Is this correct thinking and how do I start creating a criteria for this?

I'm using FluentNHibernate to do the mappings and my ProgramMap looks like:

Code:

public ProgramMap()
        {
            WithTable("wsdot_Programs");

            Id(x => x.ID, "programId").WithUnsavedValue(Guid.Empty).GeneratedBy.GuidComb();
            Map(x => x.Title).CanNotBeNull();
            Map(x => x.Code);
            Map(x => x.Description);
            Map(x => x.StartDate);
            Map(x => x.EndDate);
            Map(x => x.URL);
            Map(x => x.ProgramType).CustomTypeIs(typeof (ProgramType));

            References(x => x.Parent, "parentId");
            References(x => x.Style, "styleId");

            HasMany<ProgramGroup>(x => x.Groups).IsInverse().WithKeyColumn("programId").AsBag().LazyLoad();
            HasMany<Program>(x => x.SubPrograms).IsInverse().WithKeyColumn("parentId").AsBag().LazyLoad();
            HasManyToMany<User>(x => x.Users)
                .WithTableName("wsdot_UsersInPrograms")
                .WithParentKeyColumn("programId")
                .WithChildKeyColumn("userId");

            HasManyToMany<User>(x => x.Managers)
                .WithTableName("wsdot_ProgramManagers")
                .WithParentKeyColumn("programID")
                .WithChildKeyColumn("userId")
                .LazyLoad();

        }

Thanks
Jack

jambrose · **Joined:** Fri Nov 16, 2007 6:20 pm **Posts:** 15

Got it working with IQuery:

Code:

IQuery q = Session.CreateQuery("SELECT prog FROM Program prog " +
                "prog.Managers manager WHERE manager.ID = :userID");
            q.SetParameter("userID", user.ID);

How would I convert this into a criteria query?

wolli · **Posted:** Wed Dec 31, 2008 3:12 am

Quote:

SELECT prog FROM Program prog prog.Managers manager WHERE manager.ID = :userID

I suppose there is a "join" missing ?

A criteria for that would be something like that:

Code:

session.CreateCriteria(typeof(Program), "prog")
   .CreateCriteria("prog.Managers", manager)
   .Add( Expression.Eq("manager.ID", userId));