Hello Everyone,

I'm using LLBLGen v2.6 with SQL Server.

Let's say I fetch a collection into memory (i.e. MyRecords.GetMulti(MyFilter), can I use UpdateMulti directly update all the entities that are part of the collection without rehitting the DB?

Is there a way to fetch all the PKs and use that as the WHERE clause in the UpdateMulti function?

            Dim PE As New PredicateExpression

            With PE
                'Fetch all unlocked records or records that are older than 30 minutes (locked records should never take 30 minutes to process...)
                .Add(RecordFields.LockTime = System.DBNull.Value Or RecordFields.LockTime <= Now.Subtract(New TimeSpan(0, 30, 0)))

                'Fetch all records that have not been attempted or fetch records that were tried at least 5 minutes ago
                .AddWithAnd(RecordFields.LastAttemptTime = System.DBNull.Value Or RecordFields.LastAttemptTime <= Now.Subtract(New TimeSpan(0, 5, 0)))
            End With

            'Run the query
            Records.GetMulti(PE, _
                             maxNumberOfItemsToReturn, _
                             New SortExpression(RecordFields.EntryDate Or SortOperator.Ascending))

Just to be clear, I want to update the LockTime field after I select my collection, but since the filter is dynamic, I cannot just reissue the same filter - is there a way I can UpdateMulti based on the primary keys selected?

After you fetch the collection, you have to extract all id's then use them in the UpdateMulti filter.

List<int> thePks = new List<int>();
foreach (RecordEntity rec in Records)
{
     thePks.Add(rec.RecordId);
}

IPredicateExpression updateFilter = new PredicateExpression(
     RecordFields.RecordId == thePks);

// call to update multi with updateFilter
...

This will generate an sql something like (simplified):

UPDATE Records
SET MyDate = @someValue
WHERE RecordId IN (@value1, @value2, @value3, ..., @valueN)

(where N is the number of records you fetched). Be aware that some databases had limits on how much values you can include in a 'IN' clause set. If there are too much values, your DB will complain about that, in that case you have to elaborate a predicateExpression that meets your needs. In the worst-case scenario, you will break the update into chunks. For example, if you have 10000 fetched entities, you can update them in groups of 1000 for instance.

daelmo wrote:

After you fetch the collection, you have to extract all id's then use them in the UpdateMulti filter.

List<int> thePks = new List<int>();
foreach (RecordEntity rec in Records)
{
     thePks.Add(rec.RecordId);
}

IPredicateExpression updateFilter = new PredicateExpression(
     RecordFields.RecordId == thePks);

// call to update multi with updateFilter
...

That's what I thought I'll have to do .. but in regards to UpdateMulti, doesn't it make sense that it could work on the current filtered set?

but in regards to UpdateMulti, doesn't it make sense that it could work on the current filtered set?

That would be SaveMulti()