Linq2DB arbitrary Where clause

Question

I'd like to be able to implement a search method that can take any arbitrary properties of my POCO class as arguments. This works well:

 public static IEnumerable<iUser> Search(DataContext context, Func<iUser, bool> predicate)
    {
        return from i in context.GetTable<iUser>().Where(predicate) select i;
    }

but in this case the filtering appears to take place after collecting all the rows in the table.

Is it possible to use Linq to generate an arbitrary query like this without filtering after the sql call? What approaches would you recommend?

Thanks!

Change the return type to `IQueryable`. And drop the `from` and `select` parts, it's unnecessary here. — Jeff Mercado, Dec 26 '18 at 22:08
I don't know LINQ to DB but I'm pretty sure that the predicate should be `Expression>`. — Gert Arnold, Dec 26 '18 at 22:19
Thanks @GertArnold and @JeffArnold! Both of those together solved my problem. — Matt McNabb, Dec 26 '18 at 23:37

score 2 · Accepted Answer · answered Dec 27 '18 at 08:16

LINQ to DB is an Object-Relational Mapper (ORM) that is capable of translating LINQ expressions into SQL. The word "expression" is important here. A Func is not an expression but a delegate, you have to use Expression<Func<>> in LINQ methods for LINQ to DB to be able to translate them. Otherwise the data will be pulled from the database first after which the Func filters them in memory.

So your function should look like:

public static IEnumerable<iUser> Search(DataContext context,
    Expression<Func<iUser, bool>> predicate)
{
    return context.GetTable<iUser>().Where(predicate);
}

The return type depends on the what you want the caller of this function to be capable of. If you return IQueryable<iUser> the caller will be able to extend the expression by their own expressions. That is, Search(context, somePredicate).Where(...) will be translated into SQL as a whole. Returning IEnumerable will apply any subsequent predicates (either as Func or as Expression) in memory.

Side note, in order to line up with common naming conventions, if iUser is an interface (I have no idea if LINQ to DB supports interfaces) then you should rename it into IUser, otherwise name it User.

Thanks, the iUser is actually my POCO type. An interface I've implement is actually something like "IiUserRepository." Not great, I know. — Matt McNabb, Jan 02 '19 at 17:26

Linq2DB arbitrary Where clause

1 Answers1

Linked

Related