The documentation for Lightning Connect is quite complete. The help page for Considerations for Lightning Connect has a list of things that have special limitations and considerations, including SOQL, which has its own considerations for SOQL page.
Amongst some other important considerations that make it well worth your while to read them yourself, those docs state the following:
External objects also don’t support the following.
- EXCLUDES operator
- FOR VIEW clause
- FOR REFERENCE clause
- INCLUDES operator
- LIKE operator
- toLabel() method
- TYPEOF expression
- WITH clause
As for alternative, the first choice is SOSL!
SOSL is the misunderstood, underused, underappreciated stepbrother of SOQL. And actually, it is designed for fuzzy text-based searches. Most of the time when a DEV wants to use
LIKE as an operator, they should probably be thinking SOSL anyway. Don't forget to checkout the Lightning Connect Considerations for SOSL, too, though! And this will only help you if the external OData end point actually supports text searches in some fashion.
The last and probably messiest workaround might be to store some data in Salesforce. If data residency is not a regulatory concern, perhaps you could store some data in a related object on the platform with an indirect relationship to the OData source. And this is just me supposin' at this point as this is completely untested, but suppose you had relationship:
Dummy_Internal_Data__c <--parent----child--> My_External_Obj__x
And you had a custom text field in the dummy object called:
String_Query__c. You'd set up a sync job to populate that field on a periodic basis with updates from the OData source, and set it as
ExternalId to get benefits of the field being indexed.
You might try:
SELECT String_Query__c, ID,
(SELECT Field_1__c, etc. from My_External_Objs__r)
WHERE String_Query__c like 'some-value%'
If you did this, you'd also need to create logic to sync the Salesforce dummy object on a regular basis, too.