我对Entity Framework很新,我有一个关于过滤数据的问题.
我有两个Log实体,分别是：DiskLog和NetworkLog.这些实体都来自于Log实体.这是我的C#应用程序的一些代码：
public class Log { ... }
public class DiskLog : Log { ... }
public class NetworkLog : Log { ... }
public enum LogType
{
NotInitialized = 0,Disk,Network
}
public List<Log> GetWithFilter(
Guid userKey,int nSkip,int nTake,DateTime dateFrom = DateTime.MinValue,DateTime dateTo = DateTime.MaxValue,LogType logType = LogType.NotInitialized,int computerId = 0)
{
// need to know how to optimize ...
return ...
}
当然,我已经创建了工作的应用程序和数据库表.我想做的是使函数GetWithFilter工作.我有几种执行方式：
>如果logType == LogType.Disk&&& computerId <= 0(这意味着查询中不需要使用computerId参数,仅选择DiskLog实体)
>如果logType == LogType.Disk&&& computerId> 0(意味着我必须使用computerId参数,仅选择DiskLog实体)
>如果logType == LogType.NotInitialized&& computerId <= 0(不需要使用computerId和logType,只需选择所有实体,DiskLog和NetworkLog)
>如果logType == LogType.NotInitialized&& computerId> 0(选择指定计算机的所有类型的日志)
>如果logType == LogType.Network&& computerId <= 0(选择所有NetworkLog实体)
>如果logType == LogType.Network&& computerId> 0(选择指定计算机的所有NetworkLog实体)
你可以看到,有很多可用的选项.而且我写了6个这样的查询：
1.
context.LogSet
.OfType<DiskLog>
.Where(x => x.Computer.User.UserKey == userKey)
.Where(x => x.DateStamp >= dateFrom && x.DateStamp < dateTo)
.OrderByDescending(x => x.Id)
.Skip(nSkip)
.Take(nTake)
.ToList();
2.
context.LogSet
.OfType<DiskLog>
.Where(x => x.Computer.User.UserKey == userKey)
.Where(x => x.DateStamp >= dateFrom && x.DateStamp < dateTo)
.Where(x => x.Computer.Id == computerId)
.OrderByDescending(x => x.Id)
.Skip(nSkip)
.Take(nTake)
.ToList();
3.
context.LogSet
.Where(x => x.Computer.User.UserKey == userKey)
.Where(x => x.DateStamp >= dateFrom && x.DateStamp < dateTo)
.OrderByDescending(x => x.Id)
.Skip(nSkip)
.Take(nTake)
.ToList(); // simplest one!
4.
context.LogSet
.Where(x => x.Computer.User.UserKey == userKey)
.Where(x => x.DateStamp >= dateFrom && x.DateStamp < dateTo)
.Where( x => x.Computer.Id == computerId)
.OrderByDescending(x => x.Id)
.Skip(nSkip)
.Take(nTake)
.ToList();
5.
context.LogSet
.OfType<NetworkLog>
.Where(x => x.Computer.User.UserKey == userKey)
.Where(x => x.DateStamp >= dateFrom && x.DateStamp < dateTo)
.OrderByDescending(x => x.Id)
.Skip(nSkip)
.Take(nTake)
.ToList();
6.
context.LogSet
.OfType<NetworkLog>
.Where(x => x.Computer.User.UserKey == userKey)
.Where(x => x.DateStamp >= dateFrom && x.DateStamp < dateTo)
.Where( x => x.Computer.Id == computerId)
.OrderByDescending(x => x.Id)
.Skip(nSkip)
.Take(nTake)
.ToList();
那么问题是如何优化代码？在哪里可以使它更好.
解决方法
您可以轻松使用查询编译.
你首先从查询开始.
IQueryable<Log> query = context.LogSet;
他们组成子查询.
if (logType == LogType.Disk)
{
query = query.OfType<DiskLog>(); // not sure if you need conversion here
}
else if (logType == LogType.Network)
{
query = query.OfType<NetworkLog>(); // not sure if you need conversion here
}
query = query.Where(x => x.Computer.User.UserKey == userKey);
if (computerId != 0)
query = query.Where( x => x.Computer.Id == computerId);
// .. and so on
query = query.OrderByDescending(x => x.Id).Skip(nSkip).Take(nTake);
return query.ToList(); // do database call,materialize the data and return;
而当没有值时,我会推荐使用可空值类型.
