I went with HQL and saved the hassles. Any tips on improving this would be appreciated though. One thing I would do in native sql is change the date comarison stuff to come from a single query as a table, i.e.
FROM ( SELECT MIN, MAX FROM .. ) T public IList<Order> GetOrdersBy(string referenceNumber = null, int? customerId = null, int? carrierId = null, DateTime? startDate = null, DateTime? endDate = null, int? websiteId = null, OrderStatus? status = null) { var byStatusHql = @" select odor from Order odor join fetch odor._orderStatusLog statusLog where (:referenceNumber is null or odor.Quote.ReferenceNumber=:referenceNumber) and (:customerId is null or odor.Quote.Customer.CustomerID=:customerId) and (:carrierId is null or odor.Quote.Carrier.CarrierID=:carrierId) and (:websiteId is null or odor.Quote.Customer.Website.WebsiteID=:websiteId) and ( :startDate is null or :startDate > (select min(CreatedDate) from OrderStatusLog lg where lg in elements(odor._orderStatusLog)) ) and ( :endDate is null or :endDate <= (select max(CreatedDate) from OrderStatusLog lg where lg in elements(odor._orderStatusLog)) ) and ( :status is null or :status = ( select Status from OrderStatusLog lg where lg in elements(odor._orderStatusLog) and lg.OrderStatusLogID = (select max(OrderStatusLogID) from OrderStatusLog lgg where lgg in elements(odor._orderStatusLog)) ) ) "; var query = Session.CreateQuery(byStatusHql); query.SetParameter("referenceNumber", referenceNumber) .SetParameter("customerId", customerId) .SetParameter("carrierId", carrierId) .SetParameter("websiteId", websiteId) .SetParameter("startDate", startDate) .SetParameter("endDate", endDate) .SetParameter("status", status) ; return query.List<Order>(); } On Jun 4, 11:09 pm, Corey Coogan <coreyacoo...@gmail.com> wrote: > I need to build a dynamic query from a search form. I have been using > Linq for NH and it's been great when the queries are simple. I just > build the query for the passed parameters, like so: > > public IList<Order> GetOrdersBy(int? customerId=null, int? carId=null, > string status=null) > { > var query = Session.Query<Order>(); > if(customerId.HasValue) > query = query.Where(x => x.status == status); > > ... > ... > > } > > Pretty simple since everything is on the same table. > > Now I need to track the status everytime it changes so there is a new > table that looks like this: > > OrderStatusLog > ----------------------- > id int > orderId int > status varchar > createdate datetime > > When someone searches for a status, I need to search against the MAX > status. When someone searches on a date range, I want to search > between min(createdate) and max(createdate). I tried to do this with > a GROUP BY in linq but I got a notimplemented. What is the best way > to do this? I was avoiding QueryOver and Criteria for much of this > because it always trips me up. > > Any suggestions before I hack together an HQL string? -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to nhusers@googlegroups.com. To unsubscribe from this group, send email to nhusers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.