I am trying to come up with a reasonable EF1 entity model that maps to our SQL Server database schema and have hit a couple of issues related to a requirement that all tables in our database have fields for holding the last user of our application (not database user) to update the record and a timestamp of the last update. Let's call these fields 'UpdatedBy' and 'UpdateTimestamp'.
In our database schema we have join tables used for many-to-many relationships. If it wasn't for the presence of the two audit fields on the join table, Entity Framework would handle relationships like this for us. Because of the presence of the audit fields that are not involved in the join between the two tables, the EF designer creates an entity that maps to the join table. If possible I'd like to eliminate the need for this entity to simplify working with the model in our application code but have not been able to come up with a good way to do it.
We also have several tables involved in a table-per-type inheritance hierarchy. ie. we have an ancestor table that contains values for properties common to several entity types and several tables to hold values for properties that apply to specific subclass entities. Each of these tables has the two audit fields. It's okay for our purposes if changing a property stored in the table for either the base or descendant entity updates the audit field in both tables. I have been unable to find a way to map one entity property to fields in two tables. Is this possible?
The audit properties are currently defined separately in each entity in the model, except in subclass entities as described above. I have been experimenting with creating a common Audit baseclass as described in Zeeshan Hirani's Entity Framework Learning Guide but creating a superclass just for this smells a bit to me and doesn't by itself solve either of the above issues.
What is the best way to map these audit properties in EF in a way that addresses these issues? I guess we could do it with stored procedures but I'd like to avoid that if possible.