Dim Records As New RecordCollection
Transaction.Add(Records)
'Create a SQL WHERE clause
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...)
.AddWithAnd(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)))
'Don't select records more than 5 attempts
.AddWithAnd(RecordFields.Attempt <= 5)
End With
Dim SE As New SortExpression
With SE
.Add(RecordFields.EntryDate Or SortOperator.Ascending)
'Select oldest attempted record first
.Add(RecordFields.Attempt Or SortOperator.Descending)
End With
'Run the query
Records.GetMulti(PE, maxNumberOfItemsToReturn, SE, Nothing, Nothing)
Query: SELECT TOP(@top0) [LLBLGen].[dbo].[Records].[ID] AS [Id], [LLBLGen].[dbo].[Records].[EntryDate], [LLBLGen].[dbo].[Records].[Type], [LLBLGen].[dbo].[Records].[LockTime], [LLBLGen].[dbo].[Records].[Attempt], [LLBLGen].[dbo].[Records].[LastAttemptTime], [LLBLGen].[dbo].[Records].[XML] AS [Xml] FROM [LLBLGen].[dbo].[Records] WHERE ( ( ( [LLBLGen].[dbo].[Records].[LockTime] IS NULL OR [LLBLGen].[dbo].[Records].[LockTime] <= @LockTime2) AND ( [LLBLGen].[dbo].[Records].[LastAttemptTime] IS NULL OR [LLBLGen].[dbo].[Records].[LastAttemptTime] <= @LastAttemptTime3) AND [LLBLGen].[dbo].[Records].[Attempt] <= @Attempt4)) ORDER BY [LLBLGen].[dbo].[Records].[EntryDate] ASC,[LLBLGen].[dbo].[Records].[Attempt] DESC
Parameter: @top0 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 50.
Parameter: @LockTime2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1/5/2011 11:56:09 PM.
Parameter: @LastAttemptTime3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1/6/2011 12:21:09 AM.
Parameter: @Attempt4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
Running Self-Servicing, SQL Server 2008 R2, with the latest (October 9, 2010) runtimes (2.6.10.0930)