问题描述
我对实体框架很陌生,我有一个关于过滤数据的问题.
I'm quite new to Entity Framework and I have a question about filtering data.
我有两个不同的日志实体,它们是:DiskLog
和 NetworkLog
.这些实体都源自 Log
实体.这是我的 C# 应用程序中的一些代码:
I have two various Log entities, they are: DiskLog
and NetworkLog
. These entities both are derived from Log
entity. Here is some code from my C# app:
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 工作.我有几种执行方式:
Of course, I already have working app and database tables created. What I want to do is to make function GetWithFilter work. I have several execution ways there:
if logType == LogType.Disk &&computerId (表示查询中不需要使用computerId参数,只选择DiskLog实体)
if logType == LogType.Disk && computerId <= 0
(it means there is no need to use computerId parameter in the query, select DiskLog entities only)
if logType == LogType.Disk &&计算机 ID >0
(意味着我必须使用computerId参数,只选择DiskLog实体)
if logType == LogType.Disk && computerId > 0
(means I have to use computerId parameter, select DiskLog entities only)
if logType == LogType.NotInitialized &&computerId (无需使用computerId和logType,只需选择所有实体,DiskLog和NetworkLog)
if logType == LogType.NotInitialized && computerId <= 0
(no need to use computerId and logType, just select all the entities, DiskLog and NetworkLog)
if logType == LogType.NotInitialized &&计算机 ID >0
(选择指定计算机的所有类型的日志)
if logType == LogType.NotInitialized && computerId > 0
(select all types of logs for specified computer)
if logType == LogType.Network &&computerId (选择所有 NetworkLog 实体)
if logType == LogType.Network && computerId <= 0
(select all NetworkLog entities)
if logType == LogType.Network &&计算机 ID >0
(选择指定计算机的所有NetworkLog实体)
if logType == LogType.Network && computerId > 0
(select all NetworkLog entities for specified computer)
如您所见,有很多可用的选项.我必须像这样写 6 个查询:
As you can see, there are plenty of available options. And I got to write 6 queries like this:
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();
那么问题是如何优化代码?哪里有办法让它变得更好.
So the question is how can I optimize the code? Where is the way to make it better.
推荐答案
您可以轻松使用查询组合.
You can easily use query compossition.
首先从查询开始.
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;
并且我建议在没有值的情况下使用可为 null 的值类型.
And I would recomend using nullable value types for cases, when there is no value.
这篇关于如何动态构建实体框架查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!