Using NHibernate 1.2.0
I’m trying to perform a criteria select from multiple tables using Projections so that the data comes back as a simple array of object arrays, not as strongly-typed objects. I’ve managed to get it to work to get the data back the way I want using CreateAlias to refer to the join table(s), but this requires that I put the alias name in my Projections, which is undesirable for several reasons. I would like to be able to specify the projections into the join table without having to use aliases in the join table columns.
My example in this case is a table Person (with basic person information such as name) and a table PersonAddress that has a many-to-one relationship to Person. Person has a Hibernate mapped collection relating it to PersonAddress through the ListOfPersonAddress property.
The scenario that works is:
Code:
ICriteria criteria = BusinessContext.Instance.Dao.GetTypeCriteria(typeof(Person), null);
ProjectionList allProjections = Projections.ProjectionList();
// These two properties come from the Person object
allProjections.Add(Projections.Property("FirstName"));
allProjections.Add(Projections.Property("LastName"));
// Now I join the Address table through Person.ListOfAddress and
// only select the primary address for the person.
ICriteria joinAlias = criteria.CreateAlias("ListOfAddress", "addr");
joinAlias.Add(Expression.Eq("addr.PrimaryAddress", true));
// Now I can select properies on the joined record. Note that I need
// to use the alias prefix "addr." on the properties for it to work.
allProjections.Add(Projections.Property("addr.Address"));
allProjections.Add(Projections.Property("addr.City"));
criteria.SetProjection(allProjections);
The only issue with this is that I have to create the join criteria using an alias and refer to the columns from that other table using that alias. The problem with that is that for various reasons we need people to be able to enter simple SQL to specify the columns they want, and then use Projections.SqlProjection, not simple Property projections, and it would be questionable to require them to put the alias name into the SQL.
I’ve tried using CreateCriteria to create the join, rather than CreateAlias, and then creating a separate ProjectionList and attaching it to the join criteria, since it seems like I should be able to attach projections separately to each of the criteria. However, this doesn’t work and the projections on the second criteria (the join) appear to overwrite the criteria on the base table, and it fails because it tries to look for the Address columns in the Person table:
Code:
ICriteria criteria = BusinessContext.Instance.Dao.GetTypeCriteria(typeof(Person), null);
ProjectionList allProjections = Projections.ProjectionList();
// These two properties come from the Person object
allProjections.Add(Projections.Property("FirstName"));
allProjections.Add(Projections.Property("LastName"));
// Set the projections for the Person table
criteria.SetProjection(allProjections);
// Now I join the Address table through Person.ListOfAddress and
// only select the primary address for the person.
ICriteria joinCriteria = criteria.CreateCriteria("ListOfAddress");
joinCriteria.Add(Expression.Eq("PrimaryAddress", true));
// Now I can try to specify the projections on the Address
// table using a separate projection list, and set these
// projections on the Address criteria, not the Person.
// THIS DOESN’T WORK! It overwrites the projections I set
// for Person, and results in error that Address property
// doesn’t exist on the Person object.
ProjectionList joinProjections = Projections.ProjectionList();
joinProjections.Add(Projections.Property("Address"));
joinProjections.Add(Projections.Property("Cit"));
joinCriteria.SetProjection(joinProjections);
It’s possible that what I’m trying to do simply can’t be done and that I have no choice but to alias projections into join tables, but it really seems like I should be able to set separate projections for each table and attach these projection lists independently to each of the criteria. Any help would be appreciated.
Login
FAQ
Search