Using NHibernate with an EAV data model

Question

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?

Necaise

If you plan to find entities by attribute values, I'm not sure if sql_variant is working properly. You should try this. – Bill

Answer 1

here goes

class Entity
{
    public virtual int Id { get; set; }

    internal protected virtual ICollection<EntityAttribute> AttributesInternal { get; set; }

    public IEnumerable<T> Attributes<T>(string attributeName)
    {
        return AttributesInternal
            .Where(x => x.Attribute.Name == attributeName)
            .Select(x => x.Value)
            .Cast<T>();
    }
}

class EntityAttribute
{
    public virtual Attribute Attribute { get; set; }

    public virtual object Value { get; set; }
}

class EntityMap : ClassMap<Entity>
{
    public EntityMap()
    {
        HasMany(e => e.AttributesInternal)
            .Table("EntityAttributes")
            .KeyColumn("EntityId")
            // EntityAttribute cant be an Entity because there is no real Primary Key
            // (EntityId, [AttributeId] is not unique)
            .Component(c =>
            {
                c.References(ea => ea.Attribute, "AttributeId").Not.LazyLoad();
                c.Map(ea => ea.Value, "AttributeValue").CustomType<VariantUserType>();
            });
    }
}

Dropsical