SQL Fulltext search with AutoQuery

Is there a way to use the SQL full text search with AQ? I’ve a complex use-case in which text inputted by user is searched (CONATINS) across 10+ joined tables and the performance is not good.

If you’re running this in the .NET Framework (i.e. defaults to true in .NET Core) you can set either:

OrmLiteConfig.StripUpperInLike = true; 


Plugins.Add(new AutoQueryFeature {
    StripUpperInLike = true

Tp remove the UPPER({Field}) modifier in LIKE wildcard comparisons so your LIKE searches will be able to take advantage of column indexes.

Otherwise AutoQuery lets you use Customizable SQL Fragments in its QueryDbField or Implicit Conventions which you can use to execute custom queries, e.g:

[QueryDbField(Template = "CONTAINS({Field}, {Value})", Field="FirstName")]
public string FirstNameContains { get; set; }
1 Like

Thanks a lot for the quick response

Implicit Conventions worked like a charm…Thanks!

1 Like

Happy to hear it, which conventions did you end up going with?

just this:

Plugins.Add(new AutoQueryFeature { 
   EndsWithConventions = new Dictionary<string, QueryDbFieldAttribute>
                                { "Contains", new QueryDbFieldAttribute {
                                        Template = "CONTAINS({Field}, {Value})"}},
                                { "StartsWith", new QueryDbFieldAttribute {
                                    Template = "UPPER({Field}) LIKE UPPER({Value})",
                                    ValueFormat = "{0}%" }},
                                { "EndsWith", new QueryDbFieldAttribute {
                                    Template = "UPPER({Field}) LIKE UPPER({Value})",
                                    ValueFormat = "%{0}" }}
1 Like