DB: SQLServer LLBL Mode: Adpater

This is the scenario: TableA is the most important and heavy table in the DB, which has a child TableB. These are the tables which front the concurrency and optimization issues. This table have a PK of two fields, say fpk1 and fpk2.

When a record is created in TableA, I need to increment fpk2, but I want to do that in connection to fpk1, f.e. the TableA data seems to:

fpk1 fpk2 1 1 1 2 1 3 2 1 2 2 3 1 ...

So, I can't use the identity property of DB because identity in fpk2 no matter fkp1, therefore can't use "isComputed/sequenseName" attributes. And for DB independence I don't want to do that. Then I see some alternatives:

1. Use a trigger(instead of insert) which modify the value of my fpk2 in relation of fpk1. I will obtain the last fpk2 via: a. MAX function (SQL) with a WHERE clause using the fpk1. b. or a reference field in another control table which contain the last fpk2 in relation of fpk1. (seems more efficient)

• PROS:

• a. maximize the concurrency.

• CONTRAS:

• a. don't seems to be clean in the way there are logic at DB.

• b. LLBL don't seems to support insteadOfInsert Triggers, so although tableA will update, the child table TableB never know about that in a recursive save.

_2. Calculate the nextID at my businessLogic_code and set that Id in my TableA and perform a recursive save.

• PROS: a. clean and easy.

• CONTRAS:

• a. two DB steps (1 for obtan the last ID, 2 for save)

• b. reduce the concurrency, so while perform step1 and step2 other user may obtain a block. I dont want that because is the most heavy a important table.

3. Use 1) INSERT ... SELECT ... FROM formula, and 2) UPDATE a control Table. I explain:

_Table Structure_   

TableA.fpk1: I know this value at businessLogic (f.e.: EnterpriseID)
TableA.fpk2: A incremental value based on fpk1 (look the miniTable above)

TableB: Child table containing fpk1 and fpk2.

TableC: control table
TableC.fpk1: (f.e. EnterpriseID)
TableC.lastfpk2: last Id assigned to TableA.fpk2 based on fpk1.


_SQL Functionality to emulate_  

          BEGIN TRANS

            INSERT INTO TableA (fpk1, fpk2)
            SELECT someValueIKnow, TableC.lastfpk2 + 1
            FROM TableC 
            WHERE TableC.fpk1 = someValueIKnow

            UPDATE TableC
            SET lastfpk2 = lastfpk2 + 1
            WHERE fpk1 = someValueIKnow

         COMMIT TRANS

_How LLBL can help me in this point?_

Can I use a ExpressionToApply in a field of a Entity that will save? and if yes, this ExpressionToApply can reference another table via a RelationCollection?

So, I dont know if I'im complicating too much with this issue, but I want to maximize the concurrency and stay clean and DB independence, because probably I'll migrate in the future. Thank you for your opinions and support.

Hi,

I have similar case, I handled it :

Private concurrencyFactory As SecuenciaConcurrencyFactory
Private secuencia As DE.ProductoAjusteSecuenciaEntity  ' this is the control entity

Public Function Update( .......

.......

   ' this is done exactly before updating the main record

   If Me.header.IsNew Then   ' header is the main record

       Me.concurrencyFactory = New SecuenciaConcurrencyFactory
       Me.AsignarSecuencia()

       Do While True

          Try
                  ' update de control number, you can use adapter.SaveEntity
                   Me.registroDB.UpdateSecuencia(Me.secuencia, serverInfo, False, adapter)
                   Exit Do

           ' if the control number was changed by other user, then try to get the next
           Catch ex As ORM.ORMConcurrencyException

                        Me.AsignarSecuencia()  ' this is the important point

            Catch ex As Exception
                        Throw
            End Try

       Loop
   End If

   ' update the main record, you can use adapter.SaveEntity
     Me.registroDB.Update(Me.header, serverInfo, False, Me.adapter)

Private Sub AsignarSecuencia()

        ' get the control entity using its primary key (fpk1), you can use adapter.FetchEntity 
        Me.secuencia = Me.registroDB.ObtenerSecuencia(Me.serverInfo, Me.adapter)

        ' assign the concurrency factory to the control entity
        Me.secuencia.ConcurrencyPredicateFactoryToUse = Me.concurrencyFactory

        ' assign the control number to the main record, in your case it is fpk2
        Me.header.Ajuste = Me.secuencia.Secuencia
        
        ' increment the control number, in your case it is fpk2
        Me.secuencia.Secuencia += 1

End Sub


Private Class SecuenciaConcurrencyFactory
        Implements ORM.IConcurrencyPredicateFactory

        Public Function CreatePredicate(ByVal predicateTypeToCreate As ORM.ConcurrencyPredicateType, ByVal containingEntity As Object) _
         As ORM.IPredicateExpression Implements ORM.IConcurrencyPredicateFactory.CreatePredicate

            Dim toReturn As ORM.IPredicateExpression = New ORM.PredicateExpression
            Dim secuencia As DE.ProductoAjusteSecuenciaEntity = DirectCast(containingEntity, DE.ProductoAjusteSecuenciaEntity)

            Select Case predicateTypeToCreate
                Case ORM.ConcurrencyPredicateType.Delete
                Case ORM.ConcurrencyPredicateType.Save

                    ' only for updates, with this you try to be sure the control number was not changed by other user
                    toReturn.Add(DF.PredicateFactory.CompareValue(DI.ProductoAjusteSecuenciaFieldIndex.Secuencia, _
                      ORM.ComparisonOperator.Equal, secuencia.Fields(CType(DI.ProductoAjusteSecuenciaFieldIndex.Secuencia, Integer)).DbValue))
            End Select
            Return toReturn
        End Function
End Class

I really dont need _IConcurrencyPredicateFactory _as my business object don't allow updates. However thanks for your opinion about new reocords and nextID point of view . I resolved it this way:

public void CreateThing(entThing)
{
            // set fpk1
            entThing.Id1 = somevalue;
            
            // nextID calculated (fpk2)
            entThing.Id2 = GetNextID(entThing.Id1);
            
            try
            {
                // validations and persist info
                SaveThing(entThing);
            }

            // primary key error
            catch (ORMQueryExecutionException)
            {
                // recursive call until no error occur
                // (pending to resolve a possible loop issue)
                CreateThing(entThing);
            }
}

Pros: DB independent (no triggers, no SP's, no identity)

Contras: Slower than other options (triggers and SP's).

Obviously, the better option in terms of speed is Identity, but there are contras (1st. post).

I've tested this aproach with a stress threading test and although there's a speed trade off, it really works.

So, muchas gracias Rogelio.