Mapping a class with multiple collection properties containing the same object type

[David McClelland]

Our system generates emails with user-chosen lists of To / CC / BCC
contacts. I wanted to store them as follows in our SQL Server
database, here's the simplified database table structure:

CREATE TABLE [Contact] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL,
[Name] [varchar] (100) NOT NULL,
[EmailAddress] [varchar] (100) NOT NULL,
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ([ContactID])
)

CREATE TABLE [Email] (
[EmailID] [int] IDENTITY (1, 1) NOT NULL,
[Subject] [varchar] (500) NOT NULL,
[Message] [text] NULL,
[DateSent] [datetime] NOT NULL,
CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED ([EmailID])
)

CREATE TABLE [EmailContact] (
[EmailID] [int] NOT NULL,
[ContactID] [int] NOT NULL,
[Type] [varchar] (4) NOT NULL,
CONSTRAINT [PK_EmailContactList] PRIMARY KEY CLUSTERED
(
[EmailID],
[ContactID],
[Type]
),
CONSTRAINT [FK_EmailContact_Contact] FOREIGN KEY ([ContactID])
REFERENCES [Contact] ([ContactID]),
CONSTRAINT [FK_EmailContact_Email] FOREIGN KEY ([EmailID])
REFERENCES [Email] ([EmailID])
)

This to me looks like a case of a many-to-many relationship between
the Email and Contact objects. However, I would like the Email domain
object to have 3 separate IList properties for the contacts of each
list (To / CC / BCC) such that I could enable the following code to
work :

testEmail.ToContacts.Add(contact1)
testEmail.CCContacts.Add(contact2)
testEmail.BCCContacts.Add(contact3)

Can this be done without adding an additional domain object
(EmailContact)? Do I need to use two many-to-one relationships with an
additional domain object like Billy McCafferty mentions here:
http://devlicio.us/blogs/billy_mccafferty/archive/2008/07/11/when-to-use-many-to-one-s-vs-many-to-many-with-nhibernate.aspx

Also, how would I represent this my NHibernate mapping file?

By the way, I first tried posting this on Stack Overflow, but I'm not
sure how much expertise with NHibernate lurks on that site! :-)

[Jon Stelly]

I think from an NHibernate perspective, splitting out the EmailContact
table into EmailToContact, EmailCCContact, and EmailBCCContact tables
is probably the cleanest. I know that changes your table structure,
so an (almost equally clean, but I'd say not quite) alternative would
be to add a "where" attribute to your 3 bags. Something like:

where="[Type] = 'TO'"

There might be a better way than this, I don't have a lot of
experience with the more exotic relationship mappings, indexed-many-to-
many, etc...


On Nov 24, 10:36 am, David McClelland <davidrmcclell...@gmail.com>
wrote:

> additional domain object like Billy McCafferty mentions here:http://devlicio.us/blogs/billy_mccafferty/archive/2008/07/11/when-to-...

[Tuna Toksöz]

Well, first thing comes into my mind is to have different columns in the emailcontact table as foreign keys. Then map your bags specifying those columns.
Be aware that db columns should allow null values.

--
Tuna Toksöz
http://www.tunatoksoz.com

Typos included to enhance the readers attention!

[Tuna Toksöz]

And yes you don't need to specify another domain obkect & database table.

[Tuna Toksöz]

and i hope it works.

[David McClelland]

Thanks Jon, I think we're going with your suggestion.

- David McClelland

[Sean Fuhrmann]

Quick question...

Is there a way to have NHibernate automatically connect, then disconnect for every read, without calling Reconnect, Disconnect, etc? We are using connection pooling and do not want to keep the connection alive for a long period of time.

Thanks,

Sean

[Ayende Rahien]

It is doing this by default.

[Fabio Maulo]

You are calling Disconnect and Reconnect ?

Seriously ?

2008/11/25 Sean Fuhrmann <t...@agentimediaservices.com>

--
Fabio Maulo

[Sean Fuhrmann]

No no, I wasn’t…  I was just asking the question…  I figured it was, but I’ve been having problems with connections leaking so I am trying to track down some answers…

[Sean Fuhrmann]

Basically, we were leaking connections and I found a scenario in our app where we were not closing our NHibernate session…  So I guess I figured it wasn’t closing the connection each time…

[Fabio Maulo]

Sorry for previous answer... Italian dinner in Argentina ;)

BTW, are you not closing your session ?

That is not a pattern; It is a "TIME BOMB"

2008/11/25 Sean Fuhrmann <t...@agentimediaservices.com>

--
Fabio Maulo

[Sean Fuhrmann]

J No problem…  I am bad at explain my question\situation sometimes…

 

Well, I found a scenario where the session was not getting closed.  Once I fixed that I am not having connection pool problems any more.  But I am curious as to why, even with the close, NHibernate holds on to connections if it is indeed releasing the connection after each call to the database.

[Ayende Rahien]

Probably because you also had an open TX?

[Sean Fuhrmann]

Hmm…  I really don’t think so, I was only doing a single HQL Query (with .List()) in the session…  I can dig some more to verify that, but…

[Fabio Maulo]

check this list for this thread

"Transparant Lazy Loading in Long Sessions"

.....and trust ;)

--
Fabio Maulo

[Sean Fuhrmann]

Sounds great… thanks guys!  I appreciate it…

 

Another question coming soon J

 

From: nhu...@googlegroups.com [mailto:nhu...@googlegroups.com] On Behalf Of Fabio Maulo
Sent: Tuesday, November 25, 2008 8:56 PM
To: nhu...@googlegroups.com
Subject: [nhusers] Re: Database Connections..

 

check this list for this thread