Indexing in Lex.DB

(previous post)

In this post I will explain, how to improve data access in Lex.DB using built-in indexing feature.

To keep the lib compact, Lex.DB provides very simple, yet powerful indexing:

  • all non-PK indexes are non-unique
  • non-calculated indexes support composite keys based on up to 3 data members
  • calculated indexes (indexes based on calculated data members) are supported
  • all indexes (including PK index) are stored together in the index file of the table

Let’s take an example for the previos blog post. Again our demo class looks like this:

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; } } 
}

To define an index we’ll modify our mapping declaration like this:

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

The name of the index must be unique for the table. It will be used later for querying.

We can also chain several index definitions together like this:

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

Now we have two independent non-unique indexes for LastName and FullName properties. Notice that FullName is calculated one.

Let’s query the table using our freshly defined indexes.

var list1 = db.Table<Contact>().LoadAll("FullName", "Scott Guthrie");
var list2 = db.Table<Contact>().LoadAll("LastName", "Papa");

So simple is that.

continue…

10 thoughts on “Indexing in Lex.DB”

  1. On my system, VS2012 is complaining that there isn’t an overload for method ‘LoadAll’ that takes 2 arguments (when I’m trying to mimic your example of querying a table with indexes).

    Also, is it necessary to specify the key (e.g. Id) as an index or is that taken as explicit? In other words, can I go

    db.Table().LoadAll(“Id”, 1)

    ?

    1. I’m writing a blog post about changes in indexing in version 1.1.

      In short, to query by primary key, just use DbTable.LoadByKey/LoadByKeys.

      For additional indexes, LoadAll in the example above is replaced by chain of method calls like this:

      Table().IndexQueryByKey(“LastName”, “Papa”).ToList();

  2. Hi Lex,

    I am working on incorporating your DB in our Windows Phone 8 app. Suppose I was querying (using your Contacts example) for all users with LastName containing ‘pa’ or FirstName containing ‘pa’?

    Cheers and thanks for a great data storage alternative.

    1. var index = db.Table<Contact>().IndexQuery(“LastName”);

      var query = index.Where(i => i != null && i.Contains(“pa”)).ToList();

      If I understood you correctly ;)

      1. Thanks Lex. My queries are all working as expected. I did have to modify some classes that contained Lists of custom objects but apart from that the cutover to Lex.db was largely painless.

        And man is it fast!

        Thanks again.

          1. Hi Jack – I have been using SterlingDB on Windows Phone 7. I decided to move to Lex.DB on Windows Phone 8 as I am sure I will be asked to port this app to desktop metro and the compatibility of Lex.DB was compelling in this aspect.

            Cheers

      2. Lex

        I’ve got:

        var index = LexDB.Table().IndexQuery(“FamilyName”);

        but the compiler is complaining:

        The type arguments for method ‘Lex.Db.DbTable.IndexQuery(string)’ cannot be inferred from the usage. Try specifying the type arguments explicitly.

        What am I misunderstanding here? I cannot see a syntax difference between what I’ve written and your example :-(.

        Thanks.

        Philip

        1. Philip,

          Try:
          var index = LexDB.Table().IndexQuery(“FamilyName”);

          I too faced this scenario. Also (in case you strike this one) when loading a single object use this syntax:

          App.LexDatabase.LoadByKey(primary_key);

          Cheers,

          Mike Palmer

        2. Philip – problem with last post (and probably others) – the reason is that this editor is cutting out greater than and less than symbols. You need to add a generic data type encased in greater than and less than symbols directly after .IndexQuery.

          In LoadByKey the syntax is greater than symbol – ObjectType, data type – less than symbol.

          Sorry for the verbosity but I cannot edit comments.

          Cheers,

Leave a Reply

Connect with:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>