OneToMany with unmatching key columns

[Corey Coogan]

I have 2 entities that are described below. I want to map many credit
cards to one policy. There is no proper FK between them in our Oracle
DB (I can't change the DB at all). They do match up on the
PolicyNumber->PolNbr properties though.

I can't figure out how to map this relationship. Here's what I have
so far, but I'm not sure how to specify the Key in the containing
entity that doesn't match.

mapping.HasMany<CreditCard>(x => x.CreditCards)
.KeyColumn(PolNbr )
//where to map CreditCard.PolNbr to Policy.PolicyNumber

Policy
{
PolicyNumber : string (PK)
PolicyDate : date (PK)
CreditCards : IList<CreditCard>
}

CreditCard
{
PolNbr : String (PK)
CcType : String (PK)
ExpDt : String (PK)
//3 more columns that are the PK
}

[Hudson Akridge]

I'm guessing that PolicyNumber/PolicyDate in Policy are mapped as a composite key?

Is there any reason for this other than that's what the database has mapped it as? Is PolicyNumber a valid entity Identifier as far as the domain model is concerned?

If so, then you'd just map PolicyNumber with an 

Id(x=> x.PolicyNumber).GeneratedBy.Assigned(); //Just a guess on the assigned thing since I think your app might generate them

And map your collection like so:

HasMany<CreditCard>(x => x.CreditCards).AsBag()
               .KeyColumn(PolNbr );

Viola. Done. No need for a where at all there.

Now, given that I caught a mapping. prefix, I'm assuming you're using an automapper. I'd advise against it in your case. It's very rare to expect automapper to automagically map for a legacy database environment. Swap to Fluent Mappings.

--
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

[Corey Coogan]

Thanks for the help Hudson. PolicyNumber/PolicyDate is a valid domain
accepted composite identifier. It's true that I can't change any of
this and they are actually generated by the system. We're building
support systems, so we'll never actually do inserts here.

I can see your point about relying on AutoMapping. It was worth a
shot, but I've already got some failing PersistenceSpecification<T>
tests.

cc

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

[Corey Coogan]

Wait, I just realized that I may still be confused. Your example is
assigning Id(x =>, but this is a composite key. How will FNH know
that PolicyNumber=PolNbr?

On Jan 19, 2:37 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:

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

[Hudson Akridge]

By the HasMany declaration. This is a one-to-many mapping in NH lingo, and I'd recommend reading up on that. NHibernate is smart enough to know what your ParentId column is on Policy when going to the child table (CreditCards), it only needs to be told what column matches the ParentId in the child (CreditCards) table. In your case, it's PolNbr.

I'd recommend against using composite keys in your mapping. You don't have to mirror how the database has declared things. In that case, if PolicyNumber is truly your ID, which it seems like you can do since things that relate to your Policies only contain a PolicyId back to their parent, then that's the only column that needs to be specified as your Id.

Composite Keys are for when there's no other option in my opinion, and if you declare composite keys, any children tables must also have the parents composite key columns contained within them to make for a valid database relationship. Since the CreditCards table appears to only have the PolicyNumber (PolNbr) then you, by database semantics, should be able to just treat a Policy as unique in the business domain by nothing more than a PolicyNumber. If that wasn't a case, a CreditCard could belong to multiple Policies, and in that case, the DBA has made an error and really needs a join table between Policies and CreditCards.

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.

--

[Corey Coogan]

Thanks again Hudson. I'm totally with you on the DB stuff and have
read quite a bit on the relationships. Unfortunately, most the tables
truly need a PolicyNumber and PolicyDate as the composite key. That's
because everytime a policy is renewed or changed in any way, a new
"portfolio" gets created which is a whole mess of tables with those 2
columns used to identify them. On the other hand, something like
CreditCard, is tied to a policy regardless of it's portfolio, so in
this case it is related to a PolicyNumber only.

I realized that the keys typically match up as a traditional foreign
key constraint, but that's not the way these two tables are related,
which is why I'm posting this question. These are tied together today
with a hand-rolled data access layer. The user specifies a
policynumber and an as of date and the following queries are executed
on a single open connection:

- Get the max(policydate) <= asOfDate
- Ge the Policy record for the PolicyNumber/PolicyDate
- Get credit cards for the PolicyNumber

I'm OK with specifiying a hand-written sql statement if necessary.
Maybe I need to handle these cases where there is no real FK in the
database by populating with an NH query after the initial retrieval.
I'm just looking for the best way to map this stuff despite the ugly
nature of what I'm working with. So I although I appreciate the
advice regarding DB design, but this is truly out of my hands.

Thanks,
Corey

On Jan 19, 3:44 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> By the HasMany declaration. This is a one-to-many mapping in NH lingo, and
> I'd recommend reading up on

> that<http://ayende.com/Blog/archive/2009/04/13/nhibernate-mapping-ltsetgt....>.

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

[Hudson Akridge]

Understood. Ok, well, let's see if this works:

HasMany(x=> x.CreditCards).AsBag().KeyColumn("PolNbr").PropertyRef("PolicyNumber");

The PropertyRef should allow you to map to a non-key column in the parent table. On the References() side it allows you to map to a non key property in the related table. PropertyRef is for mapping lagacy data specifically.

If that doesn't work, do a quick ExportTo() in your config and paste the hbm.xml mappings if you could, so I can make sure FNH is writing the correct XML.

You're fortunate that you're going from Composite to a single id, and not the other way around. NHibernate doesn't support Hibernate's <properties> tag yet, which means you can only map from one column to another (hibernate allows you to do many columns to many columns in a property-ref)

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.

--

[Corey Coogan]

This worked like a charm and is exactly what I needed.

Thanks again!

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

[Hudson Akridge]

Awesome, glad to be of assistance :)

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.

--

[Corey Coogan]

I spoke too soon. My claim that it worked was a case of mistaken
identity on a test that was being run but recently changed.

Here's what this syntax is producing in an HBM.

- <bag cascade="save-update" name="CreditCards" mutable="true">
- <key property-ref="PolicyNumber">
<column name="`PolNbr`" />
</key>
<one-to-many class="Aah.Policy.InfoLayer.CrdtCardAcctRecord,
NHibernate.Spike.Data, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=null" />
</bag>

I don't think that is right? I get an error during configuration from
NH that it can't find property PolicyNumber on my Policy object
(Parent class to CreditCards). That message is very misleading and
led to hours of chasing the wrong stuff.

On Jan 20, 11:41 am, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> Awesome, glad to be of assistance :)
>

> > > > > > <fluent-nhibernate%2Bunsu...@googlegroups.com<fluent-nhibernate%252Buns...@googlegroups.com>
> > <fluent-nhibernate%252Buns...@googlegroups.com<fluent-nhibernate%25252Bun...@googlegroups.com>
>
> > > > <fluent-nhibernate%252Buns...@googlegroups.com<fluent-nhibernate%25252Bun...@googlegroups.com>
> > <fluent-nhibernate%25252Bun...@googlegroups.com<fluent-nhibernate%2525252Bu...@googlegroups.com>

> ...
>
> read more »

[Hudson Akridge]

Ah, that might make sense actually. Because you technically don't have PolicyNumber mapped as a property, it's part of your composite key. Try something like this:

Map(x=> PolicyNumberForNH, "PolicyNumber").Access.None();

Then change your property-ref to PolicyNumberForNH and see if NHibernate gets the clue then. I believe it actually needs a <property> to map a property-ref to, and not just a POCO property.

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.

--

[Corey Coogan]

I'll give that a try tomorrow.

Thanks,
Corey

On Jan 21, 2:45 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> Ah, that might make sense actually. Because you technically don't have
> PolicyNumber mapped as a property, it's part of your composite key. Try
> something like this:
> Map(x=> PolicyNumberForNH, "PolicyNumber").Access.None();
>
> Then change your property-ref to PolicyNumberForNH and see if NHibernate
> gets the clue then. I believe it actually needs a <property> to map a
> property-ref to, and not just a POCO property.
>

> ...
>
> read more »

[Hudson Akridge]

We are deep in NHibernate magic territory :) If that still doesn't work for you, I'll see what I can do about setting up a test environment to what you've got and giving it a go. It's an interesting problem, and I'd like to know what the final solution will be. My knowledge in this area is purely theoretical ;)

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.

[Corey Coogan]

This is almost working. At least I can build the sessionfactory now.
My tests against SqlLite are failing though. Thanks for all your help.

Details below:

--------------------------
Relevant Mapping
--------------------------
mapping.Map(x => x.PolicyNumberMapping, "POLICY_NUMBER").Access.None
();

mapping.HasMany(x => x.CreditCards)
.KeyColumn("POL_NBR")
.PropertyRef("PolicyNumberMapping");

- <composite-id mapped="false" unsaved-value="undefined">
- <key-property name="PolicyNumber" type="System.String, mscorlib,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<column name="`POLICY_NUMBER`" />
</key-property>
- <key-property name="PolicyDateTime" type="System.Nullable`1
[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089]], mscorlib, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089">
<column name="`POLICY_DATE_TIME`" />
</key-property>
</composite-id>
- <property access="none" name="PolicyNumberMapping"
type="System.String, mscorlib, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089">
<column name="`POLICY_NUMBER`" />
</property>

-----------------------
ERROR
-----------------------
Test method
NHibernate.Spike.Test.Unit.Mappings.When_policy_is_mapped.Can_map_policy
threw exception: System.ArgumentOutOfRangeException: Index was out of
range. Must be non-negative and less than the size of the collection.
Parameter name: index.
at System.ThrowHelper.ThrowArgumentOutOfRangeException
(ExceptionArgument argument, ExceptionResource resource)
at System.ThrowHelper.ThrowArgumentOutOfRangeException()
at System.Collections.Generic.List`1.get_Item(Int32 index)
at System.Data.SQLite.SQLiteParameterCollection.GetParameter(Int32
index)
at
System.Data.Common.DbParameterCollection.System.Collections.IList.get_Item
(Int32 index)
at NHibernate.Type.DateTimeType.Set(IDbCommand st, Object value, Int32
index)
at NHibernate.Type.NullableType.NullSafeSet(IDbCommand cmd, Object
value, Int32 index)
at NHibernate.Type.NullableType.NullSafeSet(IDbCommand st, Object
value, Int32 index, ISessionImplementor session)
at NHibernate.Type.ComponentType.NullSafeSet(IDbCommand st, Object
value, Int32 begin, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Dehydrate
(Object id, Object[] fields, Object rowId, Boolean[] includeProperty,
Boolean[][] includeColumns, Int32 table, IDbCommand statement,
ISessionImplementor session, Int32 index)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object
id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql,
Object obj, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object
id, Object[] fields, Object obj, ISessionImplementor session)
at NHibernate.Action.EntityInsertAction.Execute()
at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
at NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
at NHibernate.Engine.ActionQueue.ExecuteActions()
at
NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions
(IEventSource session)
at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush
(FlushEvent event)
at NHibernate.Impl.SessionImpl.Flush()
at NHibernate.Transaction.AdoTransaction.Commit()
at
NHibernate.Spike.Test.Unit.Mappings.When_policy_is_mapped.Can_map_policy
() in

On Jan 21, 4:03 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> We are deep in NHibernate magic territory :) If that still doesn't work for
> you, I'll see what I can do about setting up a test environment to what
> you've got and giving it a go. It's an interesting problem, and I'd like to
> know what the final solution will be. My knowledge in this area is purely
> theoretical ;)
>

> ...
>
> read more »

[Hudson Akridge]

Can't have two properties mapped to the same field in NHibernate. That's the index out of range exception. It's expecting there to be n columns, but there's really n-1 columns (for the repeat).

I'm in the middle of a bunch of non-FNH related stuff atm (also known as day job), but let me think on this and see what we can do about getting around this. We should be very close to resolving the issue. I was hoping the none modifier for access would prevent NHibernate from attempting to include the column in the query a second time. Guess not. More thoughts later.

> ...
>
> read more »

--
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.

[Corey Coogan]

Thanks for the help here Hudson. I appreciate your continued support.

How would this be possible from an XML mapping strategy?

On Jan 25, 4:28 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> Can't have two properties mapped to the same field in NHibernate. That's the
> index out of range exception. It's expecting there to be n columns, but
> there's really n-1 columns (for the repeat).
>
> I'm in the middle of a bunch of non-FNH related stuff atm (also known as day
> job), but let me think on this and see what we can do about getting around
> this. We should be very close to resolving the issue. I was hoping the none
> modifier for access would prevent NHibernate from attempting to include the
> column in the query a second time. Guess not. More thoughts later.
>

> ...
>
> read more »

[Hudson Akridge]

It's no different. Your problem isn't a FNH problem, it's an NH problem. Also, you may be able to head over to the nhusers google group and ask your question there, they have more experience solving strange edge case scenario's. If they can figure it out and get you the xml for it, then we can figure out the FNH versions of those xml elements.

The only workaround I can think of involves mapping the PolicyNumber column twice on the parent table, which is an awful workaround imo (But that would allow you to map PolicyNumber as the second property). Is there any way we can change the DB schema to move away from CompositeId's (Probably not, but figured I'd ask)?

> ...
>
> read more »

--
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.

[Corey Coogan]

Thanks Hudson. I actually posed the question to NHUsers right after
my last reply here. When I find something out, I'll share here. The
DB can't be changed. It's legacy and has an entire insurance company
running expecting that primary key. The brass here won't even
consider it. Thanks for asking though. I am a huge anti-fan of
composite keys as well.

Corey

On Jan 26, 9:09 am, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> It's no different. Your problem isn't a FNH problem, it's an NH problem.
> Also, you may be able to head over to the nhusers

> <http://groups.google.com/group/nhusers>google group and ask your question

> there, they have more experience solving strange edge case scenario's. If
> they can figure it out and get you the xml for it, then we can figure out
> the FNH versions of those xml elements.
>
> The only workaround I can think of involves mapping the PolicyNumber column
> twice on the parent table, which is an awful workaround imo (But that would
> allow you to map PolicyNumber as the second property). Is there any way we
> can change the DB schema to move away from CompositeId's (Probably not, but
> figured I'd ask)?
>

> ...
>
> read more »

[Corey Coogan]

I've heard nothing from NHUSERS group on how to resolve this. I'd be
interested in hearing your workaround of mapping PolicyNumber twice.
It sounds ugly, but perhaps the only option. I can't quite figure out
how to do this though?

> ...
>
> read more »

[Hudson Akridge]

I can try taking a look at it this weekend (although fair warning, pretty packed set of days coming up...) and see what I can do. The workaround involves adding the PolicyNumber column to your table twice. Once for actual use by the compositeId, and the second time for use as the property. Then keep them synchronized in your domain model. Now you can use a property-ref.

Ugly. As. Sin. But it should work.

> ...
>
> read more »

--
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.

[Corey Coogan]

I see. As I was messing with the mapping, I had a feeling that was
going to be the answer. Since I can't modify the DB, that won't work
for me.

Is there any way I can specify the sql to satisfy that relationship?

On Jan 29, 12:46 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> I can try taking a look at it this weekend (although fair warning, pretty
> packed set of days coming up...) and see what I can do. The workaround
> involves adding the PolicyNumber column to your table twice. Once for actual
> use by the compositeId, and the second time for use as the property. Then
> keep them synchronized in your domain model. Now you can use a property-ref.
>
> Ugly. As. Sin. But it should work.
>

> ...
>
> read more »

[Hudson Akridge]

I believe you can use a custom Loader on a hasMany (at least in NH, not sure if FNH supports that or not. Source not in front of me). We could write a custom SqlQuery for you and go that route. That was actually what I was going to work on this weekend for you, but if you wanted to check into it you might have quicker success than waiting on me to get time to look at it :)

> ...
>
> read more »

--
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.

[Corey Coogan]

Thanks for the help. It doesn't look like this is supported in FNH,
which is a bummer because this is happening on my root object. I'll
just go with HBM and see if we can figure out something better.

On Jan 29, 12:54 pm, Hudson Akridge <hudson.akri...@gmail.com> wrote:
> I believe you can use a custom Loader on a hasMany (at least in NH, not sure
> if FNH supports that or not. Source not in front of me). We could write a
> custom SqlQuery for you and go that route. That was actually what I was
> going to work on this weekend for you, but if you wanted to check into it
> you might have quicker success than waiting on me to get time to look at it
> :)
>

> ...
>
> read more »