如何在Asp.net C# mvc 中缓存数据库表以防止许多数据库查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17653124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to cache database tables to prevent many database queries in Asp.net C# mvc
提问by Daniel Ezra
I build my own cms using Asp.net mvc 4 (c#), and I want to cache some database data, likes: localization, search categories (it's long-tail, each category have it's own sub and sub-sub categories), etc..
我使用 Asp.net mvc 4 (c#) 构建自己的 cms，并且我想缓存一些数据库数据，例如：本地化、搜索类别（它是长尾，每个类别都有自己的子和子子类别）等..
It's will be overkill to query the database all the time, because it can be more than 30-100 queries for each page request, however the users update those database rarely
一直查询数据库是多余的，因为每个页面请求可能会超过 30-100 次查询，但是用户很少更新这些数据库
So what is the best way (performance and convenience) to do it?
那么最好的方法（性能和便利性）是什么？
I know how use the OutputCache of the action, but it's not what I need in this situation , it's cache the html, but what I need is for example, that my own helper @html.Localization("Newsletter.Tite")will take the value of the language, or any another helper that interact with data etc.
我知道如何使用操作的 OutputCache，但这不是我在这种情况下需要的，它是缓存 html，但我需要的是例如，我自己的助手@html.Localization("Newsletter.Tite")将采用语言的值，或任何其他助手与数据交互等。
I think (not really sure) that I need to cache the data I want, only when the application is invoke for the first time, and then work with the cache location, but I don't have any experience even about to it.
我认为（不太确定）我需要缓存我想要的数据，只有在第一次调用应用程序时，然后使用缓存位置，但我什至没有任何经验。
采纳答案by Darin Dimitrov
You could use the built-in MemoryCacheto store entire resultsets you have retrieved from the database.
您可以使用内置MemoryCache来存储您从数据库中检索到的整个结果集。
A typical pattern:
一个典型的模式：
MyModel model = MemoryCache.Default["my_model_key"] as MyModel;
if (model == null)
{
model = GetModelFromDatabase();
MemoryCache.Default["my_model_key"] = model;
}
// you could use the model here
回答by Digbyswift
There are several things to consider here before choosing your implementation of caching, but one of the main things you have to decide is at what point do you want the caching to occur - at data access, the model creation or UI generation? One or all of these places?
在选择缓存的实现之前，这里有几件事需要考虑，但您必须决定的主要事情之一是您希望缓存发生在什么时候 - 在数据访问、模型创建或 UI 生成时？这些地方之一或所有？
You have several options but for general data caching you can use the System.Runtime.Caching.MemoryCache, or for something much more flexible and rewarding you can look at an implementation using NoSQL like Redis.
您有多种选择，但对于一般数据缓存，您可以使用System.Runtime.Caching.MemoryCache，或者对于更灵活和更有价值的东西，您可以查看使用 NoSQL（如Redis）的实现。
You could use MemoryCachefor data caching, but use Redis for caching aggregates making up your view models. If you went with Redis, you could of course handle all your caching using this. The benefit being that all your data would become persistent across application restarts. The disadvantage being that it becomes an additional requirement for running your CMS.
您可以MemoryCache用于数据缓存，但使用 Redis 来缓存构成视图模型的聚合。如果您使用 Redis，您当然可以使用它来处理所有缓存。好处是您的所有数据将在应用程序重新启动时保持不变。缺点是它成为运行 CMS 的额外要求。
Other things to factor in are consistency (using some IoC would help) and allowing data to be voided once it updates. Therefore having some means of updating the cache.
其他需要考虑的因素是一致性（使用一些 IoC 会有所帮助），并允许数据在更新后无效。因此有一些更新缓存的方法。
With regards to the bestapproach, in your case if you are creating a new CMS application, start small/simple and build up in steps. You may not get it perfect first time but as long as you are consistent and clear in your approach it should be easy to build upon what you have done or swap out and try something different/better.
关于最佳方法，在您的情况下，如果您正在创建一个新的 CMS 应用程序，请从小/简单开始，然后逐步建立。你第一次可能不会完美，但只要你在你的方法中保持一致和清晰，就应该很容易建立你所做的或者换掉并尝试不同/更好的东西。
回答by Brendan Vogt
I had to cache common database data like data that were displayed in drop downs. I used MemoryCache. And I used Entity Framework code firstand Autofacfor dependency injection.
我必须缓存常见的数据库数据，例如显示在下拉列表中的数据。我用过MemoryCache。我使用Entity Framework code first和Autofac进行依赖注入。
Here is part of what I did in my solution, might not work for you but it worked for me, not perfect though but needs a lot of cleaning up to do.
这是我在解决方案中所做的部分工作，可能对您不起作用，但对我有用，虽然不完美，但需要大量清理工作。
My ICacheManagerinterface:
我的ICacheManager界面：
public interface ICacheManager
{
T Get<T>(string key);
void Set(string key, object data, int cacheTime);
bool IsSet(string key);
void Remove(string key);
void Clear();
}
My CacheManagerclass:
我的CacheManager班级：
public class CacheManager : ICacheManager
{
private ObjectCache Cache
{
get
{
return MemoryCache.Default;
}
}
public T Get<T>(string key)
{
return (T)Cache[key];
}
public void Set(string key, object data, int cacheTime)
{
if (data == null)
{
return;
}
CacheItemPolicy policy = new CacheItemPolicy();
policy.AbsoluteExpiration = DateTime.Now + TimeSpan.FromMinutes(cacheTime);
Cache.Add(new CacheItem(key, data), policy);
}
public bool IsSet(string key)
{
return (Cache.Contains(key));
}
public void Remove(string key)
{
Cache.Remove(key);
}
public void Clear()
{
foreach (var item in Cache)
{
Remove(item.Key);
}
}
}
An extension class for my caching:
我的缓存的扩展类：
public static class CacheExtensions
{
public static T Get<T>(this ICacheManager cacheManager, string key, Func<T> acquire)
{
return Get(cacheManager, key, 60, acquire);
}
public static T Get<T>(this ICacheManager cacheManager, string key, int cacheTime, Func<T> acquire)
{
if (cacheManager.IsSet(key))
{
return cacheManager.Get<T>(key);
}
else
{
var result = acquire();
cacheManager.Set(key, result, cacheTime);
return result;
}
}
}
And this is how I would use it in my repository class. This method returns a list of all my banks which is displayed in a drop down.
这就是我在存储库类中使用它的方式。此方法返回我所有银行的列表，该列表显示在下拉列表中。
public class BankRepository : RepositoryBase<Bank>, IBankRepository
{
private readonly ICacheManager cacheManager;
private const string BanksAllCacheKey = "banks-all";
public BankRepository(IDatabaseFactory databaseFactory, ICacheManager cacheManager)
: base(databaseFactory)
{
Check.Argument.IsNotNull(cacheManager, "cacheManager");
this.cacheManager = cacheManager;
}
public IEnumerable<Bank> FindAll()
{
string key = string.Format(BanksAllCacheKey);
return cacheManager.Get(key, () =>
{
var query = from bank in DatabaseContext.Banks
orderby bank.Name
select bank;
return query.ToList();
});
}
}
I hope this helps. It's a very simple implementation but it works for me. There are many articles online on how to use a caching strategy in ASP.NET MVC. Just Googleit.
我希望这有帮助。这是一个非常简单的实现，但对我有用。网上有很多关于如何在ASP.NET MVC. 就Google这样。
