So how do we delete expired prices?

One approach would be to use the built-in APIs.

First we need to get a list of all prices using IPriceDetailService.List() and then get rid of expired prices using IPriceDetailService.Delete().

This approach works fine on demo sites with relatively small catalogs. However, fetching a list of all expired prices might be painfully slow if you're working with big catalogs.

Another solution would be to use good ol' SQL.

It is as simple as

delete from PriceDetail where ...

But wait!

You shouldn't delete data using SQL because you might have event listeners that are performing some magic every time prices are created, deleted, or updated. You never know if Optimizely will implement caching of expired prices in the future to fine-tune the UI performance, etc.

So what's the solution?

Use the best of both worlds!

If you're using a micro ORM library like Dapper, you could delete expired prices like this:

public void DeleteExpiredPrices()
{
    var expiredPrices = GetExpiredPriceIds();
    if (expiredPrices.Count > 0)
    {
        _priceDetailService.Delete(expiredPrices);
    }
}

private List<long> GetExpiredPriceIds()
{
    string connectionstring = ConfigurationManager
        .ConnectionStrings["EcfSqlConnection"]
        .ConnectionString;

    const string sql = @"select distinct PriceValueId
                         from PriceDetail
                         where ValidUntil < GETUTCDATE()";


    using (var connection = new SqlConnection(connectionstring))
    {
        return connection.Query<long>(sql).ToList();
    }
}

If not, take a look at https://github.com/DapperLib/Dapper and become a true legend.

Happy coding! :)