updating LastChildEntityId property on parent entity when entity added to child collection

This is not a new issue - but it's causing me problems so I need to revisit it.

The problem...
I've got an entity (EntityA) with a 1-Many relationship (to EntityB).  
The fundamental need I have is to be able to update EntityA.Status whenever the most recent (of many) EntityB changes its Status.  Everything else below is working towards that end.
Also, I'd like to expose a property on EntityA which has a single instance of EntityB - specifically the MostRecent, according to a sortorder - usually CreatedOn desc.  So I'd have a readonly property called EntityA.MostRecentEntityB.  
And as an example, BankAccount and TransactionRetrieval.  One BankAccount has many TransactionRetrievals.  A BankAccount has a Status and a TransactionRetrieval has a Status (these are separate enum types).  

Jeremy's previous comment on these use cases:
Certainly for now this isn't something we have a feature for - although this is an interesting idea, so we might have a think about this one after TechEd finishes up :)



A few more details about the use case...
I'm going to keep adding more and more TransactionRetrievals to the BankAccount collection.  I do want to be able to go back and use/display these but for a given BankAccount, 99% of the time I'm only interested in the most recent TransactionRetrieval.  Ideally I'd have something like an EntityHolder<TransactionRetrieval> on BankAccount which eager loads just the most recent TransactionRetrieval.  This is a situation I come across quite regularly, where for a transactional system I'm wanting to eager load just the most recent 'many' entity for a 1-many relationship.
The BankAccount.Status needs to be automatically changed based on the MostRecentTransactionRetrieval.Status (although it can also change of its own accord).  If MostRecentTransactionRetrieval.Status == Error then I want BankAccount.Status to go to error.  



What I've attempted so far is this...

1. I tried and failed to add a 1-1(nullable) association alongside the 1-many association.  This came back with:  Circular associations are not supported by LightSpeed: [TransactionRetrieval.BankAccount and BankAccount.MostRecentTransactionRetrieval]  Mindscape.LightSpeed.LightSpeedException: Circular associations are not supported by LightSpeed: [TransactionRetrieval.BankAccount and BankAccount.MostRecentTransactionRetrieval]
2. Jeremy's suggestion was this:  For now what I would suggest is that you have a property of the entity which holds the "latest value" which you can either manage manually or via a trigger from the collection change as a new entity is added. You can then expose a custom property which calls a UnitOfWork.FindById<TEntity>(yourIdProperty) to return the actually entity back (keeping in mind that this is fetched from the identity map after the first call so you wouldnt need to worry about holding the value yourself).
3. So I tried adding an EntityAdded event handler to BankAccount.TransactionRetrievals in BankAccount.AfterLoad().  The intent was to set the BankAccount.MostRecentTransactionRetrievalId whenever a new TransactionRetrieval was added.  This didn't work though because the TransactionRetrieval.Id hadn't yet been set at the time the BankAccount was saved.  And if I stored the most recent TransactionRetrieval as a transient field on the BankAccount and tried to set it in BankAccount.OnSaving() after the TransactionRetrieval had been assigned an Id, I ran into problems with the order of inserts/updates. *sigh*
4. So at present I have an AFTER INSERT trigger on the TransactionRetrieval table which goes and sets the MostRecentTransactionRetrievalId on the relevant BankAccount.  And in the TransactionRetrieval class I have a setter for Status like so:

if(this.BankAccount.MostRecentTransactionRetrievalId.HasValue &&  this.BankAccount.MostRecentTransactionRetrievalId == this.Id){
  this.BankAccount.UpdateStatusFromTransactionRetrieval(this);
}
This does actually work except that I get the occasional OptimisticConcurrencyException because my db trigger is incrementing the LockVersion on the BankAccount (hooray) and there's obviously some race condition somewhere (boo) where some threads are referencing stale BankAccount entities once the trigger has run.

I think a lot of my problems would go away if I could manage these requirements directly in the model rather than using dirty db triggers.

So, four questions:

1. Is there a way of setting BankAccount.MostRecentTransactionRetrievalId within the model without resorting to db triggers?
2. Is there a better way of chaining setters (TransactionRetrieval.Status -> BankAccount.Status but only for the most recent TransactionRetrieval) than how I've described?
3. Any thoughts on whether you guys would implement some handy dandy convention for easily getting the MostRecent (by createdon) 'many' from a 1-many association.  That would be pretty cool.
4. just cos i'm curious - If I subscribe to an event on a private EntityCollection inside Entity.AfterLoad(), do I need to explicitly unsubscribe from that event - ie make the entity IDisposable?  Or can I just rely on the event reference being to a private field and let the GC tidy up for me?

Thanks
justin

related posts:
http://www.mindscapehq.com/forums/Thread.aspx?PostID=11891
http://www.mindscapehq.com/forums/Thread.aspx?PostID=10910

From initial discussions here we continue to suggest Jeremy's approach.  You can get around the save ordering issue by initially saving with a null or dummy BankAccount.MRTRId, then once the save has completed and the TransactionRetrieval.Id has been assigned, update the BackAccount.MRTRId and save again.  You can wrap both the saves in a transaction so nobody will see the null or dummy MRTRId.

Re 4: no.  Barring other references that keep the entity and EC live, the GC will be able to reclaim both despite the mutual reference.  *does the circular reference dance on COM's grave*

ok, that's great thanks. 

I did think I should be able to do everything through the model - the bit i was missing previously is that I wasn't doing a two-stage save.  I was trying to set BankAccount.MRTRId in BankAccount.OnSaving() - which was where the insert/update ordering was getting out of sync.

The suggested approach does rely however on anyone adding a TransactionRetrieval doing so in the prescribed way (transacted 2-stage save) doesn't it?  I can't find any post-save event which would allow me to define this all on the BankAccount entity and thereby enforce this right in the model rather than in code external to the model (like a repository). 

How about an OnSaved event that runs within the same transaction as the Save?  Then I could do this:

        [Transient]
        TransactionRetrieval _newTransactionRetrieval;

        protected override void AfterLoad() {
            _transactionRetrievals.EntityAdded += OnTransactionRetrievalAdded;
        }

        void OnTransactionRetrievalAdded(object sender, EntityEventArgs<TransactionRetrieval> e) {
            // have to temp store TransactionRetrieval as it hasn't been assigned an id yet.
            _newTransactionRetrieval = e.Entity;
            // temporarily assign an invalid mostRecentTransactionRetrievalId - we'll be putting in the proper one in the OnSaved method.
            // must use Set else BankAccount doesn't know it has been modified
            Set(ref _mostRecentTransactionRetrievalId, Guid.Empty, MostRecentTransactionRetrievalIdField);
        }

        protected override void OnSaved() {
            if (_newTransactionRetrieval != null) {
                // at this point the new TransactionRetrieval should have been assigned an id
                _mostRecentTransactionRetrievalId = _newTransactionRetrieval.Id;
            }
            UnitOfWork.SaveChanges();
        }

I can think of numerous reasons why you wouldn't want to expose a Post-Save hook but it would allow me to prevent any holes where people don't save stuff right - at least in this scenario ;)

For the time being I'll just rely on the fact that only I am writing code that adds TransactionRetrievals to BankAccounts so I know the right way to do it - but is there a way of enforcing this within the model?

cheers

You can kind of do that by overriding UnitOfWork.SaveChanges(bool).  Something like this:

public override void SaveChanges(bool reset) {
  using (TransactionScope ts = new TransactionScope()) {
    var thingsThatNeedFixingUp = this.Where(e => NeedsFixingUp(e).ToList();
    base.SaveChanges(reset);
    if (thingsThatNeedFixingUp.Count > 0) {
      FixEmUp(thingsThatNeedFixingUp);
      SaveChanges(reset);
    }
    ts.Complete();
  }
}

(Warning: code above has never even been compiled, but hopefully gives you the idea!)

Obviously, this is pretty similar to the 'enforce it in a repository' approach, just slightly further down the stack; it could still be bypassed by somebody not using your strong-typed unit of work.

perfect thanks.

that's at the right level for me - if someone isn't using the same strong typed UnitOfWork that's used everywhere else then they deserve inconsistent data... :D