|
|
mshe
User
Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
|
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?
|
|
|
mshe
User
Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
|
| Code: |
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?
|
|
|
daelmo
Support Team
Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8086 posts
|
After you fetch the collection, you have to extract all id's then use them in the UpdateMulti filter.
| Code: |
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):
| Code: |
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.
|
|
|
mshe
User
Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
|
| daelmo wrote: |
After you fetch the collection, you have to extract all id's then use them in the UpdateMulti filter.
| Code: |
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?
|
|
|
Walaa
Support Team
Location:
Joined on:
21-Aug-2005 16:03:48
Posted:
14527 posts
|
| Quote: |
| but in regards to UpdateMulti, doesn't it make sense that it could work on the current filtered set? |
That would be SaveMulti()
|
|
|
|