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
Is this even possible with query expressions in one query or will I have to resort to querying per record basis?
please help
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.