NHibernate CreateCriteria query problem

Question

I hope someone can help with this please.
I am trying to query an OLAP Fact table with NHibernate, but am struggling to get it to work. Its seems a simple requirement but I just cant see what the problem could be.
I have a central Fact table with several Dimension tables, one of the Dimensions has a secondary Dimension.

So ERD is. Fact >---1 Factor_Dim >---1 Target_Dim

My NHibernate query is.

facts = session.CreateCriteria(typeof(Fact), "facts")
.CreateAlias("facts.FactorDimension", "factDim", JoinType.InnerJoin)
.CreateAlias("factDim.TargetDimension", "targetDim",JoinType.InnerJoin)
.Add(Restrictions.Eq("targetDim.TargetID", targetId))
.List();

The error is "The multi-part identifier "targetdim2_.TargetID" could not be bound.". The generated SQL does not have the Factor_DIM or Target_DIM tables in the From clause.

Are there any alternative techniques to get this query to work? Id like to stick to this style as opposed to CreateSQLQuery() if possible.

Please help. Thanks.

Answer 1

Linq or QueryOver will be your cleanest solutions. If you are determined to stay with ICriteria you probably would want to wrap each of your entities with a class with common crud methods, it also makes your code access common, so code corrections are done in one place, not over hundres of files or classes.

Theres plenty of projects at http://nhforge.org/wikis/general/open-source-project-ecosystem.aspx which can help you out. I know NhGen ( http://sourceforge.net/projects/nhgen/ ) creates a CRUD class for each entity based on the NHibernate.Burrows GenericDao class with a few CRUD methods. It takes care of all the aliases and joins so queries become as simple as

IMessageDao messageDao = new MessageDao();
// Get All
IList<IMessage> messageList1 dao.FindAll();
// Find using QueryByExample
IList<IMessage> messageList2 = dao.FindByExample(messageDetails, orderBy)).ToList();
// Find using a simple entity query
IList<IMessage> messageList3 = messageDao.Find( new [] { Restrictions.Le(MessageHelper.Columns.Date, dateLastChecked) } );
// Find using a join and a query on said joined entities
IList<IMessage> messageList4 = messageDao.Find
( new []
    {
        Restrictions.Le(MessageHelper.Columns.Date, dateLastChecked),
        Restrictions.Eq(MessageHelper.Columns.IsActive, true))
    }, new[]
    {
        Restrictions.Eq(CategoryHelper.KeyColumns.Rsn, categoryRsn),
        Restrictions.Eq(CategoryHelper.Columns.IsActive, true))
    }, new []
    {
        Restrictions.Eq(ChannelHelper.KeyColumns.Rsn, channelRsn),
        Restrictions.Eq(ChannelHelper.Columns.IsActive, true))
    }
);

Theres plenty of overrides so you can specify your join type or it naturally assumes inner join.