How to map an objectified Many to Many relationship?
Frans Bouma
Consider Northwind. It has several objectified many to many (m:n)
relationships: Customer m:n Employee (via Order) and Order m:n Product (via
OrderDetails).
'objectified relationship' is a NIAM/ORM (object role modeling) term, see
http://www.orm.net, and it means that a m:n relationship on itself is an
entity with its own attributes (fields).
In the examples above, Order and OrderDetails are normal entities with
non-pk fields. So let's pick Order m:n Product in Northwind. This
relationship is defined on two m:1 relationships:
- OrderDetails m:1 Order
and
- OrderDetails m:1 Product
I can define for each entity a class and map it into their table in
Northwind, define the two m:1 relationships as well and all is well.
But I also want to define the m:n relationship Order m:n Product, so I can
fetch the products of an order without fetching Order Details and without
the necessity of formulating the joins, like:
var products = myOrder.Products;
which, through lazy loading, should create a query like (I use '*' but you
get the idea)
SELECT DISTINCT p.*
FROM Products p INNER JOIN [Order Details] od ON p.ProductId =
od.ProductId
INNER JOIN Orders o ON od.OrderId = o.OrderId
WHERE o.OrderId = @orderId
(yes this can be made more efficient, that's irrelevant now)
My o/r mapper supports this, also in queries with filters etc. and my
designer does as well. I wanted to support these relationships for
NHibernate in my designer as well, but couldn't find any info about how to
map Order.Products in such a way that I get the above query. I only could
find information about mapping a 'pure' m:n relationship, so with an
intermediate table which isn't an entity and doesn't have a class mapped
onto it. Typical example of a pure m:n relationship: User m:n Group, where a
3rd, 'hidden', table is present in the db, UserGroup, which has two m:1
relationships, and only PK fields which are the FK fields to resp. User and
Group.
So, my questions are:
1) is mapping Order.Products supported in NHibernate?
2) if so, how to map Order.Products ?
I know that Order.Products is 'readonly' as adding a product there requires
an OrderDetails instance as well, so this might be the reason it's _not_
supported, but I'd like to know for sure.
TIA,
Frans
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, .NET's most advanced O/R mapping designer.
LLBLGen Pro website: http://www.llblgen.com
Blog: http://weblogs.asp.net/fbouma
Twitter: http://twitter.com/FransBouma
Microsoft MVP (C#).
------------------------------------------------------------------------
Fabio Maulo
--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
--
Fabio Maulo
Frans Bouma
> Please simplified....
> I'll then add the Order.Products readonly collection.
> btw have a look to <loader>
I've sent you a private email with a .zip attachment to your email
address. Not sure if google still strips off .zip attachments...
Thanks!
Frans
> <mailto:nhusers%2Bunsu...@googlegroups.com> .
Sergej Koščejev
Product like this:
<set name="Products" table="OrderDetails">
<key column="ProductID" />
<many-to-many class="Order" column="OrderID" />
</set>
And also map OrderDetails independently as an entity (using
<composite-id> and <key-many-to-one>). Though of course
<key-many-to-one> is not entirely easy to use.
Have you tried this?
Sergej
Rúben Lício
I've a similar situation (srr for kidnap thread).
In my case i tried your <set> example and this is the error message:
Unable to create instance of class
AccessControl.Test.GroupBusinessTest. Error:
System.TypeInitializationException: The type initializer for
'AccessControl.Test.NHibernateTestHelper' threw an exception. --->
System.Collections.Generic.KeyNotFoundException: The given key was not
present in the dictionary..
My database is clean so no miss data error.
Someone have any idea?
Thank you.
Rúben Lício Reis
On 26 jul, 19:20, Sergej Koščejev <sergey....@gmail.com> wrote:
> I think you could map themany-to-manyassociation from e.g. Order to
>
> <set name="Products" table="OrderDetails">
> <key column="ProductID" />
>
> And also map OrderDetails independently as an entity (using
> <composite-id> and <key-many-to-one>). Though of course
> <key-many-to-one> is not entirely easy to use.
>
> Have you tried this?
>
> Sergej
>
> On 26.7.2010 16:41, Frans Bouma wrote:
>
>
>
> > Hi,
>
Frans Bouma
> Product like this:
>
> <set name="Products" table="OrderDetails"> <key column="ProductID" />
<many-
> to-many class="Order" column="OrderID" /> </set>
>
> And also map OrderDetails independently as an entity (using <composite-id>
> and <key-many-to-one>). Though of course <key-many-to-one> is not entirely
> easy to use.
>
> Have you tried this?
I tried it, and it indeed works! I didn't need <key-many-to-one>,
just normal mapping on orderdetails. thank you :)
I have no idea what I did wrong the first time I tried it, but I'm
glad it works now, so I can add true m:n support for NHibernate to LLBLGen
Pro v3's designer :)
One general note: the docs on M:N on nhforge's reference are
completely unclear/incomplete, as they mumble about value sets and what not,
while m:n relationships are normal relationships over two m:1 relationships
and therefore easily described. Perhaps someone (if there's time) could
update that section, at least with an example :)
For reference I'll paste my mapping files below.
Product:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="MtoMnhsimple.Model"
namespace="ManyToManyNH.EntityClasses">
<class name="Product" table="[dbo].[Product]"
optimistic-lock="version" >
<id name="Id" column="Id"
access="field.camelcase-underscore" >
<generator class="identity"/>
</id>
<property name="Name" column="Name"
access="field.camelcase-underscore"/>
<set name="OrderDetails" access="field.camelcase-underscore"
cascade="all-delete-orphan" inverse="true" fetch="select">
<key>
<column name="ProductId"/>
</key>
<one-to-many class="OrderDetails"/>
</set>
<set name="Orders" table="[dbo].[OrderDetails]"
access="field.camelcase-underscore" inverse="true">
<key>
<column name="ProductId"/>
</key>
<many-to-many class="Order" column="OrderId"/>
</set>
</class>
</hibernate-mapping>
Order:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="MtoMnhsimple.Model"
namespace="ManyToManyNH.EntityClasses">
<class name="Order" table="[dbo].[Order]" optimistic-lock="version"
>
<id name="Id" column="Id"
access="field.camelcase-underscore" >
<generator class="identity"/>
</id>
<property name="OrderDate" column="OrderDate"
access="field.camelcase-underscore"/>
<property name="ShippingDate" column="ShippingDate"
access="field.camelcase-underscore"/>
<set name="OrderDetails" access="field.camelcase-underscore"
cascade="all-delete-orphan" inverse="true" fetch="select">
<key>
<column name="OrderId"/>
</key>
<one-to-many class="OrderDetails"/>
</set>
<set name="Products" table="[dbo].[OrderDetails]"
access="field.camelcase-underscore" inverse="true">
<key>
<column name="OrderId"/>
</key>
<many-to-many class="Product" column="ProductId"/>
</set>
</class>
</hibernate-mapping>
OrderDetails:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="MtoMnhsimple.Model"
namespace="ManyToManyNH.EntityClasses">
<class name="OrderDetails" table="[dbo].[OrderDetails]"
optimistic-lock="version" >
<composite-id>
<key-property name="OrderId" column="OrderId" />
<key-property name="ProductId" column="ProductId" />
</composite-id>
<property name="Quantity" column="Quantity"
access="field.camelcase-underscore"/>
<many-to-one name="Order"
access="field.camelcase-underscore" fetch="select" cascade="none">
<column name="OrderId"/>
</many-to-one>
<many-to-one name="Product"
access="field.camelcase-underscore" fetch="select" cascade="none">
<column name="ProductId"/>
</many-to-one>
</class>
</hibernate-mapping>
I haven't looked into making the m:n collections readonly but I'll look into
that as well, as it's of course not possible to save these.
FB
> > -- Lead developer of LLBLGen Pro, .NET's most advanced O/R mapping
> > designer.
> > LLBLGen Pro website: http://www.llblgen.com
> > Blog: http://weblogs.asp.net/fbouma
> > Twitter: http://twitter.com/FransBouma Microsoft MVP (C#).
> > ----------------------------------------------------------------------
> > --
> >
> >
>
> --
Fabio Maulo
access="field.camelcase-underscore" inverse="true">
<key>
<column name="OrderId"/>
</key>
<many-to-many class="Product" column="ProductId"/>
</set>
Fabio Maulo
Frans Bouma
> access="field.camelcase-underscore" inverse="true">
> <key>
> <column name="OrderId"/>
> </key>
> <many-to-many class="Product" column="ProductId"/>
> </set>
>
>
> you can do it in RAM you don't have to specify it in the mapping.
that requires all intermediate entities to be fetched as well. The
query executed when I do:
var products = order.Products;
is
SELECT products0_.OrderId as OrderId1_, products0_.ProductId as ProductId1_,
product1_.Id as Id2_0_, product1_.Name as Name2_0_
FROM [dbo].[OrderDetails] products0_ left outer join [dbo].[Product]
product1_ on
products0_.ProductId=product1_.Id
WHERE products0_.OrderId=@p0;
1) way more efficient,
2) I can also use order.Products in queries.
FB
> <mailto:nhusers%2Bunsu...@googlegroups.com> .
> > For more options, visit this group at
> > http://groups.google.com/group/nhusers?hl=en.
>
> --
> You received this message because you are subscribed to the Google
> Groups "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to
> nhusers+u...@googlegroups.com
> <mailto:nhusers%2Bunsu...@googlegroups.com> .
> For more options, visit this group at
> http://groups.google.com/group/nhusers?hl=en.
>
>
>
>
>
>
> --
> Fabio Maulo
>
>
>
Fabio Maulo
Fabio Maulo
Frans Bouma
is
> added/removed/modified-product.
that's the downside of working with data outside the db. Of course
it's stale data, valid on a moment in time.
But indeed, it's one of the downsides of objectified relationships,
but if you know this, it's not really a problem.
> You can solve the Order.Product with a simple LINQ in RAM and you can set
> the batch-size to upload all products of all details in one shot.
to get all Products related to an order:
- first fetch all OrderDetails for order
- per OrderDetail fetch all products.
that's a select n+1.
If all data is already in-memory, sure, the traveral of the object
graph is easy. It's the point where the data isn't in memory that's
interesting for this case. :)
See this as a 'view' over two m:1 relationships.
> About the usage of Order.Products in queries you can map a collection with
> access="none".
sure, but why not have both?
The only question that sometimes pops up is how to add a new m:n
relationship this way, which is answerable by adding the intermediate entity
with two references instead, if the intermediate entity is a full entity.
FB
> > <mailto:nhusers%2Bunsu...@googlegroups.com
> <mailto:nhusers%252Buns...@googlegroups.com> > .
>
> > > For more options, visit this group at
> > > http://groups.google.com/group/nhusers?hl=en.
> >
> > --
> > You received this message because you are subscribed to the
> Google
> > Groups "nhusers" group.
> > To post to this group, send email to
> nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > nhusers+u...@googlegroups.com
> <mailto:nhusers%2Bunsu...@googlegroups.com>
>
> > <mailto:nhusers%2Bunsu...@googlegroups.com
> <mailto:nhusers%252Buns...@googlegroups.com> > .
Fabio Maulo
--
Fabio Maulo
Frans Bouma
>
>
>
> that's a select n+1.
>
> No, it isn't. Try what I said.
You said:
> You can solve the Order.Product with a simple LINQ in RAM and you can set
the batch-size to
> upload all products of all details in one shot.
so that should then be:
public IEnumerable<Product> Products
{
get
{
return from od in this.OrderDetails
from p in od.Products
select p;
}
}
this then is only not a disaster if the this.OrderDetails reference
fetches all OrderDetails entities and all Products in 1 go, correct?
this thus means
- I always have to do that (and perhaps I don't want to), as it's a mapping
directive. I could use fetch set to join to do that, as batch is hardcoded
batching and thus totally not interesting. Setting fetch to join also has a
side effect that I always eager-load the related data, IMHO not interesting.
- it still materializes orderdetails entities, something which I'm not
interested in
- it doesn't work on .NET 2.0
So this indeed is equal to what I am after if all 3 points above are
no problem. However there are situations where it IS a problem, hence it's
nice to have the alternative.
After all, this is a generic feature not for me, but for our users.
So even if *I* think it's perhaps doable in 'a' situation in memory, that
doesn't mean it matches all scenarios of all our users. We're in the tool
business, Fabio, what we think is irrelevant, what the user needs is
relevant.
Thanks for the help/time btw :)
FB
Fabio Maulo
--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
--
Fabio Maulo
Frans Bouma
Product
> and not a collection (well... it has even a property ProductId but I don't
> want know what you want do with it)
it's a pk field? ;)
> The correct LINQ is
> OrderDetails.Select(d=> d.Product)
>
> if you set the batch-size you will see all products, related with the
whole
> Order, uploaded in just one SQL.
yes, you're right, I was incorrect.
Still loading the product with the orderdetails is not always what
one wants, hence it's IMHO desirable that one can opt for an alternative.
FB
> <mailto:nhusers%2Bunsu...@googlegroups.com> .
Michael Möhle
Hi Fabio,
would you like to post an example here? I am trying the same thing ;-)
Michael
Fabio Maulo
Michael Möhle
I have an entity Artikel:
<class name="Artikel" table="artikel">
<id name="Nr" column ="nr" type="int" access ="nosetter.pascalcase-m-underscore">
<generator class ="sequence">
<param name="sequence">artikel_nr_seq</param>
</generator>
</id>
<property type="int" name="ArtikelNr" column="artikelnr" access ="nosetter.pascalcase-m-underscore" not-null="true"/>
<property type="string" length="16" name="BarcodeString" column="barcode" access ="nosetter.pascalcase-m-underscore"/>
<property type="string" length="80" name="NameEnglisch1" column="nameenglisch1" access ="nosetter.pascalcase-m-underscore"/>
<property type="string" length="60" name ="NameEnglisch2" column="nameenglisch2" access ="nosetter.pascalcase-m-underscore"/>
.
.
.
<bag name="Auftraege" inverse="true" lazy="true" access ="nosetter.pascalcase-m-underscore">
<key/>
<many-to-many class ="Auftrag"/>
<loader query-ref ="ArtikelAuftrag"/>
</bag>
</class>
An entity Auftrag:
<class name="Auftrag" table="auftrag1" lazy="true">
<id name="Nr" column ="nr" type="int" access ="nosetter.pascalcase-m-underscore">
<generator class ="sequence">
<param name="sequence">auftrag1_seq</param>
</generator>
</id>
<property type="int" name="AuftragNr" column="nr2" access ="nosetter.pascalcase-m-underscore" not-null="true"/>
<property type="int" name="Jahr" column="jahr" access ="nosetter.pascalcase-m-underscore" not-null="true"/>
<property type="date" name="Datum" column="datum" access ="nosetter.pascalcase-m-underscore"/>
.
.
.
<bag name="AuftragDetails" inverse="true" lazy="true" cascade="all" access ="nosetter.pascalcase-m-underscore" order-by ="artikelnrzusatz asc">
<key column="nrhaupt" />
<one-to-many class="AuftragDetails" />
</bag>
</class>
An entity Auftragdetails:
<class name="AuftragDetails" table="auftrag2" lazy="true">
<id name="Nr" column="nr" type="int" access ="nosetter.pascalcase-m-underscore">
<generator class="sequence">
<param name="sequence">auftrag2_seq</param>
</generator>
</id>
<property type="int" name="Jahr" column="jahr" access ="nosetter.pascalcase-m-underscore"/>
<property type="int" name="ZeilenNr" column="zeilennr" access ="nosetter.pascalcase-m-underscore"/>
<many-to-one name="Auftrag" cascade="none" class="Auftrag" column="nrhaupt" access ="nosetter.pascalcase-m-underscore" />
<many-to-one name="Artikel" cascade="none" class="Artikel" column="artikelnr" access ="nosetter.pascalcase-m-underscore" />
<property type="int" name="ArtikelNr" column="artikelnrzusatz" access ="nosetter.pascalcase-m-underscore" />
.
.
.
</class>
And my problem is the Bag in Artikel (Auftraege). I want all “Auftraege” where the “Artikel” is on. (Maybe reduced to Offene Auftraege(open orders), but this is the next step ;-)
Here is the the loading sql:
<sql-query name ="ArtikelAuftrag" read-only ="true">
<return alias ="at1" class ="Auftrag" lock-mode ="read"/>
select at1.*
from auftrag1 at1
inner join auftrag2 at2 ON at1.nr=at2.nrhaupt and (at2.artikelnr=?)
</sql-query>
The error message is:
"Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt." (null reference?)
StackTrace = " bei NHibernate.Collection.PersistentBag.get_Count()
bei NHibernate.DebugHelpers.CollectionProxy`1.get_Items()"
Any hints are welcome!
mbd-team
client. And it seems, that i did not get got all answers here. Please
forget my question at all. With the hints from you and Frans i could
solve my Problem!
Michael
>
> Hi Fabio,
>
> would you like to post an example here? I am trying the same thing ;-)
>
> Michael
>
> Von: nhu...@googlegroups.com [mailto:nhu...@googlegroups.com] Im Auftrag
> von Fabio Maulo
> Gesendet: Montag, 26. Juli 2010 16:53
> An: nhu...@googlegroups.com
> Betreff: Re: [nhusers] How to map an objectified Many to Many relationship?
>
> Frans.
>
> Can you send me a private mail with classes and scrip to create tables ?
>
> Please simplified....
>
> I'll then add the Order.Products readonly collection.
>
> btw have a look to <loader>
>
> On Mon, Jul 26, 2010 at 11:41 AM, Frans Bouma <fr...@sd.nl> wrote:
>
> Hi,
>
> Consider Northwind. It has several objectified many to many (m:n)
> relationships: Customer m:n Employee (via Order) and Order m:n Product (via
> OrderDetails).
>
> For more options, visit this group athttp://groups.google.com/group/nhusers?hl=en.
> --
> Fabio Maulo
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to
> nhusers+u...@googlegroups.com
> For more options, visit this group athttp://groups.google.com/group/nhusers?hl=en.
> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to
> nhusers+u...@googlegroups.com
> For more options, visit this group athttp://groups.google.com/group/nhusers?hl=en.