How do I DRY query logic between IEnumerable and IQueryable?

Question

Given two entities EntityOne and EntityTwo:

public class EntityOne
{
    public string Title { get; set; }
    public bool IsSomething { get; set; }
}

public class EntityTwo
{
    ...

    public virtual IEnumerable<EntityOne> EntityOnes { get; set; }
}

and an EntityOneFilter object like:

public class EntityOneFilter
{
    public EntityOneFilter(string? query, bool? isSomething, ...)
    {
        ...
    }
    
    public string? Query { get; }
    public bool? IsSomething { get; }
}

I'd like to reuse the query logic that can be applied to both IEnumerable<EntityOne> and IQueryable<EntityOne>:

(filter.Query == null || EF.Functions.Like(entity.Title, $"%{filter.Query}%"))
    && (!filter.IsSomething.HasValue || entity.IsSomething == filter.IsSomething.Value)
    && ...

So that it can be applied to both IQueryables coming from a DbContext:

dbContext.EntityOnes.Where(<something with filter>).FirstAsync();

and also to IEnumerable<EntityOne> that appear as a one to many collection to EntityTwo:

dbContext.EntityTwos
    .Where(e2 => e2.EntityOnes.Where(<something with filter>).Any())
    .FirstAsync();

How can I accomplish this?

Have you tried using `entity.Title.Contains(filter.Query)`? Also you should assign `filter.Query` and `filter.IsSomething` to local variables to use in the query so that EF will not complain about translating `EntityOneFilter`. — juharr, Oct 31 '20 at 16:57
@juharr Thanks for the `.Contains` tip. At the moment it doesn't seem to complain at all about that. It translates them as if they were two independent variables. But yeah, the expression itself is not the problem. It's working as it's supposed to. — Shoe, Oct 31 '20 at 17:14
I think you should take a look at Linqkit, for example starting [here](https://stackoverflow.com/q/46706255/861716). — Gert Arnold, Oct 31 '20 at 22:06
What i did is cast the IQueryable to an IEnumerable with the `.AsEnumerable()` method. I then pass that result to various other classes to augment the filter. Is that something that would work? — Ginger Ninja, Nov 01 '20 at 05:08
You can turn IEnumerable to IQueryable by AsQueryable method. But anyway it will not work with EF.Functions which are targeted only for SQL translation. — Svyatoslav Danyliv, Nov 01 '20 at 07:05
@SvyatoslavDanyliv `AsQueryable` was indeed the solution. Thanks. If you put it in an answer I'll accept it. By the way `EF.Functions` do work when you are within an expression context like the last code example. — Shoe, Nov 02 '20 at 06:41

score 0 · Accepted Answer · answered Nov 05 '20 at 17:00

0

As suggested in the comments by, I just had to use:

dbContext.EntityTwos
    .Where(e2 => e2.EntityOnes
        .AsQueryable()
        .Where(expression).Any())
    .FirstAsync();

where expression is an Expression<Func<EntityOne, bool>>.

answered Nov 05 '20 at 17:00

Shoe

74,840
36
166
272

How do I DRY query logic between IEnumerable and IQueryable?

1 Answers1