How do I filter a query based on an expression instead of a field?

[bpaolo71]

I would like to filter a query like that:
var filter = new QueryField("REPLACE(CorporateNumber, '-', '')", Operation.Equal, normalizedOrganizationNumber!);
but I get the message "Unknown column 'REPLACE(CorporateNumber, '-', '')' in 'WHERE'"
I understand that QueryField wants a field name but is there no other way besides using a raw query?

[mikependon]

A bit edge-case, but your case is interesting. Unfortunately, this is not yet supported for now. Below are the only supported QueryField classes. Can you wait, I think I can support it for you?

When do you think you need this?

[bpaolo71]

Hello Mike,
I've solved it with a raw query for the time being. So, no rush :)

[mikependon]

But to be honest, RepoDB can easily support this for you. We have a capability to this kind of use-case. You can do the same as below, but for now you have to do it your own.

Link: https://github.com/mikependon/RepoDB/blob/master/src/Providers/RepoDb.Core/RepoDb/Extensions/QueryFields/FunctionalQueryField.cs

[mikependon]

You have to pass the Format your own on the FunctionalQueryField. Like below.

var where = new FunctionalryQueryField(fieldName, operation, value, dbType, "REPLACE({0}, '-', '')");
connection.Query<Entity>(where);

[bpaolo71]

I will update to the last version and try it. Thank you.

[bpaolo71]

This worked fine!

[mikependon]

Cool, thanks for confirming. If you would you like to add the ReplaceQueryField to core, please creat a request ticket. I can do it for you later. You can also issue a PR if you have time. Thanks