我正在研究监控应用程序。例如，记录在用户配置文件中的打印机。目标是获取每台打印机的用户列表。管理员可能必须筛选结果。按用户、按 PC、默认打印机...并将结果导出为 CSV。因此，当管理员执行导出时，csv 需要与活动过滤器相关。我尝试使用 EF 6 创建一个静态缓存，以保留这些过滤器。
缓存类
public class PrintersCache
{
public static AuditprinterDBEntities1 db = new AuditprinterDBEntities1();
public static IQueryable<AuditPrinter> auditPrinterCache = null;
public static IQueryable<AuditPrinter> AuditPrinterCache
{
get
{
if (auditPrinterCache == null) auditPrinterCache = db.AuditPrinter.Include(a => a.Pc).Include(a => a.PrintersConfig).Include(a => a.Users);
return auditPrinterCache;
}
}
}
在控制器中，我正在调用我的类
static IQueryable<AuditPrinter> auditPrinter = PrintersCache.AuditPrinterCache;
然后，在过滤器方法的开头：
auditPrinter = PrintersCache.AuditPrinterCache;
和导出方法：
public void ExportCSV()
{
var sw = new StringWriter();
sw.WriteLine(String.Format("{0};{1};{2};{3}", "PcName", "Date", "ActivityName", "UserName"));
foreach (var record in auditPrinter)
{
sw.WriteLine(String.Format("{0};{1};{2};{3}", record.Pc.PcName, record.Date, record.Activity.ActivityName, record.Users.UserName));
}
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=Export.csv");
Response.ContentType = "text/csv";
Response.Write(sw);
Response.End();
}
它正在工作...但是搜索有点慢，例如，如果我非常快速地单击搜索按钮 5 或 6 次，我会得到一个'System.Data.Entity.Core.EntityException'. The underlying provider failed on Open. 我需要二级缓存吗？
编辑：搜索过滤法
public ActionResult LaunchSearch(string keyword, string keyword2, int chx, int pid, int fid)
{
auditPrinter = PrintersCache.AuditPrinterCache;
string returnpartial = "";
switch (chx)
{
case 1:
if(pid!=0)
{
auditPrinter = auditPrinter.Where(a => a.Printers.PrinterId == pid);
}
returnpartial = "Indexprinter";
break;
case 2:
if (pid != 0)
{
auditPrinter = auditPrinter.Where(a => a.UserId == pid);
} else
{
auditPrinter = auditPrinter.OrderBy(a => a.Users.UserName).ThenBy(a => a.Pc.PcName);
}
returnpartial = "Indexvuser";
break;
case 3:
if (pid != 0)
{
auditPrinter = auditPrinter.Where(a => a.Pc.PcId == pid);
}
else
{
auditPrinter = auditPrinter.OrderBy(a => a.Pc.PcName).ThenBy(a => a.Users.UserName);
}
returnpartial = "Indexvpc";
break;
}
if (keyword != "")
{
switch (chx)
{
case 1:
auditPrinter = auditPrinter.Where(a => a.Users.UserName.Contains(keyword)).OrderBy(a => a.Users.UserName).ThenBy(a => a.Pc.PcName);
break;
case 2:
auditPrinter = auditPrinter.Where(a => a.Users.UserName.Contains(keyword)).OrderBy(a => a.Users.UserName).ThenBy(a => a.Pc.PcName);
break;
case 3:
auditPrinter = auditPrinter.Where(a => a.Pc.PcName.Contains(keyword)).OrderBy(a => a.Pc.PcName).ThenBy(a => a.Users.UserName);
break;
}
}
if (keyword2 != "")
{
switch (chx)
{
case 1:
auditPrinter = auditPrinter.Where(a => a.Users.UserName.Contains(keyword) && a.Pc.PcName.Contains(keyword2)).OrderBy(a => a.Pc.PcName).ThenBy(a => a.Users.UserName);
break;
case 2:
auditPrinter = auditPrinter.Where(a => a.Users.UserName.Contains(keyword) && a.Pc.PcName.Contains(keyword2)).OrderBy(a => a.Pc.PcName).ThenBy(a => a.Users.UserName);
break;
case 3:
auditPrinter = auditPrinter.Where(a => a.Pc.PcName.Contains(keyword) && a.Users.UserName.Contains(keyword2)).OrderBy(a => a.Users.UserName).ThenBy(a => a.Pc.PcName);
break;
}
}
if (fid != 0)
{
switch (fid)
{
case 1:
auditPrinter = auditPrinter.Where(a => a.PrintersConfig.IsDefault == true);
break;
case 2:
auditPrinter = auditPrinter.Where(a => a.PrintersConfig.IsDefault == false);
break;
}
}
return PartialView(returnpartial, auditPrinter.ToList());
}
实体框架上下文不应是静态的，其生存期应尽可能短。
删除缓存类并将上下文创建和查询放在LaunchSearch方法中，不要忘记释放此上下文。
仅当存在真正的性能问题时，才考虑缓存，缓存复杂对象(如实体(很少是一个好主意。如果需要缓存，请尝试使用 HTTP 缓存、客户端或服务器端。
public ActionResult LaunchSearch(string keyword, string keyword2, int chx, int pid, int fid)
{
using(var db = new AuditprinterDBEntities1())
{
var auditPrinter = db.AuditPrinter.Include(a => a.Pc).Include(a => a.PrintersConfig).Include(a => a.Users);
// Do whatever you need to do and return result ...
}
}
我建议您按如下方式更改PrinterCache。
public class PrintersCache
{
public static AuditprinterDBEntities1 db = new AuditprinterDBEntities1();
static PrintersCache()
{
AuditPrinterCache = db.AuditPrinter
.Include(a => a.Pc)
.Include(a => a.PrintersConfig)
.Include(a => a.Users);
}
public static IQueryable<AuditPrinter> AuditPrinterCache
{
get; private set;
}
}
这仍然不能解决问题，因为您实际上没有缓存。只需存储一个 linq 查询，当您遍历 foreach (var record in auditPrinter) 时，就会被懒惰地调用。
因此，这将被多次调用并导致性能问题，以避免它通过将其列出来存储结果。所以类应该看起来像
public class PrintersCache
{
public static AuditprinterDBEntities1 db = new AuditprinterDBEntities1();
static PrintersCache()
{
AuditPrinterCache = db.AuditPrinter
.Include(a => a.Pc)
.Include(a => a.PrintersConfig)
.Include(a => a.Users).ToList;
}
public static List<AuditPrinter> AuditPrinterCache
{
get; private set;
}
}
使用内存缓存，您可以执行以下操作：
class PrintersCache
{
private const string CacheName = "MyCacheName";
private const string AuditPrinterKey = "AuditPrinterKey";
private static readonly MemoryCache memoryCache = new MemoryCache(CacheName);
private const int CacheExpirationInMinutes = 20;
public static List<AuditPrinter> GetAuditPrinterCache()
{
// Create a lazy object to retrieve the data when the cache has expired
var newLazyValue = new Lazy<List<AuditPrinter>>(() =>
{
// You should not keep an instance of your db context without disposing it. Also The instantiation of a db context is cheap.
using (var db = new AuditprinterDBEntities1())
{
return db.AuditPrinter
.Include(a => a.Pc)
.Include(a => a.PrintersConfig)
.Include(a => a.Users).ToList();
}
});
// Return the instance of the Lazy object. If the cahce has expired a new instance of the Lazy object is created.
return
((Lazy<List<AuditPrinter>>)
memoryCache.AddOrGetExisting(AuditPrinterKey, newLazyValue, new CacheItemPolicy()
{
// Defines that the cache will expired after 20min
AbsoluteExpiration = new DateTimeOffset(
DateTime.UtcNow.AddMinutes(CacheExpirationInMinutes))
})).Value;
}
}
- 使用内存缓存的
AddOrGetExisting方法，如果缓存中不存在对象，您将提供某种回退。 - 使用
Lazy<T>，您将仅在缓存为空时调用数据库。 - 使用 AbsoluteExexpation，您可以确保在数据库中发生一些更改时更新缓存(在本例中每 20 分钟更新一次(。
由于List<T>是可查询的，因此您可以像这样检索数据：
// Initializes your query
var query = PrintersCache.GetAuditPrinterCache().Where(a => a.Printers.PrinterId == pid);
// apply your others conditions here
...
query = query .Where(a => a.Users.UserName.Contains(keyword)).OrderBy(a => a.Users.UserName).ThenBy(a => a.Pc.PcName);
// Return your new filtered list
return query.ToList()