How to store extensible metadata in an ORM-friendly way in .NET?

It seems I am unable to find any answers to "how to use EAV approach with ORM tools" question, so I'll try my luck here.

Suppose I have an Entities Table:

ID -> int
Name -> nvarchar(50)

An Images Table:

EntityID -> int
Width -> int
Height -> int

And a Songs Table:

EntityID -> int
Duration -> decimal(12,3)

I need to add extensible metadata to the entities (unknown key-value pairs with type info), so that I'm able to issue queries like:

Find me all the Songs that have a Duration longer than 3 minutes, wit开发者_StackOverflowh a Name starting with 'The', with metadata fulfilling these criteria:

• HasGuitarSolo is set to true
• GuitarSoloDuration is greater than 30 seconds

And sort the results on GuitarSoloDuration in descending order.

I don't want to create HasGuitarSolo, GuitarSoloDuration, etc. columns in the database, Ideally I would like to store them in an EAV-like schema, or an alternative schema that doesn't require a knowledge of the keys up front.

Add a column to the tables called 'metadata' and put XML in it. SQL server allows you to look at a blob full of XML as if it were additional columns (with limitations).

For ORM, it depends on how your object is structured.

• Infinitely Customizable metadata items: you put the name-value pairs from the XML in a collection. If your ORM won't allow this, put it straight into a string property, the setter could parse it into an XML doc (or faster object if you need speed). Getter would return the string. Then a separate property 'MetaDataItem(ItemName->string)' that is not ORM'd would read values from the metadata list and update/add them with its setter.
• Metadeta is hardcoded properties - map them using a query that pulls them from the XML.
• Hybrid of the two - hardcoded properties for some items - have their setters/getters call MetaDataItem.
• Reverse hybrid if certain properties need to be directly stored (esp if you are sorting large lists on them): you have to hardcode properties for that metadata with their own private members, but don't ORM those properties. Hardcoded the saving/loading of those values into the string property that is ORM'd, and if you want to be able to update those hardcoded metadata items from the MetaDataItem property as well, hardcode them it that spot, too!

If you have a whole bunch of hardcoded metadata properties, in addition to the infinite amount, you can easy the crud in the XML property and MetaDataItem property with lists and reflection. If all are hardcoded you can still use the XML text property to load/save them, map that one property, not the others.

Sort them with a LINQ query on the object.

I did this with great success and with each bullet coded, stuff worked better and better! 2005/.Net 1.1 so no ORM, LINQ, my first VB.net program etc. But other developers did use SQL server's XML querying to read my XML. Of course I forgot about this, changed it, and tripped them up :-(

Here are snippets. Key of this all is: ORM friendly = ORM some properties, not others; Allow consumers to use other properties, but not some. If your ORM doesn't allow such ala-carte property selection, you might be able to use inheritance or composition to trick it. Sorry I don't have time to post full example for your purpose.

Well I don't have the code sample here, at home. I will edit and paste it in tommorrow.

EDIT as promised, here's the code snippet:

   Public Property ItemType(ByVal stTagName As String) As String
        Get
            Dim obj As Object
            obj = Me.lstMemberList.Item(stTagName)
            If Not obj Is Nothing Then
                Return CType(obj, foDataItem).Type
            End If
        End Get
        Set(ByVal Value As String)
            Dim obj As Object
            obj = Me.lstMemberList.Item(stTagName)
            If Not obj Is Nothing Then
                CType(obj, foDataItem).Type = Value
            End If
        End Set
    End Property

    Public Function ItemExists(ByVal stTagName As String) As Boolean
        Return Me.lstMemberList.ContainsKey(stTagName)
    End Function

    Public Property ItemValue(ByVal stTagName As String, Optional ByVal Type4NewItem As String = "") As String
        Get
            Dim obj As Object
            obj = Me.lstMemberList.Item(stTagName)
            If obj Is Nothing Then
                Dim stInternalKey As String = ""
                Try
                    stInternalKey = Me.InternalKey.ToString
                Catch
                End Try
                If stTagName <> "InternalKey" Then '' // avoid deadlock if internalkey errs!
                    Throw New ApplicationException("Tag '" & stTagName & _
                      "' does not exist in FO w/ internal key of " & stInternalKey)
                End If
            Else
                Return CType(obj, foDataItem).Value
            End If
        End Get
        Set(ByVal Value As String)
            '' // if child variation form...
            If bLocked4ChildVariation Then
                '' // protect properties not in the list of allowed updatable items 
                If Not Me.GetChildVariationDifferentFields.Contains(stTagName) Then
                    Exit Property
                End If
            End If
            '' // WARNING - DON'T FORGET TO UPDATE THIS LIST OR YOU WILL NEVER FIND THE BUG!
            Select Case stTagName
                Case "PageNum"
                    _PageNum = CInt(Value)
                Case "Left"
                    _Left = CInt(Value)
                Case "Top"
                    _Top = CInt(Value)
                Case "Width"
                    _Width = CInt(Value)
                Case "Height"
                    _Height = CInt(Value)
                Case "Type"
                    _Type = String2Type(Value)
                Case "InternalKey"
                    _InternalKey = CInt(Value)
                Case "UniqueID"
                    _UniqueID = Value
            End Select
            Static MyError As frmErrorMessage
            Dim obj As Object
            If Me.lstMemberList.ContainsKey(stTagName) Then
                Dim foi As foDataItem = CType(Me.lstMemberList.Item(stTagName), foDataItem)
                If foi.Type = "Number" Then
                    Value = CStr(Val(Value))
                End If
                If foi.Value <> Value Then
                    If bMonitorRefreshChanges Then
                        LogObject.LoggIt("Gonna Send Update for Change " & stTagName & " from " & _
                          foi.Value & " to " & Value)
                        If Not Me.FormObjectChanged_Address Is Nothing Then
                            FormObjectChanged_Address(Me, stTagName)
                        End If
                    End If
                End If
                foi.Value = Value
            Else
                Me.lstMemberList.Add(stTagName, New foDataItem(Value, Type4NewItem))
                Me.alOrderAdded.Add(stTagName)
            End If
        End Set
    End Property


  Public Function StringVal(ByVal st As String, Optional ByVal stDefault As String = "") As String
        Try
            StringVal = stDefault
            Return CType(Me.ItemValue(st), String)
        Catch ex As Exception
            Dim bThrowError As Boolean = True
            RaiseEvent ConversionError(ex, "String=" & Me.ItemValue(st), Me, st, bThrowError)
            If bThrowError Then
                LogObject.LoggIt("Error setting tag value in fo.StringVal: " & st)
                Throw New Exception("Rethrown Exception getting value of " & Me.ID & "." & st, ex)
            End If
        End Try
    End Function
    Public Function IntVal(ByVal st As String, Optional ByVal iDefault As Integer = 0) As Integer

    ...

 '' // 'native' values - are normal properties instead of XML properties, which 
    '' // actually makes it harder to deal with b/c of extra updates to sync them, BUT,
    '' // worth it - as they are read much more than written (sorts, wizard builds,
    '' // screen redraws, etc) I can afford to be slow when writing to them, PLUS
    '' // retain the benefits of referencing everything else via ItemValue, PLUS
    '' // these are just the more 'popular' items. 
    Private _Top As Integer
    Private _Left As Integer
    Private _Width As Integer
    Private _Height As Integer
    Private _PageNum As Integer
    Private _Type As pfoType
    Private _InternalKey As Integer
    Private _UniqueID As String

    Public Sub SetNativeValuesFromMyXML()
        _Top = CInt(CType(Me.lstMemberList("Top"), foDataItem).Value)
        _Left = CInt(CType(Me.lstMemberList("Left"), foDataItem).Value)
        _Width = CInt(CType(Me.lstMemberList("Width"), foDataItem).Value)
        _Height = CInt(CType(Me.lstMemberList("Height"), foDataItem).Value)
        _PageNum = CInt(CType(Me.lstMemberList("PageNum"), foDataItem).Value)
        _Type = String2Type(CType(Me.lstMemberList("Type"), foDataItem).Value)
        _InternalKey = CInt(CType(Me.lstMemberList("InternalKey"), foDataItem).Value)
        _UniqueID = CType(Me.lstMemberList("UniqueID"), foDataItem).Value
    End Sub

    Public Property Top() As Integer
        Get
            Return _Top '' // CInt(ItemValue("Top"))
        End Get
        Set(ByVal Value As Integer)
            ItemValue("Top") = Value.ToString
        End Set
    End Property

    Public Property Left() As Integer
        Get
            Return _Left '' //CInt(ItemValue("Left"))
        End Get

    ...

I have done this in the past by putting an Extra property on my object, which is a Dictionary or similar data type. You can then freely populate this with data, and query using LINQ.

You could add a couple of tables like:

[EntitiesExtended]
EntitiesExtendedId int
EntitiesExtendedDescription varchar(max)

[Entities_EntitiesExtended]
Entities_EntitiesExtendedId int
EntitiesId int
EntitiesExtendedId int
EntitiesExtendedValue varchar(max)

So if song id 1 had a guitar solo of 34 seconds and lasted for 3 minutes and 23 seconds it could be modeled as:

[Entities_EntitiesExtended]
EntitiesId = 1
EntitiesExtendedId = 1
EntitiesExtendedValue = "34"

EntitiesId = 1
EntitiesExtendedId = 2
EntitiesExtendedValue = "203"

[EntitiesExtended]
EntitiesExtendedId = 1
EntitiesExtendedDescription = "GuitarSoloDuration"

[EntitiesExtended]
EntitiesExtendedId = 2
EntitiesExtendedDescription = "Duration"

And then queries like:

select * from Entities e 
join Entities_EntitiesExtended eee on e.id = eee.id 
join EntitiesExtended ee on eee.id = ee.id
where EntitiesExtendedDescription = "GuitarSoloDuration"
and cast(EntitiesExtendedValue as int) > 30 

select * from Entities e 
join Entities_EntitiesExtended eee on e.id = eee.id 
join EntitiesExtended ee on eee.id = ee.id
where EntitiesExtendedDescription = "Duration"
and cast(EntitiesExtendedValue as int) > 180 

You can store the data in SQL Server and using LINQ to SQL ORM.

Updated: You can also take a look at NH. LLBL, this is a ORM/Generator, your entities will have a lot of code pre-generated, and it starts from the database.