SQL Fulltext search with AutoQuery

ServiceStack Auto Query
1

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.

2

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

OrmLiteConfig.StripUpperInLike = true; 

//or:

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
3

Thanks a lot for the quick response

4

Implicit Conventions worked like a charm…Thanks!

1 Like
5

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

6

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