Beware what you wish for!
I've just had a frustrating afternoon panicking because my app was hanging big time on a TextRenderer.DrawText line that has been working fine for years!
Turns out that a query I was using that used WITH TIES was fetching a lot more records than I was expecting - some 7,000 more - and these were used in a Linq loop which indirectly created an Image from them. GDI handles were being hoovered up and at 10,000 goes bang!
So what I really want is a WITH TIES but with an absolute maximum just in case.
This is the original generated query:-
DECLARE @p2 bigint; SET @p2='50'
SELECT
TOP(@p2) WITH TIES [Item].[ResourceID] AS [ResourceID],
MIN([Item].[DateAdded]) AS [DateAdded]
FROM
[Item]
WHERE
( ( [Item].[DateRemoved] IS NULL))
GROUP BY
[Item].[ResourceID]
ORDER BY
MIN([Item].[DateAdded]) DESC
generated by this code if it is relevant
public static DynamicQuery<ResourceAndItemInfoDTO> CreateRecentlyAddedQuery(int count)
{
var qf = new QueryFactory();
var sortClause = ItemFields.DateAdded.SetAggregateFunction(AggregateFunction.Min) | SortOperator.Descending;
sortClause.EmitAliasForExpressionAggregateField = false;
return qf.Item
.Where(ItemFields.DateRemoved == DBNull.Value)
.OrderBy(sortClause)
.Limit(count)
.Select(() => new ResourceAndItemInfoDTO
{
ResourceID = qf.Field(nameof(ItemEntity.ResourceID)).SetExpression(new DbFunctionCall("WITH TIES {0}", new object[] { ItemFields.ResourceID })).ToValue<int>(),
DateAdded = sortClause.FieldToSortCore.ToValue<DateTime?>()
})
.GroupBy(ItemFields.ResourceID);
}
What I am looking for is a simple way to wrap the generated query with another TOP clause specifying an absolute maximum.
In SQL I would do
SELECT TOP 501 * FROM
(
// Original query here
) x
(based on a 500 maximum - if I get 501 back I know I gone past the maximum and can deal differently with that; if I get exactly 500 back there really were 500)
Is there a simple way within LLBLGen to wrap a query like that?
If not, in this case since there are only a few fields, I'll just let them all come back and just use the first 500 but I prefer a reusable, efficient solution if it is available.