Improved Indexing in Lex.DB 1.1

Original indexing feature was rather limited. You could look for records with specified values of indexed key only. Basically it was only useful for speedy access to related entities. Like child entities via parent key.

This has now been drastically improved. The new indexing adds capabilities to query records with less, great and between operators. In addition some LINQ-like methods are available: Skip, Take and Where, helping you to limit record loading.

Let me show few examples to understand the details. Indexing declaration during initialization phase is not changed, changes in code are minimal. I just copied the code snippets from original indexing post.

public class Contact 
{   
  public int Id { get; set; }   
  public string FirstName { get; set; }   
  public string LastName { get; set; }   
  public DateTime? BirthDate { get; set; }   
  public string FullName { get { return FirstName + " " + LastName; } } 
}

And initialization:

db.Map<Contact>()
  .Automap(i => i.Id)
  .WithIndex("LastName", i => i.LastName)
  .WithIndex("FullName", i => i.FullName);

And now we’d like to get all contacts with the last name Guthrie:

var contacts = db.Table<Contact>();
var list1 = contacts.IndexQueryByKey("LastName", "Guthrie").ToList();

Or without implicitly inferring index type:

var list2 = contacts.IndexQuery<string>("LastName").Key("Guthrie").ToList();

The interesting part is the new method IndexQuery<K> that returns IIndexQuery interface. This interface is backed by an object containing indexed query parameters.
For example, if you want to query all last names starting from letter L, following query construct can be used.

The IndexQuery instance is immutable, so we may cache the query in variable:

var index = contacts.IndexQuery<string>("LastName");

// The index has low bound L (inclusive) 
// and high bound M (exclusive by default),
// so we basically make operator Between 
// "L" <= i.LastName < "M"
var query = index.GreaterThan("L", true).LessThan("M");

To actually materialize the entities from query above ToList() method is used. All relevant entities will be loaded at once in a singe read-only transaction:

var items = query.ToList();

If we don’t want to load relevant entities, but just to look at resulting last names, we can use lazy loading feature with ToLazyList() method. In this case list of Lazy<T> entity wrappers is returned. Lazy<T> will load wrapped entity only once, when you access Lazy<T>.Value property.

For your convenience indexed key value stored in Key property is also part of Lazy<T>:

var items = query.ToLazyList();

foreach(var i in items)
{
  var lastName = i.Key;
  if ((lastName ?? "").Length > 5)
  {
    // load from database
    var entity = i.Value;
    // process entity
  }
}

Sometimes you need to limit the resulting number of entities returned by the indexed query. To avoid materialization of excessive entities, limit your query with Take(int) method:

var items = query.Take(5).ToList();

In this case only five first available entities will be loaded from database.

Sometimes you’d want to implement paging. To simplify this scenario, Skip(int) method can be used:

var pageIndex = 3;
var pageSize = 50;
var items = query.Skip(pageIndex * pageSize).Take(pageSize).ToList();

This will skip first 3 pages with page size of 50 entities. The order you call Skip and Take are not important for sake of simplicity.

There is also parameterless overload for PK index query allowing you to query primary index the same way:

var index = contacts.IndexQuery<string>();

And last but not least. There are some complex logic involved in deciding whether to load entity or not based on indexed values. To simplify this scenario Filter<K>(Func<K, bool>) is available at your disposal:

var items = query.Where(i => (i ?? "").Length> 5).Take(5).ToList();

The method above will return first five entities with last name’s length greater than 5.
Similarly to LINQ, IndexQuery has Count() method to determine the number of entities without actually loading them.

To get list of primary keys for resulting entities, ToIdList() method is used. If you pass the resulting value to DbTable.LoadByKeys(), you’ll get all keyed entities loaded. That is what ToList() method does.

But be aware, all these methods above are not LINQ. Thy are just plain old CLR methods. They look like LINQ, but LINQ they are not. So don’t expect them to behave exactly like LINQ.

Feel free to submit your feedback. Sometimes one’s minute can save another’s days.