CODEWITHSUNDEEP
sqlsql-serverhibernatenhibernate
Sameul.T
Sameul.T

Reputation: 309

Nhibernate how to do inner join and match mode together?

I have been research it for a while now on the net, but non of them really make and sense for me .. maybe I am new to Hibernate.

however customer table suppose to join customerProduct table then join product table and I have a string product variable I would like to able match the product table Name value column .. in SQL SERVER the query i wanted to do 

select c.Name, c.Address1, c.Address2, c.County, p.Name
from dbo.Customers as c inner join dbo.CustomerProducts as cp
    on c.CustomerID = cp.CustomerID inner join dbo.Products as p 
     on cp.ProductID = p.ProductID
where c.Address1 ='&somthing&' and c.Name='&somthing&' and c.Address1='&somthing&' and p.Name='&somthing&'

however this is what i have at the moment and i cant think of how to do a inner join in Nhibernate and put string product variable to search product table 

public IPagedList<Customer> GetSearchPagedCustomer(string product, string practice, string address, string county, int pageNumber = 1, int pageSize = 100)
        {
            ICriteria criteria = Session.CreateCriteria<Customer>();
            if (!string.IsNullOrEmpty(product))
                criteria.Add(Restrictions.Like("Product.Name",product,MatchMode.Anywhere));

            if (!string.IsNullOrEmpty(practice))
                criteria.Add(Restrictions.Like("Name", practice,MatchMode.Anywhere));

            //One value and search 3 column: address 1, address 2 and address 3
            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>().OrderBy(x => x.Name).ToPagedList<Customer>(pageNumber, pageSize);
    }

Can anyone convert that SQL query code to Nhibernate code for me,, Thank very much for the you !!

Upvotes: 1

Views: 283

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

I guess that your mapping would be <bag> with <many-to-many> and The Customer has a collection Products:

public class Customer
{
    public virtual IList<Product> Products { get; set; }
    public virtual string Name { get; set; }
    ...

Then the way how to do a join would be like this:

ICriteria criteria = Session.CreateCriteria<Customer>();

if (!string.IsNullOrEmpty(product))
{
     // here we do LEFT JOIN on Products
     var productCriteria = criteria
          .CreateCriteria("Products", "Product", JoinType.LeftOuterJoin);

     // the subcriteria targeting the products table
     productCriteria.Add(Restrictions.Like("Name",product,MatchMode.Anywhere));
} 
...

And also, we can do paging and sorting on the server

criteria
    .SetMaxResults(pageSize)
    .SetFirstResult(0) // calculate from pageNumber
    .AddOrder(new Order("Name", true))
    .Future<Customer>()
    //.OrderBy(x => x.Name)
    //.ToPagedList<Customer>(pageNumber, pageSize)
;

Upvotes: 1

Related Questions