Database performance with Nhibernate and Activerecord

Question

Inspired by the DDD hype, I designed my classes pretending there was no database at all. And then used NHibernate to map the classes to database tables. Part of my class graph look like this: Order (hasmany)--> Product (belongsto)-->Seller. To retrieve all orders placed for a certain seller. I have the code:

public class Order:ActiveRecordBase<Order>
{
    [HasMany]
    public ICollection<Product> Items{get;set;}
    ...
}

public class Product: Order:ActiveRecordBase<Product>
{
    [BelongsTo]
    public Seller Seller{get; set;} 
    ...
}

public class OrderRepository:IOrderRepository
{
    public IQuerable<Order> GetOrdersBySellerId(int sellerId)
    {
        return Order.FindAll().AsQuerable.Where(x=>x.Items.Count > 0 &&
                                                   x.Items.First().Seller.SellerID == sellerId).AsQuerable();

    }
}

Not pretty, but it worked with my unit tests. I was happy until we started to inject real data into database. The performance is so bad that I want to vomit. So, I did a little investigation for my repository code. Not surprisedly, in order to find the orders I want, I had to get All data from Order table, and All data from Product table and some data from Seller table.

So, here comes my problem. As a total dummy in database area, I don't know how to improve my repository code even though I know it stinks badly. So, I am tempted to modify my Order class to have a reference to Seller class, so that I can use hql to add a Where clause to improve the performance. But modifying class structure due to database problem seems to be violating the DDD principles.

What is your suggestion,especially in improving my repository code? I have tried Linq for ActiveRecord and hql. But couldn't get them to work.

Answer 1

Although AFAIK ActiveRecord doesn't have direct support of LINQ, you can use LINQ to NHibernate provider. Here is a sample. At its end theres an example of using more traditional and mature criteria API.

UPDATE: Sorry, I was wrong. It seems that you need inherit your entities from ActiveRecordLinqBase<T>, and build queries using its Queryable property.

It should look like

public IQuerable<Order> GetOrdersBySellerId(int sellerId)
{
    return Order.Queryable
               .Where(x=>x.Items.Count > 0 && x.Items.First().Seller.SellerID == sellerId)();
}

If you wish to stick with plain ActiveRecordBase<T>, you can use static method ActiveRecordLinq.AsQueryable<T>().

public IQuerable<Order> GetOrdersBySellerId(int sellerId)
{
    return ActiveRecordLinq.AsQueryable<Order>()
               .Where(x=>x.Items.Count > 0 && x.Items.First().Seller.SellerID == sellerId)();
}

Have you tried it with your search criteria? (I ask this, because AFAIK you not all LINQ constructs work on NHibernate)

Answer 2

After much research I got this hql statement

public IQuerable<Order> GetOrdersBySellerId(int sellerId)
{
     string hql = @"select  o 
                    from     Order o, Product p
                    where    p.Seller.SellerID=:sellerID
                    and      p in elements(o.Items )";

     SimpleQuery<Order> q = new SimpleQuery<Order>(hql);
     q.SetParameter("sellerID", sellerID);
            return q.Execute().AsQueryable();
}

Thanks for this post. I got the idea from Ayende's reply. Please let me know if this will boot the performance.