Need help optimizing a NHibernate criteria query that uses Restrictions.In(..)

Question

I'm trying to figure out if there's a way I can do the following strictly using Criteria and DetachedCriteria via a subquery or some other way that is more optimal. NameGuidDto is nothing more than a lightweight object that has string and Guid properties.

public IList<NameGuidDto> GetByManager(Employee manager)
{
    // First, grab all of the Customers where the employee is a backup manager.
    // Access customers that are primarily managed via manager.ManagedCustomers.
    // I need this list to pass to Restrictions.In(..) below, but can I do it better?
    Guid[] customerIds = new Guid[manager.BackedCustomers.Count];

    int count = 0;
    foreach (Customer customer in manager.BackedCustomers)
    {
        customerIds[count++] = customer.Id;
    }

    ICriteria criteria = Session.CreateCriteria(typeof(Customer))
                                .Add(Restrictions.Disjunction()
                                                 .Add(Restrictions.Eq("Manager", manager))
                                                 .Add(Restrictions.In("Id", customerIds)))
                                .SetProjection(Projections.ProjectionList()
                                                          .Add(Projections.Property("Name"), "Name")
                                                          .Add(Projections.Property("Id"), "Guid"))

    // Transform results to NameGuidDto
    criteria.SetResultTransformer(Transformers.AliasToBean(typeof(NameGuidDto)));

    return criteria.List<NameGuidDto>();
}

@dotjoe Employee (backup managers) * -> * Customer. Employee (primary managers) 1 -> * Customer. Therefore, you have IList manager.ManagedCustomers and IList manager.BackedCustomers. You also have Employee customer.PrimaryManager and IList customer.BackupManagers. — Chris F, May 10 '10 at 14:06
oh then you should join on the BackupManagers. I'll post an example. — dotjoe, May 10 '10 at 14:27

score 1 · Accepted Answer · answered May 10 '10 at 14:35

return Session.CreateCriteria<Customer>()
    .CreateAlias("BackupManagers", "bm", LeftOuterJoin)
    .Add(Restrictions.Disjunction()
        .Add(Restrictions.Eq("Manager", manager))
        .Add(Restrictions.Eq("bm.Id", manager.Id)))
    .SetProjection(Projections.Distinct(Projections.ProjectionList()
        .Add(Projections.Property("Name"), "Name")
        .Add(Projections.Property("Id"), "Guid")))
    .SetResultTransformer(Transformers.AliasToBean(typeof(NameGuidDto)))
    .List<NameGuidDto>();

I threw a distinct in there because I'm not sure if it is possible to have backup and primary be the same person.

Works like a charm. I wasn't aware of the ability for an alias to have a SQL join type associated with it. Thanks a lot for this. — Chris F, May 11 '10 at 16:55

Need help optimizing a NHibernate criteria query that uses Restrictions.In(..)

1 Answers1