How to call a function inside a where clause of IQueryable-CodePudding

I would like to execute a Where clause inside an IQueryable statement so it will be done in the database side rather than do the selection in the client side. The code I have right now is:

var all = await session.Query<Instance>()
    .ToListAsync()
    .ConfigureAwait(false);
var insts = all.Where(i => ruleInstances.Contains(i.Name,
    new ComparerExtension<string>(
        (x, y) => string.Compare(x, y, StringComparison.OrdinalIgnoreCase) == 0,
            h => h.ToLower().GetHashCode())));

Which does the selection on the client side.

Ideally, I would be able to write the IQueryable as follows:

var all = await session.Query<Instance>()
    .Where(i => ruleInstances.Contains(i.Name,
        new ComparerExtension<string>(
            (x, y) => string.Compare(x, y, StringComparison.OrdinalIgnoreCase) == 0,
                h => h.ToLower().GetHashCode())))
    .ToListAsync()
    .ConfigureAwait(false);

However, this ideal coding does not work. I get an exception: "Could not understand expression..." referring to the entire where clause.

By now I believe that I need to express the ruleInstances.Contains(...) method as an expression tree.

I will appreciate help setting it up.

Thank you.

CodePudding user response：

Your instantiation and call of ComparerExtension can't be done on the server side, hence it can't be converted to run there. Be aware, that at the end, the library has to create a SQL statement to be send to the server and no function or similar. If you need case insensitive comparison on the server side, you have to ensure that the server uses the correct collation for the database (e.g. SQL_Latin1_General_CP1_CI_AS) and in that case you can probably send:

.Where(i => ruleInstances.Contains(i.Name))

If this really works, depends on the type of ruleInstances. Maybe you have to convert it to a simple list of strings and use this:

var ruleInstanceNames = ruleInstances.Select(rule => rule.Name).ToList();

... .Where(i => ruleInstanceNames.Contains(i))