How can you get foreign key values from a DBEntityEntry?

Question

I'm trying to create an audit log of any changes at point of save using Entity Framework. So far I have it working fairly well, storing all changes made to each field using the code below:

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()
        });
    }
}

The issue I'm facing is how to get values for any foreign keys that belong to this object. For example: I have a vehicle object that has relationships to a series of lookup tables, such as gearbox, model etc. If these values change the audit table will store the changed id, but I want to store the actual value.

Is there a way of getting the foreign key value in this situation?

Answer 1

This solution is based on @JamesR's answer.

My goal was to make the code more generic so it could be used for multiple foreign keys connecting to different tables.

Improvements worth noting:

• I moved the code that gets the list of foreign keys outside of the propertyName foreach loop. Since the list of FKs doesn't change based on the specific property, there is no reason to retrieve a new list every time. If there are many FKs in the system, this can take a while, so you don't want to repeat the process unnecessarily.

• Instead of hard-coding a specific class type like GetType(typeof(User), I retrieved the foreign key table name from the FK using:

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

  Then, although the referenced FK property name will usually be ID, since it can vary, I retrieved the FK property name as well:

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

  I then used ObjectContext.ExecuteStoreQuery to dynamically plug in the table and column name and retrieve the foreign key text value.

• If a property is a FK, I get the FK text value for both the original a new value.

---

Complete code:

First, get a list of all the foreign keys in the system.

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();

Then, loop through the list of properties and replace the original and current values with the foreign key values when a FK exists.

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"
        });
    }
}

Here is the actually foreign key finding code:

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;

    }
}

This is the method I used to build the SQL CommandText:

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();
}

Answer 2

Alrighty... this is an old question but I've spent the last while working this out because I had the exact same requirements. Maybe there is an easier way, but here's the code I used:

Your original code, slightly modified for my purposes (RecordID is always an int), and calling the new method to calculate the new value

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
                                }
                        );
                }
            }

A new attribute called "IsName"

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

}

Marking the "name" property of foreign key models with the IsName attribute (note the code will default to a property called "Name" if it doesn't find one)

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

And the heavy lifting code

 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;
    }