Encountering an "undefined alias or unknown mapping" exception when using NHibernate's Multi Query feature

Question

I am trying to implement a NHibernate's Multi Query feature for a typical Paging and Counting functionality using NHibernate version v2.0.50727.

I've been following the example given here by Ayende Rahien and my code is a lot similar to it. However, every time it hits the call to return the results as a list, an exception is thrown with the message along the lines of:

"undefined alias or unknown mapping: Shop [from Shop.dbo.Sales s where s.Customer = :customer_id and s.date between :start_date and :end_date order by s.date desc]"

Here is the step in the code where the problem occurs:

        IList queryResults = NHibernateSession.CreateMultiQuery()
            .Add(dataQuery)
            .Add(countQuery)
            .List();

Here is the rest of the code:

        private IList < SalesDetails > GetSalesByCustomer(Customer customer, DateTime start, DateTime end, int pageIndex, int pageSize, out int TotalCount)
     {
        IQuery dataQuery = NHibernateSession.CreateQuery("from Shop.dbo.Sales s where s.Customer = :customer_id and s.date between :start_date and :end_date order by s.date desc");
        dataQuery.SetInt32("customer_id", customer.ID);
        dataQuery.SetParameter("start_date", start);
        dataQuery.SetParameter("end_date", end);
        dataQuery.SetFirstResult(pageIndex);
        dataQuery.SetMaxResults(pageSize);

        IQuery countQuery = NHibernateSession.CreateQuery("select count(*) from Shop.dbo.Sales s where s.Customer = :customer_id and s.date between :start_date and :end_date");
        countQuery.SetInt32("customer_id", customer.ID);
        countQuery.SetParameter("start_date", start);
        countQuery.SetParameter("end_date", end);

        IList queryResults = NHibernateSession.CreateMultiQuery()
            .Add(dataQuery)
            .Add(countQuery)
            .List();

        IList < SalesDetails > results = (IList < SalesDetails > ) queryResults[0];
        TotalCount = (int)((IList) queryResults[1])[0];
        return results;
    }

Do you need to provide a mapping for this to work? I didn’t think that was the case because it is being returned to a generic list, but if this was so, how would you even go about doing this in Hibernate's Mapping File, ie, hbm.xml, file?

Answer 1

You, and Ayende in the link you gave, are creating queries in HQL using the CreateQuery method. This type of NH query must be written against NH entities, which means you must have a proper mapping, i.e. a Sales entity, and then write select count(*) from Sale as Ayende selects from Item.

If you want to use SQL instead of using CreateQuery, you should use the CreateSQLQuery method. You could review chapters 14-17 of the manual. Too bad you are using the old version, or I'd advise you to make linq your query method of choice.

In short - replace

NHibernateSession.CreateQuery("select ..

with

NHibernateSession.CreateSQLQuery("select ..

or write a proper HQL query using your mapped entity name instead of the SQL table based identifiers.