nhiberate Subquery with criteria

Question

how am i able to join/add SubQuery to a criteria ? what I wanted to do is able to make a SubQuery that will search String Product which is getCustomerProduct (two table joins). and I also want it able to do a soundex search. my problem is, I am not sure if my subquery is right or not unless i run it, how can i add it to the criteria ? I am new to nhiberate, thanks for help !

   public IPagedList<Customer> GetSearchPagedCustomer(string product, string address, string county, int pagenumber, int pageSize)
     {
    ICriteria criteria = Session.CreateCriteria<Customer>();

                //.CreateAlias("Products", "cp")
                //.CreateAlias("cp.Product", "p");

                if (!string.IsNullOrEmpty(product))
                {
                    var getCustomerProduct = DetachedCriteria.For<Product>()
                        .SetProjection(Projections.Distinct(Projections.Property("Products.id")))
                        .CreateCriteria("Product", JoinType.InnerJoin)
                        .Add(Restrictions.Eq("Product.Name",product));

                    //criteria.Add(Restrictions.Like("Name", product));
                }            
                if (!string.IsNullOrEmpty(address))
                {
                    criteria.Add(Restrictions.Like("Address1", address, MatchMode.Anywhere) || Restrictions.Like("Address2", address, MatchMode.Anywhere) || Restrictions.Like("Address3", address, MatchMode.Anywhere));
                }
                if (!string.IsNullOrEmpty(county))
                {
                    criteria.Add(Restrictions.Like("County", county, MatchMode.Anywhere));
                }

return criteria.Future<Customer>().ToPagedList<Customer>(pageNumber, pageSize);
}

score 2 · Accepted Answer · edited May 23 '17 at 11:49

The sub-query should look like this:

DetachedCriteria userSubquery = DetachedCriteria.For<Product>("product")
     // here we need to join the Many-to-many table - to later project customer
     .CreateCriteria("CustomerProducts", "cp", JoinType.InnerJoin)
     // Filter the Subquery
     .Add(Restrictions.Eq("product.Name", product)) // alias must fit 
     // SELECT The Customer Id  - projection from the join
     .SetProjection(Projections.Property("cp.CustomerId"));

     // root Customer criteria, filtered by the subquery
     criteria
       .Add( Subqueries.PropertyIn("ID", userSubquery) );

Take it please as a draft, because I do not know the exact mapping...

Maybe also check:

Query on HasMany reference
Hibernate Criteria for "in subselect" (hibernate but...)

A side note Samuel: You did a great decision to use pairing object - instead of mapping many-to-many. I always try to encourage people to use your approach... but not always they do understand why. And the query you are building is a great example. Wihtout the pairing object (i.e. with many-to-many) this kind of queries is much more complictated. Enjoy NHibernate... it is fantastic library — Radim Köhler, Aug 21 '14 at 04:34

nhiberate Subquery with criteria

1 Answers1