You could go that route, but that's messy, and in my experience will
only end in tears for you :)
If it's a legacy database, and you can't change the schema (otherwise
you'd obviously be normalizing it a bit better), then alternatively
you can create a Formula mapping:
Map(x => x.County).FormulaIs("SELECT Counties.CountyID FROM Projects
this_ left outer join Counties county1_ on =county1_.StateAlphaCode
AND =county1_.CountyName");
Something like that, I may not have the SQL right, as I'm just going
off what you posted. You'll obviously need to add County to your
Project class, otherwise there's no point to your scenario (you'd have
to have done that as well, since your reference mapping needed to be
x.County). There's a caveat with this though. If the County doesn't
exist, you'll get a new Empty County object, instead of a null like
you would with a typical one-to-many.
Check out:
http://thoughtspam.spaces.live.com/blog/cns!253515AE06513617!458.entry
for more information. There's two additional followup articles listed
at the bottom of his post which give you some hbm and class examples
of how to do it.
I'm not 100% on whether or not this will work with Fluent,
theoretically it shouldn't care, but you might hit a bug that's Fluent
specific, while it works for you if you hardcode the .hbm.xml mapping.
If you encounter this, let me know, and I'll check into it and get you
out a patch.
On Mar 30, 2:59 pm, Dallas <[email protected]> wrote:
> Hudson,
>
> Thanks for your suggestions. I had considered this before, but in my
> case this won't work because we have a UserCounties table that
> determines which counties a User has access (it is used in a Many to
> Many relationship, so this table is not mapped to an object). This
> table actually has the CountyID in it, but does not contain the
> StateName or CountyName, so I am forced to use CountyID for the
> primary key.
>
> So, is my only option to create a new County object, say ProjectCounty
> that maps by compositeID, and keep the existing County object? It
> would break a few LINQ queries, but I can refactor them to compare
> against StateName and CountyName instead of just ID.
>
> Thanks again,
>
> -Dallas
>
> On Mar 30, 3:05 pm, Hudson Akridge <[email protected]> wrote:
>
> > Technically, you can't. Not directly like that. This is a NHibernate
> > Limitation, not a Fluent limitation.
>
> > What you need to do to get around it, is to set up a CompositeID
> > mapping in your CountyMap, this would replace your "CountyID". The
> > method is .UseCompositeID() on the ClassMap for CountyMap, and map
> > your StateCode and Name as the fields for the composite key.
>
> > The reason is simple. A one-to-many is the reverse side of the many-to-
> > one. This relationship, inherently through NHibernate, will use the ID
> > mapping of the entity containing the one-to-many when saving/looking
> > up data from the many-to-one side.
>
> > What your CountyMap is technically missing, is:
> > HasMany(x=> x.Projects)
> > With whatever the name of your collection is. If you don't want the
> > collection of projects on your County class, then you can still do
> > this, but you'll definitely need the CompositeID mapping instead of ID
> > mapping for County.
>
> > On Mar 30, 1:09 pm, Dallas <[email protected]> wrote:
>
> > > Hey guys,
>
> > > I have an issue where our legacy database model isn't exactly
> > > normalized- we have a list of Projects that need a County associated
> > > with them. In the Counties database we have a CountyID for the
> > > Primary Key, but in the Projects database we do not-- I need to map
> > > the County and Location_State columns from the Projects database
> > > against CountyName and StateAlphaCode in the Counties database.
>
> > > In ProjectMap.CS I have:
> > > References<County>(x => x.County, "Location_State").PropertyRef(x =>
> > > x.StateCode);
>
> > > CountyMap if you need it:
> > > public CountyMap()
> > > {
> > > WithTable("Counties");
>
> > > Id(x => x.ID, "CountyID")
> > > .WithUnsavedValue(0)
> > > .GeneratedBy.Assigned();
>
> > > Map(x => x.Name, "CountyName");
> > > Map(x => x.StateCode, "StateAlphaCode");
> > > }
>
> > > This is the generated SQL:
> > > FROM Projects this_ left outer join Counties county1_ on
> > > this_.Location_State=county1_.StateAlphaCode
>
> > > I need it to have the CountyName in there too, so this is what I need
> > > it to generate this:
> > > FROM Projects this_ left outer join Counties county1_ on
> > > this_.Location_State=county1_.StateAlphaCode AND
> > > this_.County=county1_.CountyName.
>
> > > I need a way to do 2 PropertyRef, but that fails on a Reference
> > > already defined. So the question is, how to I map a Many to 1 against
> > > an object where I'm NOT using its ID?
>
> > > Thank you in advance,
>
> > > -Dallas
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
