I am currently using EF Core 2.2 in my project which works with Oracle DB. I have a table in DB with a CLOB column. I have enabled Oracle Text search on this column. To perform Indexed search on this column Oracle expects the syntax
WHERE CONTAINS(column_name, 'SearchText') > 0
The column is mapped as a string property in my C# Entity and I have decorated it with the ColumnAttribute and given the DBType as CLOB. I am writing a LINQ query with a Contains() in the Where clause, somewhat like below,
DbContext.MyEntities.AsQueryable().Where(x => x.ClobField.Contains('SearchText'));
However, this translates to WHERE INSTR(ClobField, 'SearchText') > 0 which is the default ODP translation for C# Contains().
I researched this quite a bit on Internet and read MS Documentation, SO Questions, and several other blog posts but couldn't find a working solution. The closest I got was implementing a custom QueryProvider and a custom Expression Visitor which got me the desired output but Includes stopped working.
I want a way to extend the existing ODP classes so that I don't lose out on the default functionality. I just want to implement an extended behavior where if my Entity string property is decorated with a particular attribute, then, the translated query ends up as CONTAINS instead of the default INSTR.
Any guidance will be highly appreciated.