How to select last n historic records per item using Query Expressions

Question

I need to use Microsoft XRM sdk Query Expression to query a SQL database through windows service and I came across a requirement which I must select last n history records per item which translate to SQL like this:

SELECT 
RecentPrices.ProductId, 
Product.[Description], 
RecentPrices.Price, 
RecentPrices.DateChanged 
FROM 
( 
    SELECT 
        ProductId, 
        Price, 
        DateChanged, 
        Rank() over 
        ( 
            Partition BY ProductId 
            ORDER BY DateChanged DESC 
        ) AS Rank 
    FROM PricingHistories 
) RecentPrices 
JOIN ProductMaster Product 
ON RecentPrices.ProductId = Product.Id 
WHERE Rank <= 10

It should output list of Products with last 10 price change histories (Price and DateChange) which can be grouped as C# IEnumerable to something like:

Product

Id : number
Description : string
PriceChangesLastTenPreview [] : Object array

Is this even possible with query expressions in one query or will I have to resort to querying per record basis?

please help

score 0 · Accepted Answer · answered Jun 13 '19 at 12:44

If you retrieve ALL the price change records, you can group them, then Take(10) from each grouping.

If you had a date cutoff rather than top 10 you could query for all price changes after that date and then group them.

If you want to retrieve only the top 10 for each record, I think you'll have to query per record. You might want to think about using ExecuteMultiple to batch the queries.

How to select last n historic records per item using Query Expressions

1 Answers1