看起来我找不到任何有关于“如何在ORM工具中使用EAV方法”的答案，所以我想在这里试试手气。
假设我有一个Entities表：
ID -> int
Name -> nvarchar(50)
一张Images表：
EntityID -> int
Width -> int
Height -> int
还有一个Songs表：
EntityID -> int
Duration -> decimal(12,3)
我需要向实体添加可扩展的元数据（带有类型信息的未知键值对），以便能够发出如下查询：
找到所有持续时间超过3分钟，并且名称以“The”开头，具有满足以下标准的元数据的歌曲：
HasGuitarSolo设置为trueGuitarSoloDuration大于30秒并按照GuitarSoloDuration降序排序结果。
我不想在数据库中创建HasGuitarSolo、GuitarSoloDuration等列，理想情况下我想将它们存储在类似EAV的模式中，或者是一种不需要提前知道键的替代方案。
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
...
过去我曾经通过在对象上添加一个名为Extra的属性来实现这一点，它是字典或类似数据类型。然后您可以自由地填充它并使用LINQ进行查询。
[EntitiesExtended]
EntitiesExtendedId int
EntitiesExtendedDescription varchar(max)
[Entities_EntitiesExtended]
Entities_EntitiesExtendedId int
EntitiesId int
EntitiesExtendedId int
EntitiesExtendedValue varchar(max)
如果歌曲ID为1，吉他独奏持续34秒，整首歌曲的时长为3分23秒，则可以建模为：
[Entities_EntitiesExtended]
EntitiesId = 1
EntitiesExtendedId = 1
EntitiesExtendedValue = "34"
EntitiesId = 1
EntitiesExtendedId = 2
EntitiesExtendedValue = "203"
[EntitiesExtended]
EntitiesExtendedId = 1
EntitiesExtendedDescription = "GuitarSoloDuration"
[EntitiesExtended]
EntitiesExtendedId = 2
EntitiesExtendedDescription = "Duration"
然后像这样的查询：
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