如何从DBEntityEntry中获取外键值

Question

我正试图使用实体框架在保存时创建任何更改的审核日志。到目前为止，它运行得相当好，使用以下代码存储对每个字段所做的所有更改：

foreach (string propertyName in dbEntry.OriginalValues.PropertyNames)
{
// For updates, we only want to capture the columns that actually changed
if (!object.Equals(dbEntry.OriginalValues.GetValue<object>(propertyName), dbEntry.CurrentValues.GetValue<object>(propertyName)))
{
result.Add(new AuditLog()
{
UserID = UserId,
EventDateUTC = changeTime,
EventType = "M",    // Modified
TableName = tableName,
RecordID = primaryKey.ToString(),
ColumnName = propertyName,
OriginalValue = dbEntry.OriginalValues.GetValue<object>(propertyName) == null ? null : dbEntry.OriginalValues.GetValue<object>(propertyName).ToString(),
NewValue = dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null : dbEntry.CurrentValues.GetValue<object>(propertyName).ToString()
});
}
}

我面临的问题是如何获取属于该对象的任何外键的值。例如：我有一个车辆对象，它与一系列查找表(如变速箱、型号等)有关系。如果这些值发生变化，审计表将存储更改后的id，但我想存储实际值。

在这种情况下，有没有办法获得外键值？

Answer 1

Alrighty。。。这是一个老问题，但我花了最后一段时间来解决这个问题，因为我有完全相同的要求。也许有一种更简单的方法，但这是我使用的代码：

您的原始代码，出于我的目的稍作修改(RecordID始终是int)，并调用新方法来计算新值

foreach (string propertyName in dbEntry.OriginalValues.PropertyNames)
{
// For updates, we only want to capture the columns that actually changed
if (!Equals(dbEntry.OriginalValues.GetValue<object>(propertyName), dbEntry.CurrentValues.GetValue<object>(propertyName)))
{
var newVal = getNewValueAsString(dbEntry, tableName, propertyName);
result.Add(new AuditLog
{
UserID = currentUser.ID,
Timestamp = changeTime,
EventType = EventType.Modified,
TableName = tableName,
RecordID = dbEntry.OriginalValues.GetValue<int>(keyName),
ColumnName = propertyName,
OriginalValue = dbEntry.OriginalValues.GetValue<object>(propertyName) == null ? null : dbEntry.OriginalValues.GetValue<object>(propertyName).ToString(),
NewValue = newVal
}
);
}
}

一个名为"IsName"的新属性

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class IsNameAttribute : Attribute
{
}

用IsName属性标记外键模型的"name"属性(注意，如果找不到名为"name"的属性，代码将默认为该属性)

[Required]
[IsName]
public string Name { get; set; }

和重型起重代码

private string getNewValueAsString(DbEntityEntry dbEntry, string tableName, string propertyName)
{
var fkVal = getForeignKeyValue(tableName, propertyName, dbEntry.CurrentValues.GetValue<object>(propertyName));
return fkVal != null ? fkVal.ToString()
: (dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null
: dbEntry.CurrentValues.GetValue<object>(propertyName).ToString());
}
private object getForeignKeyValue(string tableName, string propertyName, object foreignKeyID)
{
// if this property is part of a foreign key, we need to instead look that up and store the value of the
// foreign key
// first get all the foreign keys in the system
var workspace = ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace;
var items = workspace.GetItems<AssociationType>(DataSpace.CSpace);
if (items == null) return null;
var fk = items.Where(a => a.IsForeignKey).ToList();
// now we look into the FK attributes and find that the "To Role" is out current table, and the
// "To Property" is out current property. The underscore is a bit of an assumption that the foreign
// key name built by EF will be ENTITY_BLAH_BLAH
var thisFk = fk.Where(x => x.ReferentialConstraints[0].ToRole.Name.StartsWith(tableName + "_"))
.FirstOrDefault(x => x.ReferentialConstraints[0].ToProperties[0].Name == propertyName);
// if fkname has no results, this is not a foreign key and we are done
if (thisFk == null) return null;
// Now that we know the foriegn key, we need to lookup the Name value in the other table
// find the assembly
var assembly = Assembly.GetCallingAssembly();
// build the type for the foreign key entity
// e.g. if the current entity is Task, and the property is StatusID, we are 
// getting the "TaskStatus" type with reflection
// "User" class is an object in the Models namespace - you could just hardcode the string if you want
var foreignKeyType = assembly.GetType(typeof(User).Namespace + "." +
thisFk.ReferentialConstraints[0].FromRole.GetEntityType().Name);
// get the DbSet, same as: "(new DBContext()).EntityName"
var fkSet = Set(foreignKeyType);
// and find the row in that table
var fkItem = fkSet.Find(foreignKeyID);
// find the first column marked with the "IsName" attribute, otherwise default to "Name"
var nameColProperty = foreignKeyType.GetProperties()
.FirstOrDefault(p => p.GetCustomAttributes(typeof(IsNameAttribute), false).Any());
string nameCol = "Name";
if (nameColProperty != null) nameCol = nameColProperty.Name;
var nameColProperty2 = fkItem.GetType().GetProperty(nameCol);
if (nameColProperty2 == null) return null;
// get the value
var fkValue = nameColProperty2.GetValue(fkItem, null);
// and now, my brain hurts
return fkValue;
}

Answer 2

此解决方案基于@JamesR的答案。

我的目标是使代码更通用，这样它就可以用于连接到不同表的多个外键。

值得注意的改进：

• 我将获取外键列表的代码移到了propertyNameforeach循环之外。由于FK的列表不会根据特定的属性而更改，因此没有理由每次都检索新的列表。如果系统中有许多FK，这可能需要一段时间，所以您不想不必要地重复这个过程。

• 我没有像GetType(typeof(User)那样硬编码特定的类类型，而是使用从FK检索外键表名

  string lookUpTableName = thisFk.ReferentialConstraints[0].FromRole.Name;      
  

  然后，尽管引用的FK属性名称通常是ID，但由于它可能会有所不同，我也检索到了FK属性名：

  string lookUpPropertyName = thisFk.ReferentialConstraints[0].FromProperties[0].Name;  
  

  然后，我使用ObjectContext.ExecuteStoreQuery动态插入表和列名，并检索外键文本值。

• 如果某个特性是FK，我将获得原始值和新值的FK文本值。

---

完整代码：

首先，获取系统中所有外键的列表。

IObjectContextAdapter contextAdapter = ((IObjectContextAdapter)this);
MetadataWorkspace workspace = contextAdapter.ObjectContext.MetadataWorkspace;
var items = workspace.GetItems<AssociationType>(DataSpace.CSpace);
List<AssociationType> FKList = items == null ? null
: items.Where(a => a.IsForeignKey).ToList();

然后，循环浏览特性列表，并在FK存在时将原始值和当前值替换为外键值。

foreach (string propertyName in entry.OriginalValues.PropertyNames)
{
var original = entry.OriginalValues.GetValue<object>(propertyName);
var current = entry.CurrentValues.GetValue<object>(propertyName);
if (FKList != null)
{
GetPossibleForeignKeyValues(tableName, propertyName, ref original, ref current,
FKList, contextAdapter);
}
if ((original == null && current != null) ||
(original != null && !original.Equals(current)))
{
result.Add(new AuditLog()
{
UserID = UserId,
EventDateUTC = changeTime,
EventType = "M",    // Modified
TableName = tableName,
RecordID = primaryKey.ToString(),
ColumnName = propertyName,
OriginalValue = original != null ? original.ToString() : "NULL",
NewValue = current != null ? current.ToString() : "NULL"
});
}
}

以下是实际的外键查找代码：

private void GetPossibleForeignKeyValues(string tableName, string propertyName,
ref object originalFKValue, ref object newFKValue,
List<AssociationType> FKList, IObjectContextAdapter contextAdapter)
{
// If this property is part of a foreign key, look up and set the FKValue to the text
// value of the foreign key. Otherwise, just leave the FKValue alone.
// Look into the FK attributes and find that the "To Role" is out current table,
// and the "To Property" is out current property.
AssociationType thisFk = FKList.FirstOrDefault(x =>
tableName.Contains(x.ReferentialConstraints[0].ToRole.Name)
&& propertyName.Contains(x.ReferentialConstraints[0].ToProperties[0].Name));
// If fkname has no results, this is not a foreign key and we are done.
if (thisFk != null)
{
// Now that we know the foriegn key, look up the Name value in the other table.
string lookUpTableName = thisFk.ReferentialConstraints[0].FromRole.Name;
string lookUpPropertyName = thisFk.ReferentialConstraints[0].FromProperties[0].Name;
//Assuming the FK column name is "Name".
//Use the idea in @JamesR's solution or some sort of LookUp table if it is not.
string commandText = BuildCommandText("Name", lookUpTableName, lookUpPropertyName);
originalFKValue = contextAdapter.ObjectContext
.ExecuteStoreQuery<string>(commandText, new SqlParameter("FKID", originalFKValue))
.FirstOrDefault() ?? originalFKValue;
newFKValue = contextAdapter.ObjectContext
.ExecuteStoreQuery<string>(commandText, new SqlParameter("FKID", newFKValue))
.FirstOrDefault() ?? originalFKValue;
}
}

这是我用来构建SQL命令文本的方法：

private string BuildCommandText(string columnName, string lookUpTableName, 
string lookUpPropertyName)    
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT ");
builder.Append(columnName);
builder.Append(" FROM ");
builder.Append(lookUpTableName);
builder.Append(" WHERE ");
builder.Append(lookUpPropertyName);
builder.Append(" = @FKID");
//The result query will look something like:
//SELECT ColumnName FROM TableName WHERE PropertyName = @FKID
return builder.ToString();
}