Using Func as parameter for LinqToEntities

Question

I have a Linq-Query to get my EF-Data. My query joins 4 tables and selects the result to a denormalized type. I will need this query very often but with different predicates. The List-ExtensionMethods (e.g. .Where() are working with a Func<T,bool> as a parameter and I wanted to do it the same - but I don´t find a way to access my predicate in my Method.

public DenormalizedType GetData(Func<Thing, bool> predicate)
{
    using (var dbContext = new MyDbContext())
    { 
        var myData = (from some in dbContext.Thing 
        join other in dbContext.OtherThing
        on some.OtherId equals other.Id
        // => HowToWhere ???
        select new DenormalizedType()
        {
            SomeEntry = some.Entry
            SomeOtherId = some.OtherId
            OtherValue = other.Value
        }).ToList();
    }
}

I have 3 questions regarding this issue.

First (obviously): how to invoke my predicate to use a dynamic where-clause?

Second: If my initial idea doesn´t work (because teovankots answer indicates, that my approach isn´t valid for LinqToEntities) is it somehow possible, to make a method of the join only?

Third: What is the best performing approach to return my results to another software-component?

Answer 1

You can call it easily. Install this package, add:

public DenormalizedType GetData(Expression<Func<Thing, bool>> predicate)
{
    using (var dbContext = new MyDbContext())
    { 
        var myData = (from some in dbContext.Thing 
        join other in dbContext.OtherThing
        on some.OtherId equals other.Id
        where predicate.Invoke(some) //check this
        select new DenormalizedType()
        {
            SomeEntry = some.Entry
            SomeOtherId = some.OtherId
            OtherValue = other.Value
        }).ToList();
    }
}

You just should know that Func<T1,T2> is a method. With this signature:

T2 predicate(T1 parameter) { /*...*/ }

Your second question depends on how you connect your conponent. But as long as you get DenormalizedType not DbEntity your example looks ok.

Answer 2

At least I´ve found a way to solve my issue - but I´d highly appreciate hints, and or ways to make it better, because I can´t imagine that this is the holy grail or even close to...

However, the first step is my join, which I return as IQueryable. Important: No using here, because otherwise the dbContext will be disposed, which is not so nice, while working with the IQueryable:

private static MyDbContext _dbContext;
private static IQueryable<DenormalizedType> SelectType()
{
    _dbContext = new MyDbContext();
    var myData = (from some in dbContext.Thing 
        join other in dbContext.OtherThing
        on some.OtherId equals other.Id
        select new DenormalizedType()
        {
            SomeEntry = some.Entry
            SomeOtherId = some.OtherId
            OtherValue = other.Value
        };
    return myData;
}

I´ve learned a lot today. For example: IEnumerable and IQueryable have both an extension method .Where(). But only IEnumerable.Where() has a Func<T,bool> as a parameter. IQueryable takes a Expression<Func<T,bool>> for its Where(). If I want my query to be executed, with all of my conditions I need to work with the IQueryable-type, as long as all my wheres aren´t executed. So I needed to take a closer look to the Expression-Type. I didn´t understand what all this actually does, but it works ;)

The first thing I had to do, was writing my Where-Methods.. That was pretty easy after I´ve read this one: Entity Framework Filter "Expression<Func<T, bool>>". The Method looks like this:

public static IQueryable<DenormalizedType> SelectWhereCriteria(IQueryable<DenormalizedType> data, Expression<Func<DenormalizedType, bool>> predicate)
{
    return data.Where(predicate);
}

The Expression itself was a little more complicated, because I have a Selection-Enum which should select specified filters. The Expression looks like:

Expression<Func<DenormalizedType, bool>> FilterBySelection(Selection selection)
{
    switch(selection)
    {
        case Selection.Active:
            return x => x.IsActive == true;
        case Selection.InActive:
            return x => x.IsActive == false;
        case Selection.SomeOtherSelection:
            return x => x.SomeOther == "Criteria"
        default:
            return x => true;
    }
}

This Expression works fine on my IQueryable:

var selectedQuery = DataHandler.SelectWhereCriteria(query, FilterBySelection(selection));

The only thing I needed now, was ordering. I found some very cool stuff from MarcGravell (what a genius btw) Dynamic LINQ OrderBy on IEnumerable<T> where he posted some code as an answer, which you can use, to OrderBy PropertyName. His first piece of code takes an IQueryable, orders it by PropertyName (he provides Extensions for Descending OrderyBy as well) and returns an IOrderedQueryable. The ToList()-Operation is the very last operation I execute.

And one more thing: Don´t forget to Dispose the DbContext:

public static void Dispose()
{
    _dbContext.Dispose();
}

Answer 3

EF query provider needs to translate the LINQ query expression tree to SQL, which is not possible when you pass a Func<...> (and more generally, invocation expression like delegate, unknown method etc.).

Shortly, what you ask is not possible with Func<...> type parameters.

The first thing to consider when working with Queryable methods is to use Expression<Func<...>> whenever you would use Func<..> in Enumerable methods. So change your method argument like this:

public DenormalizedType GetData(Expression<Func<Thing, bool>> predicate)

Unfortunately using the supplied expression inside LINQ query syntax is not supported out of the box. To do that, you need some expression tree processing library.

The problem and the possible solution is explained in the LINQKit package page. The solution provided by the package is through AsExpandable and Invoke custom extension methods.

Install the nuget package, add

using LinqKit;

to the source code file, and now you can use something like this to achieve the goal:

public DenormalizedType GetData(Expression<Func<Thing, bool>> predicate)
{
    using (var dbContext = new MyDbContext())
    { 
        var myData = (from some in dbContext.Thing.AsExpandable() // <= 
        join other in dbContext.OtherThing
        on some.OtherId equals other.Id
        where predicate.Invoke(some) // <=
        select new DenormalizedType()
        {
            SomeEntry = some.Entry
            SomeOtherId = some.OtherId
            OtherValue = other.Value
        }).ToList();
    }
}