Criteria Help

rcombs · **Joined:** Tue May 26, 2009 5:07 pm **Posts:** 1

I am trying to do the following SQL query:

Code:

SELECT     oi.ProductCode, SUM(oi.OrderQty) AS OrderQty, prod.Description, prod.Price, prod.QtyOnHand, SUM(oi.OrderQty * prod.Price) AS ItemTotal
FROM         OrderItems AS oi INNER JOIN
                      Orders AS ord ON oi.OrderId = ord.OrderId INNER JOIN
                      Products AS prod ON oi.ProductId = prod.Id
WHERE     (ord.OrderDate BETWEEN '01/01/2008' AND '09/01/2009')
GROUP BY oi.ProductCode, prod.Description, prod.Price, prod.QtyOnHand
ORDER BY oi.ProductCode

as a Criteria query, but I haven't been able to get it to run successfully.

As it is now:

Code:

return CreateCriteria("oi")
                .CreateCriteria("oi.Order", "o")
                .CreateCriteria("oi.Product", "p", JoinType.FullJoin)
                .Add(Expression.Conjunction()
                    .Add(Expression.Eq("order.OrderType", "Sale"))
                    .Add(Expression.Between("order.OrderDate", DateTime.Parse(startDate.ToShortDateString()), DateTime.Parse(endDate.ToShortDateString())))
                )
                .SetProjection(Projections.ProjectionList()
                        .Add(Projections.GroupProperty("p.ProductCode"))
                        .Add(Projections.GroupProperty("p.Description")))
                .AddOrder(NHibernate.Criterion.Order.Asc("p.ProductCode"))
                .List<OrderItem>();

I get this error and can't make any sense of it:

The value "System.Object[]" is not of type "EcoStore.Domain.OrderItem" and cannot be used in this generic collection.
Parameter name: value

Can anyone point me in the right direction?

wolli · **Posted:** Wed May 27, 2009 4:09 am

Code:

CreateCriteria("oi")
                .CreateCriteria("oi.Order", "o")
                .CreateCriteria("oi.Product", "p", JoinType.FullJoin)

.CreateCriteria returns a subcriteria. In your case you try to join Product to Order. Try this instead:

Code:

CreateCriteria("oi")
                .CreateAlias("oi.Order", "o")
                .CreateAlias("oi.Product", "p", JoinType.FullJoin)