Mapping link table between two tables as two-way reference?

[Boz]

I'm using a legacy database schema which cannot be changed (although
it can be added to, keys, columns etc).

I have two tables that have no direct relationships between them.
There is then a third table, a link table, which simply references the
two tables with foreign keys. A record in this third table is meant
to be unique (although it isn't enforced with a key yet!) and it
indicates that a row in Table1 is related to only 1 row in Table2 and
vice-versa.

Questions:
What classes should I have?
It seems like I ought to have just two classes that each have a single
reference to each other.

How do I go about mapping this?
I can't use HasOne because that requires that the keys in each table
are unique, and besides I have the link table in the way.

I could create 3 classes, one for each table, but I can't seem to
avoid implementing one-to-many relationships (which I couldn't get to
work either).

I intend to add a composite key to the link table to enforce the
unique relationship, in case that assists with the mapping?

Simplified schema, if it helps:

Table1 (Table1_Id (pk), Name)
Table2 (Table2_Id (pk), Name)
Table3 (Table1_Id (fk), Table2_Id (fk))

Thanks in advance

Boz

[Hudson Akridge]

The only way I can think of mapping that would be using a ManyToMany between the two of them (even though you're only ever going to get a single row back), and then projecting that backing field with a public property that just does something like a return backingCollectionField.First();

I don't know if there's a better way to do it. May jump onto: http://groups.google.com/group/nhusers and ask them (using NH xml mappings of course). If they can give you better xml to do it, we can probably show you how to map it in FNH.

--
You received this message because you are subscribed to the Google Groups "Fluent NHibernate" group.
To post to this group, send email to fluent-n...@googlegroups.com.
To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.

--
- Hudson
http://www.bestguesstheory.com
http://twitter.com/HudsonAkridge

[Boz]

Hi,

I just found someone doing a similar thing:
http://stackoverflow.com/questions/497548/nhibernate-2-0-mapping-a-composite-id-and-many-to-one-relationship-causes-in
which says you need 3 classes, with the link table class referencing
the other two tables, and it seems a composite key mapping does the
job. Below is what I've got so far. Note that Table1 and Table2 are
identical for this simplified example, and their maps are
straightforward and not shown below.

public class Table1 // Table2 identical
{
public virtual decimal Id {get;set;}
public virtual string Name {get;set;}
}

public class Table3
{
public virtual Table1 Table1 {get;set;}
public virtual Table2 Table2 {get;set;}
}

public class Table3Map: ClassMap<Table3>
{
Table("Table3");
CompositeId()
.KeyProperty(x => x.Table1, "Table1_Id")
.KeyProperty(x => x.Table2, "Table2_Id")
Reference(x => x.Table1, "Table1_Id")
Reference(x => x.Table2, "Table2_Id")
}

So, comments on the above approach would be appreciated.

But also, I get the following error when running this:
"Could not determine type for: MyNamespace.Table1"

On Jan 25, 5:17 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> The only way I can think of mapping that would be using a ManyToMany between
> the two of them (even though you're only ever going to get a single row
> back), and then projecting that backing field with a public property that
> just does something like a return backingCollectionField.First();
>

> I don't know if there's a better way to do it. May jump onto:http://groups.google.com/group/nhusersand ask them (using NH xml mappings

> > fluent-nhibern...@googlegroups.com<fluent-nhibernate%2Bunsu...@googlegroups.com>

[Hudson Akridge]

You can definitely do this by creating your join table as an entity, but as it's only an entity for the sake of enforcing a database normalization/uniqueness constraint via foreign keys, and contains no additional behavior, that seems like it would impact your domain negatively, un-necessarily.

Also, point of order, you're going to want to use a .KeyReference() instead of .KeyProperty, and drop the explicit .References(). Otherwise you will end up getting the error that the person posting was getting. 
CompositeId()
   .KeyReference(x => x.Table1, "Table1_Id")
   .KeyReference(x => x.Table2, "Table2_Id")

So, is adding a third class to your domain less of an impact than having both sides of table1 and table2 be a manytomany? That's a decision you need to make for yourself. In my opinion, creating a class for a join table, with no other relevant information associated to that class, would be a last resort. You could use two HasManyToMany()'s and hide your implementation behind a public property. This seems like much less of an impact to your domain model in my opinion.

Plus, in NH terms, now your third table row is now an entity, and will become a heavier object to manage, both in the NH caching/proxying and for Sql statements when updating/inserting/deleting. But, do the testing yourself and then see which one works better for you and fits your domain preference and go with that.

To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.

--

[Boz]

Hi Hudson,

My preference is not to have 3 classes, it just doesn't feel right,
and it means I can't easily navigate from a Table1 to it's Table2
without hanging on to Table3 references somewhere. So if it's
possible to use two ManyToMany for this, that certainly sounds the
right approach.

Could you help me a bit further with this implementation? I assume
you're talking about having non-virtual properties that hide the
implementation like this (get/set implementation elided):

public class Table1

{
public virtual decimal Id {get;set;}
public virtual string Name {get;set;}

public Table2 Table2 {get {...} set {...}
}

public class Table2

{
public virtual decimal Id {get;set;}
public virtual string Name {get;set;}

public Table1 Table1 {get {...} set {...}
}

But do you then have protected virtual properties for Table1 and
Table2 aswell so that they can be mapped with Fluent mappings? I find
this aspect hard to visualise, both in terms of the entities and the
mappings. Sorry to be slow, but if you could help me further here it
would be much appreciated.

Boz

> >http://stackoverflow.com/questions/497548/nhibernate-2-0-mapping-a-co...

> >http://groups.google.com/group/nhusersandask them (using NH xml mappings

> > <fluent-nhibernate%2Bunsu...@googlegroups.com<fluent-nhibernate%252Buns...@googlegroups.com>

[Boz]

Got there in the end. I have both methods working, i.e. 3 classes and
2 classes. In this post, it's the 3 class method - Table1 and Table 2
are indirectly related via a link class Table3 (which directly maps to
my DB schema).

So, taking my classes from previous post, below is a modified Table1
and Table3

public class Table1 // Table2 similar to Table1

{
public virtual decimal Id {get;set;}
public virtual string Name {get;set;}

public virtual Table2
{
get
{
result listOfTable3[0].Table2; // obviously with collection
validation!
}
}
protected virtual IList<Table3> listOfTable3 {get; set;} // hidden
away

public Table1()
{
listOfTable3 = new List<Table3>();
}
}

public class Table3 // the link class

{
public virtual Table1 Table1 {get;set;}
public virtual Table2 Table2 {get;set;}

public override bool Equals(object obj) {/* return combined equality
check */}
public override int GetHashCode() {/* return combined hashcode */}
}

You need the Equals and GetHashCode for the .KeyReference() maps
below.

And so the mappings required to get Table1 to reference Table2 via the
link class Table3:

public class Table3Map : ClassMap<Table3> // As per Hudson's reply
{
public Table3Map()
{
Table("Table3");

CompositeId()
.KeyReference(x => x.Table1, "Table1_Id")
.KeyReference(x => x.Table2, "Table2_Id")
}
}

public class Table1Map : ClassMap<Table1> // Table2Map similar
{
public Table1Map()
{
Table("Table1");
Id(x => x.Id, "Table1_Id");
Map(x => x.Name, "Name");
HasMany<Table3>(Reveal.Property<Table1>("listOfTable3"))
.Table("Table3")
.KeyColumn("Table1_Id")
.Inverse();
}
}

The use of Reveal.Property is so that I can hide away the listOfTable3
collection as a protected property. Much neater.

I think that's all correct, my implementation certainly works, but
please note that I have only implemented this for reading data so
far. I haven't attempted to insert new records into any of the
tables. That's where one of these methods may prove better than the
other.

My next post will show the 2 class method as Hudson suggested would be
better.

Boz

> > >http://groups.google.com/group/nhusersandaskthem (using NH xml mappings

[Boz]

And so to the 2 class method of mapping this indirect one-to-one
relationship between 2 tables using a 3rd link table.

But before I start, it's worth noting this solution uses HasManyToMany
in a way which isn't recommended in the NHibernate best practices,
under "Don't use exotic association mappings", indeed it recommends to
use 3 classes instead if there's additional info in the link table:
https://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/best-practices.html

Anyway, assuming you want to do this, and I've yet to determine which
is going to work best for me:

First, modify Table1 from previous post to:

public class Table1 // Table2 similar to Table1
{
public virtual decimal Id {get;set;}
public virtual string Name {get;set;}
public virtual Table2
{
get
{

result listOfTable2[0]; // with collection validation!
}
}
protected virtual IList<Table2> listOfTable2 {get; set;} // hidden

public Table1()
{
listOfTable2 = new List<Table2>();
}
}

and then the mapping:

public class Table1Map : ClassMap<Table1> // Table2Map similar
{
public Table1Map()
{
Table("Table1");
Id(x => x.Id, "Table1_Id");
Map(x => x.Name, "Name");

HasManyToMany<Table2>(Reveal.Property<Table1>("listOfTable2"))
.Table("Table2")
.ParentKeyColumn("Table1_Id")
.ChildKeyColumn("Table2_Id")
.Inverse();
}
}

See my previous post for detail on the protected property and
Reveal.Property helper method.

That too works for me in a read capacity. Haven't tried writing yet.
I think the .Inverse() is correct, would appreciate someone checking
that.

So in summary, if you want to model a one-to-one indirect relationship
with a link table (or two way reference or whatever you call it), it
can be done with either of these two methods. 2 classes certainly
looks neater, but writing data and performance will be the proof of
the pudding. And that NHibernate best practice comment concerns me.

It's been an excellent crash course in NHibernate and Fluent for me,
but I have to warn developers that the lack of documentation on these
features in this scenario has led to a lot of trial and error
learning, and I'm sure there's more work to do on this.

Boz

> > > >http://groups.google.com/group/nhusersandaskthem(using NH xml mappings

[Hudson Akridge]

Thanks for posting the implementations of the options available for that scenario. If you want to, your two posts (with a little bit of bolding and cleaning up) might make a good wiki entry at wiki.fluentnhibernate.org .

On the best practices for exotic mappings, I agree with the gist, but for many-to-many I personally believe that to be too strong of a statement. There are plenty of use cases for many-to-many's that are relatively common in day to day operations in a mature domain model. If you have additional information that you need to store in your many-to-many table that can't be done with something like a <map /> (dictionary ManyToMany), then you should definitely consider moving to a OTM/MTO model.  Your specific scenario falls outside of that boundary imo, it's a database specific design pattern that you're looking to implement in NHibernate. A DDD alternative approach would be to enforce uniqueness in the domain, and then you could deal with a single one-to-many association from A to C, without needing a B (Or a one-to-one in a few scenario's Paul can bring up). This of course, rely's on the assumption that your domain is the only method in which the database gets populated. If there's other applications or an EDI system tied in, then you will need some business enforcement in the database.

The point of that best practices comment (I feel) was to warn you and cause you to think two or three times about how you're implementing something. Just because the mappings work, doesn't necessarily mean that it's the better design. For example. In the beginning of using NHibernate, I was quite fond of <any /> and <many-to-any /> and <one-to-one /> mappings. As I got more familiar with NHibernate and some of the ways in which it implements those in the database, doing performance metrics, etc, I realized that these were being used incorrectly in my scenario's and I could change my domain model (and thus, my database model) to support a different way of making those associations that used more <one-to-many /> <many-to-one /> mappings. I used more inheritance, and more validation logic in the domain. In the end, my data mappings were made much simpler, and much more consistent. I was also then able to optimize them significantly more than my previous reliance on exotic mappings allowed me to.

That's my take on it at least, and I'm sure someone else has a different opinion. But it's worked out pretty well for me so far :)

To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.

--