fetch required property using Criteria API?

Question

I have customer and customerAddress class. CustomerAddress class have country n state object.When I load customer i want CustomerAddress also and in customerAddress i want to load only contryName and countryID other details must be null.

In short SQL Query which i want to genrate by Criteria APT is

SELECT     Customer.Name, Country.CountryName, 
           Country.CountryID AS CountryID,    
           CustomerAddress.LocalAddressLine1
FROM       Customer INNER JOIN CustomerAddress 
           ON Customer.CustomerID = CustomerAddress.CustomerID 
           INNER JOIN Country 
           ON CustomerAddress.CountryID = Country.CountryID

to achive this i did

 ICriteria criteria = session.CreateCriteria(typeof(Customer), "Customer")
              .CreateAlias("Customer.CustomerAddressList", "CustomerAddressList", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
              .CreateCriteria("CustomerAddressList.Country", "Country", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
              .SetProjection(Projections.ProjectionList().Add(Projections.Property("Country.CountryID"))
                                                         .Add(Projections.Property("Country.CountryName")))
                                                         .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(Customer)))
              .Add(Restrictions.Eq("Customer.EstablishmentId", CustomerId));

but this gives me Error. How can do this.

How to get specified columns using Criteria API.?

Edited As per guidence By @Firo

i moved .Add(Restrictions.Eq("Customer.EstablishmentId", CustomerId)) before SetProjection so my code is now

ICriteria criteria = session.CreateCriteria(typeof(Customer), "Customer")
                  .CreateAlias("Customer.CustomerAddressList", "CustomerAddressList", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
                  .CreateCriteria("CustomerAddressList.Country", "Country", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
                  .Add(Restrictions.Eq("Customer.EstablishmentId", CustomerId))                  
                        .SetProjection(Projections.ProjectionList().Add(Projections.Property("Country.CountryID"))
                                                         .Add(Projections.Property("Country.CountryName")))
                                                        .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(Customer)));

customer = criteria.UniqueResult<Customer>();

This will execute successfully no error will occure but when i look for customer object all its property is null.

move `.Add(Restrictions.Eq("Customer.EstablishmentId", CustomerId))` befor the setprojection — Firo, Oct 26 '12 at 11:17
@Firo i did the chnages and no error is occure but it gives me customer object with all its property "null". — Amogh, Oct 26 '12 at 12:24

score 2 · Answer 1 · answered Oct 26 '12 at 12:37

for AliasToBean to work correctly you need to explicitly specify the alias

.SetProjection(Projections.ProjectionList()
    .Add(Projections.Property("Country.CountryID"), "CountryID")
    .Add(Projections.Property("Country.CountryName"), "CountryName"))
    .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(Country)));

score 0 · Answer 2 · edited May 23 '17 at 12:04

Although you can supposedly do this using a Transformer, it may not be worth doing. For one thing it may be clearer to construct the instances yourself directly in the method. If you specify a ProjectionsList with multiple Projections on your Criteria instance then Hibernate will bring back a result list of Object[] So ...

    List<Object[]> results = crit.SetProjection(Projections.ProjectionList()
        .Add(Projections.Property("Country.CountryID"), "CountryID")
        .Add(Projections.Property("Country.CountryName"), "CountryName")).list();

    List<Customer> customers = new ArrayList<Customer>();
    for ( Object[] row: results ) {
         Customer c = new Customer();
         c.setCountryId((Long) row[0]);
         // etc. for other properties
         customers.add(c);
    }

See also AliasToBeanResultTransformer(MyDTO.class) fails to instantiate MyDTO

fetch required property using Criteria API?

2 Answers2