Go NHibernate Tour (11): Explore Many-to-many relationships and their associated 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        virtual int OrderId {get; set;}        Public virtual DateTime OrderDate {get; set;}        A many-to-one relationship: orders belongs 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:

<?xml version= "1.0" encoding= "Utf-8"? >bag name= "Products" generic= "true" table= "Orderproduct" > <key column= "' Order '" foreign-key= "Fk_orderprod                      Ucts "/> <many-to-many column=" Product "class =" Domainmodel.entities.product,domainmodel " foreign-key= "Fk_productorders"/> </bag> </class>
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{public    class Product    {public        virtual int ProductId {get; set;}        Public virtual string Name {get; set;}        Public virtual float cost {get; set;}        Many-to-many relationships: product belongs to multiple orders public        virtual ilist<order> orders {get; set;}}    }
 Create a new Product.hbm.xml mapping file in the Mappings folder in the project Domainmodel layer and write the following code: 
 
<?xml version= "1.0" encoding= "Utf-8"? > bag name= "Orders" generic= "true" table= "Orderproduct" > <key Co Lumn= "Product" foreign-key= "fk_productorders"/> <many-to-many column= "' Order '" class= "Dom      Ainmodel.entities.order,domainmodel "foreign-key=" fk_orderproducts "/> </bag  > </class>
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 the customer {customer}" +    "inner join [Order] o on O.customer={custome R}. CustomerId "+    " inner join orderproduct op on O.orderid=op.[order] "+    " inner the 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
Public ilist<customer> usehql_getcustomerswithordershavingproduct (DateTime orderDate) {    return _session. CreateQuery ("SELECT DISTINCT C from Customer C,"        + "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.
Public ilist<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-c.orders)        {            foreach (Product p 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.
Public 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.
Go NHibernate Tour (11): Explore Many-to-many relationships and their associated queries