How to insert or update a hierarchical structure using LINQ? RRS feed

  • General discussion

  • Hello,

    I have a data structure that resembles a workflow. It has a table called "Status" referenced to itself with a one-to-many relationship.... so, a status may have 1 or many status that follow it.

    The problem I am having is when I insert a new record.

    I filled the entitities and relations this way:

                    using (GamaDataContext context = new GamaDataContext())
                        bool nuevo = grpForm.Tag == null;
                        var estado = new Estado();
                        estado.ID = nuevo ? 0 : ((Estado)grpForm.Tag).ID;
                        estado.Descripcion = txtDescripcion.Text.Trim();
                        estado.Nombre = txtNombre.Text.Trim();
                        estado.Ambito = (string)cmbAmbito.SelectedValue;
                        List<Estado> siguientes = GetSelectedStatuses(lstSiguientes);
                        foreach (Estado siguiente in siguientes)
                            EstadoSiguiente es = new EstadoSiguiente { EstadoProximo = siguiente };
                        if (nuevo)
                            estado.CreatedAt = DateTime.Now;
                            estado.UpdatedAt = DateTime.Now;
                            estado.ID = ((Estado)grpForm.Tag).ID;
                            estado.CreatedAt = ((Estado)grpForm.Tag).CreatedAt;
                            context.Refresh(System.Data.Linq.RefreshMode.KeepCurrentValues, estado);

    Look at the foreach loop... it is filling the status that follow the status that is saved.

    The problem I see is because this is a recursive structure, when SubmitChanges is called hangs the application because it is committing the data for ever...

    for example, the current record is "estado".. the status that follows is estado.EstadoSiguientes[0].EstadoProximo,. which is the Estado entity as well.. and estado.EstadoSiguientes[0].EstadoActual is the record being saved.... so I can do estado.EstadoSiguientes[0].EstadoActual.EstadoSiguientes[0] for ever.. I think that is why LINQ hangs.

    This is the XML for the dbml file:

      <Table Name="dbo.ESTADO" Member="Estados">
        <Type Name="Estado">
          <Column Name="EST_ID" Member="ID" Storage="_EST_ID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
          <Column Name="EST_NAME" Member="Nombre" Storage="_EST_NAME" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
          <Column Name="EST_DESCRIPTION" Member="Descripcion" Storage="_EST_DESCRIPTION" Type="System.String" DbType="VarChar(512)" CanBeNull="true" />
          <Column Name="EST_SCOPE" Member="Ambito" Storage="_EST_SCOPE" Type="System.String" DbType="Char(3) NOT NULL" CanBeNull="false" />
          <Column Name="EST_ORDERING" Member="Orden" Storage="_EST_ORDERING" Type="System.Int32" DbType="Int" CanBeNull="true" />
          <Column Name="EST_CREATED_AT" Member="CreatedAt" Storage="_EST_CREATED_AT" Type="System.DateTime" DbType="DateTime NOT NULL" CanBeNull="false" />
          <Column Name="EST_UPDATED_AT" Member="UpdatedAt" Storage="_EST_UPDATED_AT" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
          <Column Name="EST_DELETED_AT" Member="DeletedAt" Storage="_EST_DELETED_AT" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
          <Association Name="Estado_EstadoSiguiente" Member="EstadoSiguientes" Storage="_ESTADO_SIGUIENTEs" ThisKey="ID" OtherKey="EstID" Type="EstadoSiguiente" />
          <Association Name="Estado_EstadoSiguiente1" Member="EstadoAnteriores" Storage="_EstadoSiguientes" ThisKey="ID" OtherKey="EstSigID" Type="EstadoSiguiente" />
      <Table Name="dbo.ESTADO_SIGUIENTE" Member="EstadoSiguientes">
        <Type Name="EstadoSiguiente">
          <Column Name="EST_SIG_ID" Member="EstSigID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
          <Column Name="EST_ID" Member="EstID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
          <Association Name="Estado_EstadoSiguiente" Member="EstadoActual" Storage="_Estado" ThisKey="EstID" OtherKey="ID" Type="Estado" IsForeignKey="true" />
          <Association Name="Estado_EstadoSiguiente1" Member="EstadoProximo" Storage="_Estado1" ThisKey="EstSigID" OtherKey="ID" Type="Estado" IsForeignKey="true" />

    How can I do it?

    Thanks a lot in advance


    Powered by C++

    Saturday, March 31, 2012 1:54 AM

All replies

  • Greetings,

    I couldn't understand the naming you followed but i will give you my experience with the Workflow concept that if you applied it, it will work, and i hope that it will help you

    1- each table that you need to enter it to a work flow must contains a code for example employee has a vacations ( "V01-EmployeeNumber",V02-EmployeeNumber" and so on), contracts ("C01-EmployeeNumber","C02-EmployeeNumber")

    2- Workflow Status ( Pending 0 , InProgress 1,Rejected 2, Approved 3) // that will be used in the workflow table to mention the workflow stage

    3- Workflow table ( ID, CreatedOn,CreatedBy,Status,Code,IsActive)

    4- Signatory Status (Pending 0,Rejected 2, Approved 3) // that will be used by the Signatory table

    5- Signatory table (ID,Workflow,Status,InitiatedForOn,InitiatedForBy,ModifiedOn,ModifiedBy,Note,IsActive)

    6- Employee table (ID,Manager,FullName) Manager is the ID of the Employee's manager ( null if the employee has no manager ,CEO for example has no manager )

    7- the scenario: suppose that Employee Number was XT340 and with ID=1120 (internal id of the employee) and he wants to request a vacation ( and this vacation was his 7th vacation during his career)

    a- the employee after filling the vacation request and on submit: a record will be created in the Workflow table as (ID=11,CreatedOn,1120,0,'V07-XT340',1)

    b- also record will be created in the signatory table (ID=1000, 11 (Workflow id ), 0 (Pending),Value of current date,1125 (id of the employee manager),null,null, null,1)

    c- when the employee manger open the request , he will take the decision ( either 2 (rejected) or 3 (approved) then the record 1000 will be updated by the manager when he save the result as follows ( ModifiedOn will be the datetime when he clicked save, ModifiedBy will be 1125, and Note will be coming from the manager if exists, IsActive=0 ,Status depends on the manager decision)

    d- if decision was 2 rejected => update Workflow set Status= Rejected

    e- if decision was 3 approved => if there still another manager ( manager of the employee's manager ) repeat the task from point b , else update the workflow, set Status=3 ( approved)

    hope it will help you


    Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.(Mosher’s Law of Software Engineering)

    Saturday, March 31, 2012 8:13 AM
  • Thanks for your complete answer, but I have a question... you mentioned the status: Pending 0 , InProgress 1, Rejected 2, Approved 3

    The main problem is to know which status follows the current status... for example, if a record is currently in InProgress status and I display a form in order to change that status. The combobox that allows status change must display only Rejected and Approved. Can this be achieved by mean of adding en Ordering column? That is why I am usng that hierarchical structure, but by using an Ordering column it might be simpler.

    What do you think, or how this is resolved by a real workflow implementation?



    Powered by C++

    Saturday, March 31, 2012 1:12 PM
  • Greetings again,

    Workflow statuses are : Pending, In progress, Rejected , Approved

    Signatory statuses are : Pending, Rejected, Approved

    when the work flow first created its status will be Pending ( let us suppose that 3 manager must approve it : M1, M2 , M3 and the request was )

    the first Signatory record under this workflow will be

    S1 (ID=1000, 11 (W1 ), 0 (Pending),Value of current date,1125 (M1),null,null, null,1)

    if M1 approved it then => in database S1.Status=(3 Approved),S1.IsActive=0  and W1.Status=1 (In progress) and another signatory S2 will be written in database for the M2 as [ S2 (ID=1001, 11 (W1 ), 0 (Pending),Value of current date,1126 (M2),null,null, null,1) ]

    if M1 rejected it then => in database S1.Status=2 (Rejected),S1.IsActive=0 and W1.Status=2 (Rejected) and then Update the main request Status to be rejected, and no more signatories will be written.

    same steps will be on M2 till M3 ( the last signatory in the hierarchy )

    if M3 approved it , no more signatories will be written but main request status will be updated as Approved

    and if he rejected it, update workflow status = rejected, workflow isactive = false , main request status=rejected and the signatory isactive=false.

    hope it will help you

    Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.(Mosher’s Law of Software Engineering)

    Sunday, April 1, 2012 3:57 PM