NHibernate Criteria Transform Result

Question

Hi

I have an SecurityGroup entity witch has Memebers and Application properties. Application is a lookup.

So securityGroups is in many-to-many relationship with User table and one-to-many with LookupApplciation (FK)

Now I want to select all application linked to a specific user.

I have follow criteria:

 public IList<LookupApplication> GetApplicationByUser(User user)
    {
        return
            this.Session.CreateCriteria(typeof(SecurityGroup), "sg")
            .CreateAlias("Members", "u")
            .CreateAlias("Application", "al")
            .Add(Restrictions.Eq("u.Id", user.Id))

            .List<LookupApplication>();

    }

It trows an exception

The value "Edi.Advance.Core.Model.Security.SecurityGroup" is not of type "Edi.Advance.Core.Model.Lookups.LookupApplication" and cannot be used in this generic collection.
Parameter name: value

and it is right.

How can I transform the result to IList<LookupApplication>?

Thanks

Answer 1

It depends on how the SecurityGroup looks like and how LookupApplication looks like.

You could use ResultTransformer like:

.SetResultTransformer(Transformers.AliasToBean<LookupApplication>())
.List<LookupApplication>();

Granted that SecurityGroup has properties matchinig LookupAppliaction, or else you have to project those properties like:

.SetProjection(NHibernate.Criterion.Projections.ProjectionList()    
.Add(Projections.Property("Number"), "OrderNumber")
.Add(Projections.Property("CreatedOn"), "CreatedOn")
   .Add(Projections.Property("MemeberName"), "Name"))
 .SetResultTransformer(Transformers.AliasToBean<LookupApplication>())
 .List<LookupApplication>();

Answer 2

You can only return the type which you create the criteria from.

The easiest way starting from the code you have will be:

    return
        this.Session.CreateCriteria(typeof(SecurityGroup), "sg")
        .CreateAlias("Members", "u")
        .CreateAlias("Application", "al")
        .Add(Restrictions.Eq("u.Id", user.Id))
        .List<SecurityGroup>()

        // get the lookup applications in memory
        .SelectMany(x => x.LookupApplications);

This loads all SecurityGroups into memory, even if it only needs the LookupApplications. This might not be an issue when you need them anyway or when they are small.

You could also reverse the query and start from the LookupApplication

    return
        this.Session.CreateCriteria(typeof(LookupApplication), "la")
        // requires navigation path from SecurityGroup to LookupApplication
        .CreateCriteria("la.SecurityGroup", "sg")
        .CreateAlias("Members", "u")
        .CreateAlias("Application", "al")
        .Add(Restrictions.Eq("u.Id", user.Id))
        .List<LookupApplication>()

Or use HQL, which has some features not available in Criteria, items gets all the items from a collection:

select sg.LookupApplications.items
from SecurityGroup sg inner join sg.Members u
where u.Id = :userId

HQL is actually recommended when you don't have dynamic queries.

Update, from isuruceanu's comment:

Session
  .CreateQuery(
      @"select sg.Application 
      from SecurityGroup sg 
          inner join sg.Members u 
      where u.Id = :userId") 
  .SetParameter("userId", user.Id)
  .List<LookupApplication>();