How can I build Entity Framework queries dynamically?

Question

I'm quite new to Entity Framework and I have a question about filtering data.

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 ...
}

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 <= 0 (it means there is no need to use computerId parameter in the query, select DiskLog entities only)
if logType == LogType.Disk && computerId > 0 (means I have to use computerId parameter, select DiskLog entities only)
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 && computerId > 0 (select all types of logs for specified computer)
if logType == LogType.Network && computerId <= 0 (select all NetworkLog entities)
if logType == LogType.Network && computerId > 0 (select all NetworkLog entities for specified computer)

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.

marc_s · Accepted Answer · 2025-06-16 17:18:58Z

16

You can easily use query composition.

You first start with query:

IQueryable<Log> query = context.LogSet;

They you compose sub-queries:

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;

And I would recommend using nullable value types for cases, when there is no value.

edited Jun 16 at 17:18

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 4, 2011 at 10:49

Euphoric

12.9k1 gold badge35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stecya · Accepted Answer · 2011-05-04 09:31:43Z

7

You can use Func<T,bool> to optimize this

IEnumerable<T> Select<T>(IEnumerable<T> source, Func<T, bool> userKeyFunc, Func<T, bool> dateFunc, int skip, int take)
{
    return source.OfType<T>().Where(userKeyFunc).Where(dateFunc).Skip(skip).Take(take);
}

Then use:

var result = Select<NetworkLog>(context.LogSet,x => x.Computer.User.UserKey == userKey,
                                x => x.DateStamp >= dateFrom && x.DateStamp < dateTo, nSkip,nTake)

And you can create factory for this functions

answered May 4, 2011 at 9:31

Stecya

23.3k10 gold badges76 silver badges102 bronze badges

1 Comment

Euphoric Over a year ago

That should be Expression<Func ..>. This will filter the data in-memory.

Collectives™ on Stack Overflow

How can I build Entity Framework queries dynamically?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related