Repodb: Request: Add caching for ExecuteQuery and ExecuteQuery<TEntity>

[BieleckiLtd]

var users = connection.ExecuteQuery<User>("SELECT * FROM [dbo].[Users];");
var users = connection.ExecuteQuery<User>("SELECT * FROM [dbo].[Users];", cache: Cache, cacheKey: "AllUsers"); // no overload

[mikependon]

This has been discussed in the past internally, it is our intention to not support the cache in the result of ExecuteQuery operation. One of the reason is to avoid the collisions as cache item should be coupled to an TEntity type. This simple use-case could trigger a lot of undesired behavior, TBH.

Think of a single scenario like below. You execute a query and cache the result by passing your cacheKey.

var users = connection.ExecuteQuery<User>("SELECT * FROM [dbo].[Users];", cache: Cache, cacheKey: "AllUsers");

But, you can also pass the same key even though you changed the SQL statement.

var users = connection.ExecuteQuery<User>("SELECT * FROM [dbo].[Customers];", cache: Cache, cacheKey: "AllUsers");

Though, we know that we can use the SQL statement as the key for cache, but that is something up for discussion. Maybe we can start over on this request again.

[BieleckiLtd]

I thinks risk would not be different than what we currently can do with QueryAll for instance:

var users1 = connection.QueryAll(tableName: "[UsersTbl]", cache: Cache, cacheKey: "AllUsers");
var users2 = connection.QueryAll(tableName: "[UsersView]", cache: Cache, cacheKey: "AllUsers");

we can use the SQL statement as the key for cache

That is even cooler idea, because cacheKey: "AllUsers" parameter won't be needed

[mikependon]

@BieleckiLtd - you might be correct as the QueryAll has been extended to support the tableName argument, up for upcoming release. I think, it is good to include this one if we are to make the next beta release. Atleast, your request is a bit perfect in our release timing.

[BieleckiLtd]

For me caching own queries would be a killer feature that Dapper doesn't have out of the box, and man, I just like writing my own sql.

[mikependon]

Thanks! Since this is also a part of our discussion historically (internally) and you requested it, I guess, we will include this on the next beta release. That is being said.

[mikependon]

Which do you like from the 2 below?

This one?

var users = connection.ExecuteQuery<User>("SELECT * FROM [dbo].[Users];", cache: Cache, cacheKey: "AllUsers");

Or this one?

var users = connection.ExecuteQuery<User>("SELECT * FROM [dbo].[Users];", cache: Cache, isCacheResult: <true|false>);

The latter one uses the SQL text as the cache key - in binary form. I prefer the first 1 for simplicity and uniformity.

[BieleckiLtd]

I think first is more self-explanatory

[mikependon]

Integration Test will soon to follow.

[mikependon]

@BieleckiLtd - The fix is now available at RepoDB v1.12.0-beta1 and RepoDb.SqlServer v1.1.0-beta1.

[mikependon]

Closing this ticket now. Kindly let us know if you still have questions and concerns. Thanks

[BieleckiLtd]

Works as expected. Much appreciated!