0

Sorry if the title is a little vague, I have the following sceneraio: I have three tables in my domain model as following: enter image description here

there is a many to many relation between the AddingOrder table and Product table

in the Product entity i have

public virtual IList<AddingOrder> AddingOrders { get; set; }

with the following mapping:

<bag name="AddingOrders" generic="true" table="AddingOrderProduct">
  <key column="ProductId"/>
  <many-to-many column="OrderId" class="Application.Domain.Entities.AddingOrder,Application.Domain"/>
</bag>

in the AddingOrder entity i have

public virtual IList<Product> Products { get; set; }

with the following mapping file:

<bag name="Products" generic="true" table="AddingOrderProduct">
  <key column="OrderId"/>
  <many-to-many column="ProductId" class="Application.Domain.Entities.Product,Application.Domain"/>
</bag>

and this is the entity that maps to the joining table

public class AddingOrderProduct
{
    public virtual int Id { get; private set; }
    public virtual decimal Quantity { get; set; }
    public virtual Unit Unit { get; set; }
}

and its mapping:

<id name="Id" column="Id">
  <generator class="native" >
  </generator>
</id>
<property name="Quantity" type="Decimal" column="Quantity" />
<many-to-one name="Unit" column="Id" not-null="true" class="Application.Domain.Entities.Unit,Application.Domain" />

So far, So good all things working fine with no problems. the question is: How could i query for the properties on the join table AddingOrderProduct and join them with the two other sides of the relation?? (the two other tables), do i need an extra mapping between this table and the two other tables, for example the following is a sql query that returns the list of products with there quantity and unit that belong to the order #1 the name of the product form the product table and the quantity and unit id from the joining table AddingOrderProduct and the order id from the AddingOrder table:

select  p.Name ,aop.Quantity ,u.Name
from   Product p 
     inner join AddingOrderProduct aop on p.Id = aop.ProductId 
     inner join AddingOrder ao on ao.Id = aop.OrderId
     inner join Unit u on u.Id = aop.UnitId
where ao.Id = 1

how can i write this query with NHibernate with the mentioned mappings i omitted a lot of code just for simplicity and there is no direct mappings one-to-many between the AddingOrderProduct and the two other tables. and for the unit entity forget about it it is not an issue.

Thanks in advance;

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164

1 Answers1

1

Don't use many-to-many. Use a one-to-many from Product and AddingOrder to AddingOrderProduct and collections of AddingOrderProduct in the code.

cremor
  • 6,669
  • 1
  • 29
  • 72
  • so i have to delete the two many to many relations between the two tables and add two one-to-many reltions between the AddingOrderProduct and the two other tables so that i can query for the properties in the join table there is no way than this???. but i didn't want to do this i want to use the many to many relation and it is working now i have order.Products and product.Orders and i can query for them, it is not the problem, the problem is how i can query for the properties on the joining table the AddingOrderProduct like the sql query mentioned in the question, thanks – Mahmoud Gamal Oct 17 '11 at 05:47
  • @MGA Yes, I don't know any other way. – cremor Oct 17 '11 at 05:59