Nhibernate and not-exists query
I'm trying to construct a query in NHibernate to return a list of customers with no orders matching a specific criteria.
My Customer object contains a set of Orders:
<set name="Orders">
    <key column="CustomerID" />
    <one-to-many class="Order" />
</set>
How do I contruct a query using NHibernate's ICriteria API to get a list of all customers who have no orders? Using nati开发者_开发问答ve SQL, I am able to represent the query like this:
select * from tblCustomers c where not exists 
    (select 1 from tblOrders o where c.ID = o.CustomerID)
I have been unable to figure out how to do this using aliases and DetatchedCriteria objects. Any guidance would be appreciated!
Thanks!
this would translate to that sql...
session.CreateCriteria<Customer>("c")
    .Add(Subqueries.NotExists(
        DetachedCriteria.For<Order>("o")
        .SetProjection(Projections.Constant(1))
        .Add(Restrictions.PropertyEq("c.ID", "o.Customer.ID"))
        //Add more order restricitions here
    ))
    .List<Customer>();
if you only want customers with no orders you could also use Restrictions.IsEmpty() to do the exact same thing as above.
session.CreateCriteria<Customer>()
    .Add(Restrictions.IsEmpty("Orders"))
    .List<Customer>()
 
         加载中,请稍侯......
 加载中,请稍侯......
      
精彩评论