CODEWITHSUNDEEP
c#linqdynamiclinq-to-entities
Daniel Bişar
Daniel Bişar

Reputation: 2763

How to apply dynamic filters for Linq-To-Entites and execute them server-side

I want to write a class which provides basically a list of entries. This entries can either be read from a database or are generated during application lifetime. This class should provide a way to filter this entries. Is it possible to implement the filters in a way that only one filter for both sources is needed?

For example:

interface IFilter
{
    bool Filter(ILogEntry entry);
}

For LINQ with IEnumerable there is no problem to apply this as dynamic filters. 

IEnumerable<IFilter> filters;
IEnumerable<ILogEntry> entries;

foreach(var filter in filters)
    entries = entries.Where(p => filter.Filter(p));

But for Linq-To-Sql there are two problems I have no solution for:

  1. How can convert the LogEntry provided by the context to ILogEntry?
  2. How can I ensure that the query will be executed on the server and not locally?

It seems a little bit strange to me that the following code will actually execute the filter on the server. But if I use IFilter in this query which takes ILogEntry (which is implemented by LogEntry) as an argument the filter will be applied on client-side:

entities.Logs
    .Select(p => new LogEntry() { Message = p.Message })
    .Where(p => p.Message == "132");

Is there a way to write filters for ICollection and IQueryable and ensure that the filters for IQueryable will be executed on the SQL-Server?

Upvotes: 3

Views: 1384

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062660

The only way to apply that server side is by composing it as expression trees, for example:

interface IFilter {
    public Expression<Func<ILogEntry, bool>> GetPredicate();
}
...
IQueryable<ILogEntry> entries = ...
foreach(var filter in filters) {
    entries = entries.Where(filter.GetPredicate());
}

This then requires implementations such as:

public Expression<Func<ILogEntry, bool>> GetPredicate() {
    return x => x.SomeField == 25;
}

Note: if you have an in-memory chink of data you can switch from IEnumerable<T> to IQueryable<T> by using .AsQueryable(), which allows it to use an expression-tree locally.

Upvotes: 3

Related Questions