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 <[email protected]> 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....>. > 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. > > > > On Tue, Jan 19, 2010 at 3:18 PM, Corey Coogan <[email protected]> wrote: > > 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 <[email protected]> wrote: > > > 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. > > > > On Tue, Jan 19, 2010 at 1:06 PM, Corey Coogan <[email protected]> > > wrote: > > > > 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 > > > > } > > > > > -- > > > > You received this message because you are subscribed to the Google > > Groups > > > > "Fluent NHibernate" group. > > > > To post to this group, send email to > > [email protected]. > > > > To unsubscribe from this group, send email to > > > > [email protected]<fluent-nhibernate%[email protected]> > > <fluent-nhibernate%[email protected]<fluent-nhibernate%[email protected]> > > > > > . > > > > For more options, visit this group at > > > >http://groups.google.com/group/fluent-nhibernate?hl=en. > > > > -- > > > - Hudsonhttp://www.bestguesstheory.comhttp://twitter.com/HudsonAkridge > > > -- > > You received this message because you are subscribed to the Google Groups > > "Fluent NHibernate" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]<fluent-nhibernate%[email protected]> > > . > > For more options, visit this group at > >http://groups.google.com/group/fluent-nhibernate?hl=en. > > -- > - Hudsonhttp://www.bestguesstheory.comhttp://twitter.com/HudsonAkridge
-- You received this message because you are subscribed to the Google Groups "Fluent NHibernate" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.
