如何动态构建实体框架查询? [英] How can I build Entity Framework queries dynamically?

问题描述

我对实体框架很陌生，我有一个关于过滤数据的问题.

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:

1. if logType == LogType.Disk &&computerId <= 0(表示查询中不需要使用computerId参数，只选择DiskLog实体)

1. 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 <= 0(无需使用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 <= 0(选择所有 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.

这篇关于如何动态构建实体框架查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！