Go NHibernate Tour (10): Explore Parent-child (one-to-many) association queries

The content of this section

• Associated query Ingestion
• One-to-many correlation query
  • 1. Native SQL Association Query
  • 2.HQL Correlation Query
  • 3.Criteria API Association Query
• Conclusion

Associated query Ingestion

There are three types of queries available in NHibernate: NHibernate Query Language (hql,nhibernate queries Language), conditional query (criteria api,query by Example (QBE) is a special case of the criteria API), native SQL (Literal sql,t-sql, PL/s). This section uses each of these three ways to correlate queries.

First look at the parent-child relationship we established for customer and order:

One-to-many correlation query 1. Native SQL Association Query

In the relational model: You can use a child table as an inner join to query customer, like this:

SELECT * FROM Customer C inner join Order o on C.customerid=o.customerid where o.customerid=<id of the customer>

Use the parent table as the inner JOIN query order, like this:

SELECT * FROM Oder o inner join Customer C on O.customerid=c.customerid where o.orderid=<id of the order>

Let's take a look at using native SQL queries in NHibernate. This article is to complete the query of the customer list after the orderdata of the order of different queries.

Public ilist<customer> usesql_getcustomerswithorders (DateTime orderDate) {    return _session. Createsqlquery ("SELECT DISTINCT {customer.*} from the customer {customer}" +    "inner join [Order] o on O.customer={custome R}. CustomerId where o.orderdate>: OrderDate ")        . Addentity ("Customer", typeof (Customer))        . SetDateTime ("OrderDate", OrderDate)        . List<customer> ();}

The case is: Instantiate the IQuery interface, use the Isession.createsqlquery () method, pass the argument is the SQL query statement; {customer.*} tag is a shorthand for all the properties of the Customer. Use the Addentity query to return the persisted class, Setdatatime set the parameters, depending on the type, the method name is different.

2.HQL Correlation Query

Query the HQL associated query for the customer list after the Orderdata order:

Public ilist<customer> usehql_getcustomerswithorders (DateTime orderDate) {    return _session. CreateQuery ("SELECT DISTINCT C from Customer C inner join c.orders o  where o.orderdate >: OrderDate")        . SetDateTime ("OrderDate", OrderDate)        . List<customer> ();}

The use of object-oriented hql, at a glance, conforms to the object-oriented programming habit.

Write a test case Test usehql_getcustomerswithorderstest () Query method is correct:

[test]public void Usehql_getcustomerswithorderstest () {    ilist<customer> customers = _relation. Usehql_getcustomerswithorders (New DateTime (1));    foreach (Customer C in Customers)    {        foreach (Order o in c.orders)        {            assert.greaterorequal (o.orderdate, New DateTime (+, 1));}    }    foreach (Customer C in Customers)    {        assert.areequal (1, customers. count<customer> (x = x = = c));}    }

First call the Usehql_getcustomerswithorders () method to query the customer list after October 1, 2008, traverse the customer list, assert that the customer is the expected 1, and his order time is after October 1, 2008. Ok! The test was successful. Note: This test case can test all the associated queries in this article.

3.Criteria API Association Query

We use Createcriteria () to navigate between associations, and it's easy to specify constraints between entities. Here the second Createcriteria () returns a new instance of the Icriteria and points to the elements of the orders entity. The sub-Createcriteria statement is used in querying a neutron object because the association between entities is already defined in the mapping file. There is also a way to use CreateAlias () to not create a new instance of Icriteria.

This example returns a duplicate of the customer list, not the result we want.

Public ilist<customer> usecriteriaapi_getcustomerswithorders (DateTime orderDate) {    return _session. Createcriteria (typeof (Customer))        . Createcriteria ("Orders")        . ADD (restrictions.gt ("OrderDate", OrderDate))        . List<customer> ();}

Pre-filtration

Use the Setresulttransformer (Iresulttransformer Resulttransformer) method of the Icriteria interface to return a customer that satisfies a specific condition. In the example above, the conditional query is used to observe that its generated SQL statement is not distinct, You can then use the methods in the Nhibernate.transform namespace or use the NHibernate.CriteriaUtil.RootEntity provided by NHibernate, NHibernate.CriteriaUtil.DistinctRootEntity, NHibernate.CriteriaUtil.AliasToEntityMap static method to realize the role of pre-filtering. Then the above query should be modified to:

Public ilist<customer> usecriteriaapi_getcustomerswithorders (DateTime orderDate) {    return _session. Createcriteria (typeof (Customer))        . Createcriteria ("Orders")        . ADD (restrictions.gt ("OrderDate", OrderDate))        . Setresulttransformer (New NHibernate.Transform.DistinctRootEntityResultTransformer ())        //or. Setresulttransformer (NHibernate.CriteriaUtil.DistinctRootEntity)        . List<customer> ();}

This example implements the effect we want from the perspective of the conversion result set.

Projection

Call the Setprojection () method to implement an application projection into a query. NHibernate.Criterion.Projections is the projection instance factory, projections provides a lot of methods to see below, the drop-down list of the methods are not many AH:

You can now conditionally query the provided projection to accomplish the same purpose:

Public ilist<customer> usecriteriaapi_getdistinctcustomers (DateTime orderDate) {    Ilist<int> ids = _ Session. Createcriteria (typeof (Customer))        . Setprojection (Projections.distinct (Projections.projectionlist ()                                                          . ADD (Projections.property ("CustomerId"))                                                      )        . Createcriteria ("Orders")        . ADD (restrictions.gt ("OrderDate", OrderDate))        . List<int> ();    Return _session. Createcriteria (typeof (Customer))        . ADD (restrictions.in ("CustomerId", IDs. Toarray<int> ()))        . List<customer> ();}

We can add a number of projections to the projection list, for example, I add a CustomerID property value to the projection list, and all the attribute values in this list are set with the distinct projection. The first sentence returns the order time after OrderDate all customers distinct CustomerID, the second sentence based on the returned CustomerID query the customer list. Achieve the above purpose. The resulting SQL statement is found to have distinct in it. We use projections to easily combine the various methods we need.

Go NHibernate Tour (10): Explore Parent-child (one-to-many) association queries