Pivoting rows into properties/columns with NHibernate

Question

What would be the best way using to model the following table structure into the 'pivoted' domain model below, using NHibernate?

Mapping Attribute is trivial; it's really the pivot of rows to properties/virtual columns that I'm unsure about.

Database Structure

Attributes table

Id      Name                Type
Attr1   Some Attribute 1    Bool
Attr2   Some Attribute 2    Bool
Attr3   Some Attribute 3    Bool

Ad table

Id  Name
Ad1 Some ad 1

AdAttributes table

Id    AdId  AttributeId Value
100   Ad1   Attr1       true
101   Ad1   Attr2       false
102   Ad1   Attr3       true

Desired NHibernate object model

class Attribute{
    public int Id {get;set;}
    public Type TheType {get;set;}
    public string Name {get;set;}
}

class AdAttribute{
    public int Id{get;set;}
    public Ad Ad{get;set;}
    public Attribute Attribute{get;set;}

    public bool Value{get;set;}
}

class Ad{
    ...

    class Attributes{
        public AdAttribute Attr1{get;set;}
        public AdAttribute Attr2{get;set;}
        public AdAttribute Attr3{get;set;}
    }

    Attributes TheAttributes{get; protected set;}
}

The table structure's not set in stone, but the goal is for the Attribute's to not be stored as columns on the Ad. Different Ad type's will have different attributes, but they are not dynamically changing, ie ad type 1 will always have 10 attributes, ad type 2 will always have 5 attributes, etc.

Raw NHibernate HBM answer preferred, but Fluent mappings are ok

have you considered IDictionary<string, object>? NHibernate can serialise objects to 2 columns, but i never tried — Firo
I think perhaps that is the route I will have to go down, but I'm not quite sure it will work with the structure above — geoffreys
when the attributes of each adtype is fix why not have a table for each adtype to hold the specific attributes? (table-per-class inheritance) — Firo

geoffreys geoffreys · Accepted Answer · 2012-10-19T00:04:04

Ultimately we decided to use a different design to remove this unnecessary complexity.

Firo's point regarding NHibernate's IDictionary support is a good one, however and would likely have been the solution

Pivoting rows into properties/columns with NHibernate

Database Structure

Desired NHibernate object model

1 Answers