Using Func as parameter for LinqToEntities

c# entity-framework-6 linq linq-to-entities performance

Question

To get my EF-Data, I have a Linq-Query. My query selects the result to a denormalized type after joining four tables. This query will be required often, but with various predicates. List-ExtensionMethods, such as.Where() are using aFunc<T,bool> I intended to use my predicate as a parameter in my method, but I couldn't discover a way to accomplish that.

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();
    }
}

Regarding this matter, I have three inquiries.

The first thing to consider is how to employ a dynamic where-clause by invoking my predicate.

Second: Is it feasible to create a join-only method if my first concept fails (teovankot's response suggests that my approach is invalid for LinqToEntities)?

What is the best method for returning my findings to another software component, thirdly?

1
2
6/15/2017 12:29:03 PM

Accepted Answer

ZZZ_tmp
3
6/15/2017 5:29:44 PM

Popular Answer

At least I've discovered a solution to my problem, but I'd really welcome any tips or suggestions on how to improve it as I doubt this is the holy grail or even near to it.

But my join, which I return as IQueryable, is the first step. Important: Avoid using here when interacting with the IQueryable since doing so would cause the dbContext to be discarded, which is not ideal:

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

Today has taught me a lot. For instance: Extension methods are available for both IEnumerable and IQueryable..Where() Though onlyIEnumerable.Where() has aFunc<T,bool> as a variable. IQueryable utilisesExpression<Func<T,bool>> since itsWhere() . I need to deal with the IQueryable-type if I want my query to be evaluated with all of my conditions, provided that none of my where clauses are executed. So I had to pay more attention to the Expression-Type. Although I'm not sure what all of this accomplishes exactly, it seems to function.

My Where-Methods have to be written first, of course. After reading this one, it was rather simple: "ExpressionFuncT, bool>>" Entity Framework Filter. This is how the method looks:

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

Because I had a Selection-Enum that should choose certain filters, the Expression itself was a bit trickier. The phrase appears as follows:

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

On my IQueryable, this Expression works well.

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

I simply needed to order at this point. I came across some very fascinating material from MarcGravell (such a genius, by the way) at OrderBy using dynamic LINQ on an IEnumerableT>, where he uploaded some code as a solution to OrderBy PropertyName that you may utilise. The first line of his code accepts an IQueryable, ranks it according to PropertyName (he also has Extensions for Descending OrderyBy), and then produces an IOrderedQueryable. The very last action I do is the ToList()-Operation.

Additionally, remember to dispose of the DbContext:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow