NHibernate Tutorials (11)--many-to-many related queries

The content of this section

• Many-to-many relationships are introduced
• Many-to-many mapping relationships
• Many-to-many correlation queries
  • 1. Native SQL Association Query
  • 2.HQL Correlation Query
  • 3.Criteria API Association Query
• Conclusion

Many-to-many relationships are introduced

Let's look again at the data model established in the second article:

In the diagram, I have clearly marked the relationship between tables, on two analysis of the "foreign key relationship" between the customer and order, or the "parent-child relationship", "one-to-many relationship" and the associated query, which is the order-centric analysis of the relationship between order and product, Look directly at the two tables of the following picture:

The two table relationships above mean that the order has multiple products,product that belong to more orders. We call order and product a many-to-many relationship, and we'll go into more detail on how nhibernate maps many-to-many relationships and their many-to-many correlation queries.

Many-to-many mapping relationships 1. Order has multiple products

With the foundation of the last two articles, now straight to the topic, build relationships. You can use this code as a template, later in the work of reference.

Modify the Order.cs class code as follows:

Namespace domainmodel.entities{public class order {public vir tual int OrderId {get; set;} public virtual datetime OrderDate { get; set;} //many-to-one relationship: orders belong to a customer public virtual customer Customer {get; set;} //many-to-many relationships: order has multiple products  public virtual ilist<product> Products {get; set; } }} 

Modify the Order.hbm.xml mapping file as follows:

<?XmlVersion="1.0 "Encoding="Utf-8 "?><Hibernate-mappingxmlns="urn:nhibernate-mapping-2.2 "Assembly="Domainmodel "Namespace="Domainmodel "> <ClassName="Domainmodel.entities.order,domainmodel "Table="' Order '> <IdName="OrderId "Column="OrderId "Type="Int32 "Unsaved-value="0 "> <GeneratorClass="Native/> </Id> <PropertyName="OrderDate "Column="OrderDate "Type="DateTime "Not-null="True/> <!--A many-to-one relationship: orders belongs to a customer-<Many-to-oneName="Customer "Column="Customer "Not-null="TrueClass="Domainmodel.entities.customer,domainmodel "Foreign-key="Fk_customerorders "/> <!--Many-to-many relationships: order has multiple products-<BagName="Products "Generic="TrueTable= "orderproduct" > <key column= " ' Order ' "foreign-key=" FK_ Orderproducts "/> <many-to-many column= "product" class = " Domainmodel.entities.product,domainmodel "foreign-key=" fk_productorders "/> </bag  > </class></hibernate-mapping>    

In a many-to-many relationship, both parties use the Bag collection and the Many-to-many element. Look at the same properties as the One-to-many,many-to-one property above.

2.Product belongs to multiple orders

To create a new Product.cs class in the Entities folder of the project Domainmodel layer, write the following code:

namespace domainmodel.entities{    Product    {        ilist<set;    }}}

Create a new Product.hbm.xml mapping file in the Mappings folder in the project Domainmodel layer and write the following code:

<?XmlVersion="1.0 "Encoding="Utf-8 "?><Hibernate-mappingxmlns="urn:nhibernate-mapping-2.2 "Assembly="Domainmodel "Namespace="Domainmodel "> <ClassName="Domainmodel.entities.product,domainmodel "Table="Product "> <IdName="ProductId "Column="ProductId "Type="Int32 "Unsaved-value="0 "> <GeneratorClass="Native/> </Id> <PropertyName="Name "Column="Name "Type="StringNot-null="TrueLength="40C/> <PropertyName="Cost "Column="Cost "Type="FloatNot-null="True/> <!--Many-to-many relationships: product belongs to multiple orders-<BagName="Orders "Generic="TrueTable= "orderproduct" > <key column= " Product "foreign-key=" FK_ Productorders "/> <many-to-many column= " ' Order '" class= "domainmodel.entities.order , Domainmodel "foreign-key=" FK_ Orderproducts "/> </bag      > </class></hibernate-mapping>    

Many-to-many correlation queries

Use the three query methods provided in NHibernate to implement many-to-many association queries that return a list of customers for all orders and products.

1. Native SQL Association Query

public ilist<customer > usesql_getcustomerswithordershavingproduct (datetime orderDate) {return _session. Createsqlquery ( "SELECT DISTINCT {customer.*} from customer {customer}" + " inner join [Order] o on O.customer={customer}. CustomerId "+ " inner join orderproduct op on O.orderid=op.[order] "+ " inner join Product p on op. Product=p.productid where o.orderdate>: OrderDate "). Addentity ( "customer", typeof ( customer)) . SetDateTime ( "OrderDate", orderDate). List<customer> ();}          

Here you need to use join to tell the query how to correlate between tables. No matter how complex the relationship is, we must specify the return value in the query statement. Here you use Addentity to set the returned entity.

2.HQL Correlation Query

ilist<customer> usehql_getcustomerswithordershavingproduct (DateTime orderDate) {    return _ Session. CreateQuery ("c.orders.elements o where o.orderdate >: OrderDate"). SetDateTime ("OrderDate", orderDate). list<customer> ();}     

Since the mapping file already defines a one-to-many, many-to-many relationship between entities, NHibernate knows how to correlate these entities through the mapping file, and we do not need to repeat the definition in the query statement. Here the query and the previous one using HQL Association query statements, NHibernate completely can go to associate objects, to achieve query orders and products.

3.Criteria API Association Query

Because of the associations between entities we have already defined them in the mapping file. So we can easily specify constraints between entities when querying sub-objects to navigate between objects using child Createcriteria statements. Here the second Createcriteria () returns a new instance of Icriteria and points to the elements of the orders entity. The third element that points to the products entity.

Publicilist<customer> usecriteriaapi_ Getcustomerswithordershavingproduct () {return _session. Createcriteria (typeof (customer)). ADD (restrictions.eq ( "Firstname",  "yjing")) . Createcriteria ( "Orders"). ADD (restrictions.gt ( "OrderDate", new datetime (2008,10,1)). Createcriteria ( "products"). ADD (restrictions.eq ( "Name",  "cnblogs")) . List<customer> ();}          

Here I use a picture to explain the mystery of this code, but also show some constraints.

Write a test case Test usecriteriaapi_getcustomerswithordershavingproduct () method, traverse the list to see if the product name is "Cnblogs", ok! Test passed.

[Test]public void Usecriteriaapi_getcustomerswithordershavingproducttest () {ilist<customer> customers = _relation.    Usecriteriaapi_getcustomerswithordershavingproduct (); bool found = false; foreach (Customer C in customers) { foreach (Order o in c.orders) { foreach (Product p C12>in o.products) { if (p.name = = "Cnblogs") {found = true; Break ;} }}} assert.istrue (found);}               

Here is a simple example to query the product ID associated with some customers, the test case is similar to the above, their own changes can be.

ilist<customer> usecriteriaapi_getcustomerswithordershavingproduct (int productId) {    return _ Session. Createcriteria (typeof (Customer)). Createcriteria ("Orders"). Createcriteria ("Products"). ADD (restrictions.eq ("ProductId", ProductId)). list<customer> ();}         

Conclusion

This article completes many-to-many relationship mappings in NHibernate through the form of full-code, and implements many-to-many correlation queries using the three query methods provided in the NHibernate. I hope you have some help and practice. Next time we continue to discuss nhibernate topics, such as delayed loading, immediate loading, object status and other topics, about friends reply said to discuss more topics, I can only say, and so on, slowly, this is the 11th article, first of all the basic problems to clear.

Link to this series: NHibernate Tour series article navigation

NHibernate Q&a

• Welcome to join NHibernate Chinese community, discuss nhibernate knowledge together!
• Please go to nhibernate Chinese community to download this series of related source code.

Continue sharing nhibernate! next time

NHibernate Tutorials (11)--many-to-many related queries