Hi there,

I am really struggling to create a Criteria based query in NHibernate.

In simplistic model of my domain I have:

Order (maps to table Orders) object

Order has a one-to-many relationship with OrderItem (maps to OrderItems table) and is mapped as a bag property: Items.

Each OrderItems has a one-to-many relationship with Seller (maps to Sellers) and a mapped proerty of Owner

I need to write a query that says "Give me all the Orders that contain items sold by :SellerID"

Depending on user inputs I want to then add further criteria such as a date range.

This is what I have:



However this query is highly inefficient, and often timeout my sql server db locally.

Can anyone help?

Many thanks for your help in answering what is surely a simple question.

Regards, Richard

You may need some indexes on your DB, however you might also want to try breaking some of your query out into a subquery to prevent large joins across some tables.

I find that sometimes joins across large tables in our DB (some with >40 million records) that the query optimizer doesn't come up with good plans for joins and executing subqueries is more efficient.

Anyway, the answer to your question is going to be DB dependent I suspect.

Cheers,

Symon.

Hi,

Thanks for your comments merge_s.rottem I will look into your selections.

Looking a this query further I have realised that the query is requesting duplicate Orders - ie is getting an Order for every OrderItem.

The SQL equivalent for what I want is



The extra criteria comes in because NHibernate uses the SellerID db column to map the Seller object to the Owner property of OrderItem.

Regards