Hi,

LLBLBGen 2.5 DataAdapter

I have a InvoiceEntity with a SequentialNumber property (that rappresenting the same field in DB). This SequentialNumber is based on Year and InvoiceType. So I have a unique SequentialNumber for each year and for each InvoiceType. For example I have this record:

InvoiceID Year InvoiceType SequentialNumber 1 2005 Type1 1 2 2005 Type1 2

3 2006 Type1 1 4 2006 Type2 1 5 2006 Type2 2

6 2007 Type1 1 7 2007 Type1 2

I save my InvoiceEntity and other related entities by a UnitOfWork2, and actually before Commit()'s call, i get the next SequentialNumber by an Adapter.GetScalar() and a PredicateExpression based on InvoiceType and Year:

InvoiceEntity invoice;
UnitOfWork2 uow;

invoice.SequentialNumber = GetNextSequentialNumber(InvoiceType, Year);
uow.AddForSave(invoice,true);
uow.Commit(....);

... but I know that this is an unsafe way in a distributed scenario

Are there any way in LLBL to execute the Adapter.GetScalar() (actually inside GetNextSequentialNumber method) call in the UnitOfWork2.Commit(), and so inside the InvoiceEntity saving transaction ?

regards Davide

there are 3 possibilities. 1. use a table trigger to update the field in the db instead of LLBL logic. Make SequentialNumber properties readonly on entity. 2. override the OnBeforeSaving function in DataAccessAdapter object. determine if the entity is an InvoiceEntity and if it IsNew. if so then set the sequence. not as safe as option 1. 3. place a UC on the Invoice table for the aggregated fields. catch this exception in your logic and attempt to resave the invoice.

I would place UC on the fields and insert trigger on the table to calculate the seq. no.

thanks for your reply jmeckley, I prefer to do it via code (because it is an independent RDBMS way) , aren't there any other way except OnBeforeSaving overriding?

I would also use the OnBeforeSave event, but use something like a GetScalar in case the entity is not new to fetch the current sequence, OR use a scalarexpression in the update you're performing, so in one-go you fetch and set the sequence in the update.

thanks for your replies.

In case of SelfServicing scenario, can I use the same way? (OnValidatingEntityBeforeSave overriding?)

Yes you can.

IMHO, that's the best way:

Dvk wrote:

OR use a scalarexpression in the update you're performing, so in one-go you fetch and set the sequence in the update.

to execute the following update statement:

UPDATE Invoice
SET SequentialNumber = (SELECT MAX(i2.SequentialNumber) + 1 FROM  Invoice i2 WHERE ...) 

thanks to all

Walaa wrote:

Yes you can.

IMHO, that's the best way:

Dvk wrote:

OR use a scalarexpression in the update you're performing, so in one-go you fetch and set the sequence in the update.

to execute the following update statement:

UPDATE Invoice
SET SequentialNumber = (SELECT MAX(i2.SequentialNumber) + 1 FROM  Invoice i2 WHERE ...) 

Can you provide me an example to build a scalarexpression in LLBL, please (SelfServicing and Adapter)?

thanks! Davide

        Dim account As New AccountEntity
        Dim bucket As New RelationPredicateBucket(AccountFields.AccountId = 1) 'set filter to e.g. accountId = 1

        'build expression to apply. Fetch MAX accountNumber from account table and add 1 and use this value as input for update entity
        Dim expr As New Expression(AccountFields.AccountNumber.SetAggregateFunction(AggregateFunction.Max), ExOp.Add, 1)
        account.Fields(AccountFieldIndex.AccountNumber).ExpressionToApply = expr

        Using adapter As New DatabaseSpecific.DataAccessAdapter
            adapter.UpdateEntitiesDirectly(account, bucket)
        End Using

This simple piece of code should fix it...