none
Unable to determine valid ordering for custom update RRS feed

  • Question

  • Hello,

    I have some problems with my Entity Model (EF 4.0). I have some custom logic on the database side, so I have to use stored procedures for the updating of the data. The tables look like this:

    ID int PK
    Some custom fields
    ValidFrom datetime
    ValidTo datetime

    I don't delete and don't update the records in the database. Instead of this I set the validity of the record. The validity information are not visible for the EF model. So I have following logic for the update (implemented in STP):

    UPDATE
      @id
      @some_custom_fields

    update T set ValidTo=getdate() where ID=@id
    insert into T @some_custom_fields
    select @@identity as ID

    For this procedure I have defined mapping in the model. I have mapped ID as input parameter and I have mapped output column to ID. Unfortunately this solution does not work - I got an exception 'System.Data.UpdateException: Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values'. When I remove the output mapping, update procedure works, but of course I don't get new ID back, what is not acceptable. I have tried with marking @id as output parameter, but this does not seem to be supported in EF 4.0.

    Do you have any other idea how can I implement this solution?

    Monday, August 8, 2011 10:04 PM

Answers

  • So I've found the solution. Maybe it's not super-elegant, but it works.

    We need to implement some methods. First for MyEntity:

    public partial class MyEntity
    {
      internal void SetNewID(int newID)
      {
        _ID = SetValidValue(newID);
        ReportPropertyChanged("ID");
      }
    }

    I cannot use the original ID property, because ReportPropertyChanging() throws an exception while trying to modify a key property of an existing object.

    In the next step I used funtion import to create a function for my update procedure (thanks for the tip, Gil).

    Finally I had to overwrite the default SaveChanges behavior:

    public partial class MyContext
    {
      public override int SaveChanges(SaveOptions options)
      {
        foreach (var entry in ObjectStateManager.GetObjectStateEntries(EntityState.Modified))
        {
          var o = entry.Entity as MyEntity;
          if (o != null)
          {
            var result = UpdateMyEntity(o.ID, ...);
            o.SetNewID(result.First().GetValueOrDefault(-1));
            entry.AcceptChanges();
            continue;
          }
        }
      }
    }

    Now it works, although I would appreciate any comments and proposals to make it better.

    • Marked as answer by ucel Wednesday, August 10, 2011 9:17 PM
    Wednesday, August 10, 2011 9:16 PM

All replies

  • Hi,

    EF support output parameters. You can read about that here: http://blogs.microsoft.co.il/blogs/gilf/archive/2010/05/09/how-to-retrieve-stored-procedure-output-parameters-in-entity-framework.aspx

    If that doesn't work you can also use EntityClient to run the stored procedure and retrieve the output parameter.


    Gil Fink
    Tuesday, August 9, 2011 4:43 AM
  • Hi,

    thanks for the answer. It's nice to know how to use the output parameters, I will surely use it in my project. Unfortunately that doesn't solve my problem. Can I use this method directly in the model? EF model is the data source for the RIA service, so I don't know to to overwrite default update behavior :(.

    Tuesday, August 9, 2011 9:18 PM
  • So I've found the solution. Maybe it's not super-elegant, but it works.

    We need to implement some methods. First for MyEntity:

    public partial class MyEntity
    {
      internal void SetNewID(int newID)
      {
        _ID = SetValidValue(newID);
        ReportPropertyChanged("ID");
      }
    }

    I cannot use the original ID property, because ReportPropertyChanging() throws an exception while trying to modify a key property of an existing object.

    In the next step I used funtion import to create a function for my update procedure (thanks for the tip, Gil).

    Finally I had to overwrite the default SaveChanges behavior:

    public partial class MyContext
    {
      public override int SaveChanges(SaveOptions options)
      {
        foreach (var entry in ObjectStateManager.GetObjectStateEntries(EntityState.Modified))
        {
          var o = entry.Entity as MyEntity;
          if (o != null)
          {
            var result = UpdateMyEntity(o.ID, ...);
            o.SetNewID(result.First().GetValueOrDefault(-1));
            entry.AcceptChanges();
            continue;
          }
        }
      }
    }

    Now it works, although I would appreciate any comments and proposals to make it better.

    • Marked as answer by ucel Wednesday, August 10, 2011 9:17 PM
    Wednesday, August 10, 2011 9:16 PM