Using NHibernate with an EAV data model

I'm trying to leverage NH to map to a data model that is a loose interpretation of the EAV/CR data model.

I have most of it working but am struggling with mapping the Entity.Attributes collection.

Here are the tables in question:

--------------------
| Entities         |
--------------------
| EntityId  PK     |-|
| EntityType       | |
-------------------- |
         -------------
         |
         V
--------------------
| EntityAttributes |    ------------------    ---------------------------
--------------------    | Attributes     |    | StringAttributes        |
| EntityId  PK,FK  |    ------------------    ---------------------------
| AttributeId  FK  | -> | AttributeId PK | -> | StringAttributeId PK,FK |
| AttributeValue   |    | AttributeType  |    | AttributeName           |
--------------------    ------------------    ---------------------------

The AttributeValue column is implemented as an sql_variant column and I've implemented an NHibernate.UserTypes.IUserType for it.

I can create an EntityAttribute entity and persist it directly so that part of the hierarchy is working.

I'm just not sure how to map the EntityAttributes collection to the Entity entity.

Note the EntityAttributes table could (and does) contain multiple rows for a given EntityId/AttributeId combination:

EntityId AttributeId AttributeValue
-------- ----------- --------------
1        1           Blue
1        1           Green

StringAttributes row looks like this for this example:

StringAttributeId AttributeName
----------------- --------------
1                 FavoriteColor

How can I effectively map this data model to my Entity domain such that Entity.Attributes("FavoriteColors") returns a collection of favorite colors? Typed as System.String?

From stackoverflow

• Hi there, Id be very interested to see how you implemented this