Custom Entity/Full-Text Search Index

[HeyJoel]

Although it is possible to build your own search index using the messaging framework, it would be a big improvement to be able to query custom entities via different partitions. Using the basic test site as an example, Blog Posts need to be partitioned by Category so I can create filtered category views and aggregated post counts in the side panel, which is a common use case on wordpress blogs.

In addition to this we have always wanted better free-text searching in the admin panel not just on custom entities but also globally.

I envisage this being a simple in-memory implementation by default, with scalablity via plugins for lucene.net and other full-text search engines. The implementation in Cofoundry would have to be a basic abstraction of the facilities typically available in these tools, e.g. simple free-text and partitioning of data sets via keys. More complex use can always be build using the message aggregator.

[JornWildt]

Here is one suggestion for implementing a fairly straight forward full text indexing feature in Cofoundry:

1. Mark up every searchable field in the data model using a special attribute Indexed or similar:

public class MyDataModel : ICustomEntityDataModel
{
  [Indexed]
  public string Summary { get; set; }
}

When ever a custom entity is created or updated, go through all [indexed] fields and concatenate all their strings into a single standard VARCHAR(max) column named something like IndexedTerms.

Then use SQL server standard text indexing mechanism for indexing the IndexedTerms column.

Now you have text search on all the columns - at least in a Google like way where you search into every searchable field simultaneously.

If you want to be able to specify individual fields for searching, you need a different approach with a separate table containing the name of the field, the value of the field and the ID of the custom entity of the field. Then do standard SQL server text indexing on the field value column and restrict to the field name in the name column.

It should, in principle, not be to complex to implement 🙈

[JornWildt]

But be very aware of SQL server's "stop words" which is very language dependent. The best example I know of is the word "and" which is not indexed in English - but in Danish it means "duck" (the bird) and is fairly reasonable to index.