Inner joins with NetTiers

Question

I am performing a review on different kind of ORM tooling and DAL generators today. One of them is NetTiers.

I have a classic DB model with customer, order, orderdetail, etc..

I want to perform a complex inner join on those tables. This is the orginal SQL query:

SELECT [Contact].LastName, SUM(OrderRow.Amount * Product.Price) TotalAmount
FROM Contact
    INNER JOIN [Order] ON [Contact].ContactId=[Order].ContactId 
     INNER JOIN [OrderRow] ON [Order].OrderId=[OrderRow].OrderId
      INNER JOIN [Product]ON OrderRow.ProductId=Product.ProductId 
       GROUP BY [OrderRow].OrderId, [Contact].LastName
        HAVING SUM(OrderRow.Amount * Product.Price) > 100

I couldn't find a way to get this done in code with NetTiers. Can you ?

(ps: using VS2008 SP1 and SQLServer2008 SP1)

Answer 1

Why not create a custom stored procedure for that, nettiers generates specific methods for stored procedures under the TableProvider class, afterwards you can simply call your methd. the method return type will probably be a DataSet in this case(not sure!). See here

Answer 2

You can't do it without a custom stored procedure. Solution here: http://blog.benpowell.co.uk/2009/02/paging-and-sorting-in-nettiers-custom.html