none
Handling database-populated audit fields in EF RRS feed

  • Question

  • I implement an auditing scheme which, I suspect, is fairly common: On each table I have four columns:

    CreateTS datetime not null default(GETDATE()),

    CreateUID varchar(50) not null default(suser_sname())

    UpdateTS datetime,

    UpdateUID varchar(50)

    For each table, there is a trigger that updates the UpdateTS and UpdateUID whenever an update occurs. User code is never responsible for populating these columns.

     

    Now, enter EF. I have tried various approaches to make this work with EF. To save time, I won't go into detail about the various ways I've wasted time on this issue, but the only way I've been able to get this to work properly is to manually configure each audit column's storage schema generator pattern, e.g.,

    <Property Name="CreateTS" Type="datetime" Nullable="false" StoreGeneratedPattern="Computed"  />

    Note, setting the generator pattern on the conceptual model is NOT SUFFICIENT. This is the only way I've been able to get this to work.

     

    Of course, one can't do this from the designer and must edit the XML directly (which, of course, makes me wonder why even BOTHER with the designer at all since there are so many things that one can't do with it, but I digress.......).

    So, great, it works. I don't get the "cannot convert datetime2 to...blah blah blah" exception and I don't get the "cannot insert null blah blah blah" exception. When the entity is persisted the audit properties on the entity are correctly populated. The HUGE problem though comes when I update the model from the database. All of my manual changes to the storage model are WIPED OUT.

    So either

    1. Someone can suggest an approach to dealing with this that doesn't involve manipulating the storage model that actually works.

    2. I have to completely throw away the designer and do everything by hand

    3. I have to throw away EF and use something else

    4. Audit columns? Who needs audit columns.

     

    ???

    Thursday, June 3, 2010 5:47 PM

All replies

  • You could set the StoreGaneratedPattern=Computed in a partial class using the fluent notation, that way it won't be overwritten if you re-generate the model.
    Tuesday, September 27, 2011 11:51 AM