Introduction to Lex.DB

(previous post)

Let’s start with simple example. We have a data class to store locally.

Every piece of data has to have a primary key. The type of primary key is up to you. What is important, the PK type T has to implement IComparable<T>.

Lex.DB maintains the Red-Black-Tree index of primary key values in memory. The index is loaded on first use, so memory is allocated on demand. Moreover, if Lex.DB detects that index is updated by another process, the index will be reloaded. During read/write operations, Lex.DB locks index file for read/write access accordingly. This approach maintains the DB consistency all the time.

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

The Contact class defines several properties, some are calculated and read only. Lex.DB does not require data members to be properties, public fields are also supported. However, if you like your class to be UI-bindable, data members have to be properties.

Now that we have our data class, we may actually start with database. Create a test project of your preferred platform (.NET 4, .NET 4.5, Silverlight 5, Windows Phone 8.0, Windows Store App) and add a reference to the NuGet package named “lex.db”. At the moment of writing this post, Lex.DB is in Prerelease version yet, so include Prerelease packages in search dialog to locate it.

Alternatively, from NuGet Package Manager Console you may run the following command to install Lex.DB:

install-package lex.db -pre

Then add a “using Lex.Db;” in the header of your C# file.

To initialize Lex.DB database, we need to name and initialize it first.

// demo here is a name of the folder, where all data will be stored.
var db = new DbInstance("demo");

Next step is to define our data to store. During this phase we produce highly optimized mapping code to blazingly fast read/write our data classes.

db.Map<Contact>().Automap(i => i.Id);

Automap method uses reflection to inspect and produce mappings for all public properties and fields. It is also possible to manually enlist all properties and fields to be serializable in our data storage like this:

  .Key(i => i.Id)
  .Map(i => i.FirstName)
  .Map(i => i.LastName)
  .Map(i => i.BirthDate);

The approach above provides more flexibility, but it has to be kept in sync in case we extend our Contact class with more data members.

After we’ve finished with declaration of our data classes, following method initializes database instance:


Now we are ready to rock-n-roll… Just store DbInstance variable for all your data access needs somewhere.

To save data:

db.Save(new Contact { Id = 1, FirstName = "Lex", LastName = "Lavnikov" },
        new Contact { Id = 2, FirstName = "John", LastName = "Doe" },
        new Contact { Id = 3, FirstName = "Scott", LastName = "Guthrie" },
        new Contact { Id = 4, FirstName = "John", LastName = "Papa" });

To load all data:

var list = db.LoadAll<Contact>();

To load certain instance by key:

var contact = db.LoadByKey<Contact>(1);

An the end, don’t forget to release all resources allocated:


That’s it for intro.


68 thoughts on “Introduction to Lex.DB”

  1. There are some missing pieces in BCL for WP7 that make it difficult to support.
    For instance, HashSet and BlockExpression.

    I have already tried to workaround these issues, but it’ll take more time. Right now, version 1.0 is already published on NuGet.

    I’ll keep updating this blog with more detailed info regarding Lex.DB and WP7 in particular.

  2. Thanks Lex,

    I’ve been looking the code, not be better try to use portable class library before compiler directive in code? I am very interest in this Project, but my english is not good (I speak spanish)

    May be you can include interfaces in portable class library and define a specific implementation to different platforms.

    I will continue studying all the code and if I can help with anything let me know. Remember my poor english :(

    anyway, it’s a great job. Thanks again.

  3. It is definitely possible to split the lib into shared (portable) part of the code and platform dependent stuff.

    However, since portable .NET subset is very limited, it will all end up with abstracting missing pieces into interfaces. So we’ll get 1 portable and 5 platform dependent assemblies, performance penalties in some places due to additional abstraction in engine itself.

    Right now, we have just 5 platform dependent assemblies in total. A consuming project has to reference just 1 platform dependent “lean-and-mean” assembly.

    I think the better way, since Lex.DB has very simple usage, is to define an interface of data access logic in portable library, and implement it in platform dependent assembly that references platform dependent Lex.DB lib.

    I may well provide an example of this technique in future blog posts.
    Stay tuned

  4. Thanks,

    Will give it a try. For wp7 used Sterling to save/retrieve images for caching. Since Sterling doesn’t run yet on winrt your LexDb might be an good alternative.

  5. Hi, Lex.
    Some great stuff you’ve been working on – I’ve tested LexDB performance and it’s just like ‘WoW!’.
    I’ve not looked deep into sources, so I simply ask a question – why do all methods that get data from DB return Lists (still, I looked at sources quickly and saw that all those methods gets an IEnumerable internally and call ToList() then), not IEnumerable and further – all the data gets loaded at once and stays in memory ’till app is closed. Simply, I’ve got an app that takes literally huge amounts of data every second and need to store this data elsewhere (and update saved data) or very shortly it’ll run out of memory, I thought I would just use Lex.DB bu,m what a bummer, data still stays in memory(
    Is this a feature by design?

  6. Thank you for the feedback!

    Actually Lex.DB does not hold any data in memory, besides indexes. It just transports your data down to file system and back. After records are loaded from DB, they are not referenced by Lex.DB, just by the List and reclaimable by GC as soon as you throw this List away.

    Why do I use List, rather than IEnumerable is quite simple. During load operation the data files are locked on OS level. Yielding records from read transaction potentially allows the programmer to do anything during the read lock. This could lead to hard-to-find deadlocks, index corruption, stalls, concurrent access problems, etc.

    That’s why read transaction is kept for the smallest amount of time.

  7. Hi Lex

    Lex.DB is looking like a promising solution for some challenges I’m facing. Well done!
    Are you planning to implement handling serialization of multi-level one-to-many relationships that will work with Entity Framework and if so, can you give an expected availability date?
    My problem is that I have multiple nested classes (i.e. Company->Projects->Tasks) and something like Sterling goes into an infinite loop with 2nd level nested classes.

  8. There are some interesting ideas how to implement out-of-box relationship serialization, however I cannot give you any dates.
    Been busy with better indexing.

    What possible now is to serialize foreign keys as arrays of primitive types. In this case, you may use LoadByKeys to materialize whole collection of foreign references.

    I may also write an article describing an example dealing with these issues.

  9. I’m starting to amend/update my app to use Lex.DB instead of SQLite to see how the performance compares. I would add my +1 to see Lex.DB working on WP7 if at all possible as I am most definitely looking for a single DB platform that works across WinRT, WP7 and WP8 if that is possible.

    One comment: the declaration of DbInstance(path) says that this creates a database instance at the specified path. However, that doesn’t seem to be the case. In testing, if I specify a full path, I get an error – it looks like my only option is to specify a folder name, and Lex.DB creates the database under \Lex.DB\. It would be fantastic if I could specify a full path as that would then allow a Win8 user to use a file picker to specify the destination folder and I can create the database there rather than always in the app’s storage.

  10. Looking at your example in more detail, does the fact that you are specifying the ID number mean that you don’t (currently) have the equivalent of an automatically generated (incrementing) ID number?

    1. No, Lex.Db has this feature.

      Typemap.Automap and Key methods have an optional autoGen parameter, that controls automatic PK generation for int, long and Guid types of PK. Automatic keys are generated for default values only (0 and Guid.Empty).

      Since Lex.DB has only upsert (no insert, no update), it cannot distinguish unassigned keys from assigned ones. So default values are used as cues.

      That is the best (non-intrusive) solution I came up with. Feel free to suggest any other ways.

        1. db.Map<Contact>().Automap(i => i.Id, true);

          In case of int, long or Guid type of Id property, the Id will be generated on Save.

  11. Hi Lex,

    Is LexDB usable on MacOS? Currently working on a Silverlight 5 PC/Mac OOB app and would love to try LexDB as it looks much easier to use than Sterling.


  12. Hi Lex,

    Can lex.db do insert or update like InsertAsnc or updateaasyc?
    or saveasync inclue both?

  13. Hi,, new on this and I trying lex.db

    I have two model classes extending a base class.. like

    public class Contact : Person

    public class User : Person

    I am mapping the classes Contact and User but during db.Initializing() I gets an error. Something like,,, “…. not known class Person …”

    Am I doing something wrong?

    1. Lex.DB does not respect inheritance hierarchies.
      Means Contact and User classes must be mapped separately.

      Person class is just a shared base class Lex.DB really knows nothing about.

        1. Contact or User classes probably have property of type Person.

          Lex.DB does not support nested objects.

          Possible usages:
          1) Add parallel foreign key property. In object property load/cache instance from DB, no setter.
          2) Mark property as [XmlIgnore] to skip automatic mapping in DB (no serialization).

  14. Hi Lex;

    I wanted to do the following things in my windows phone 8 application;

    I have to create local database contains 30+ Master tables and 20+ transaction tables.
    Every initial loading of App, these local master tables should fill with data(almost thirty thousand rows) by calling a service. (up-to 6000 rows in each master table).

    Then all the user transaction data need to capture(save/update) in local db ad update it with central server at the end of the day or instantly
    The application also should work in offline mode; and it needs a offline sync functionality to update the central database with local offline data.

    But i fear about first time data loading dealy; it will effect the app performance when downloading large data from the service and in the case offline sync too.

    Can i use LexDB in this scenario?
    Or How can i handle first time data downloading in faster way?
    please suggest me?

  15. I have a question:

    1. I backup all my source code to a jump drive.
    2. I delete all the source code.
    3. I copy back all the source code.
    4. All the data in the table are gone????? don’t know why???

    How can I backup the lex.db table data file in case of file went bad?
    Where is the table data file located?


    1. Hi Lex,

      2. I delete all the source code.
      3. I copy back all the source code.

      Where is the table data file located?

      I am using vs 2012 c# on a lapto to create a app, can I control where the .Data or the .Key file created? where are they located?


      1. Depending on platform.

        .NET & SL elevated application store data files in SpecialFolder.LocalApplicationData (c:\Users\%USER%\AppData\Local\Lex.Db\%DBName%\)

        .Windows Phone 8 & WinRT application stores data files in %YouAppIsolatedStorage%\Lex.Db\%DBName%\

        %YouAppIsolatedStorage% depends on your application Id. You may look for a folder named Lex.Db to find out where data is actually stored.

        In WinRT version there is an optional home parameter of type Windows.Storage.StorageFolder for DbInstance ctor, which controls, if specified, home folder for Lex.Db.

  16. I cannot install lex.db under NuGet, I get this error:Successfully

    installed ‘Lex.Db′.
    Successfully uninstalled ‘Lex.Db′.
    Install failed. Rolling back…
    Could not install package ‘Lex.Db′. You are trying to install this package into a project that targets ‘WindowsPhone,Version=v8.0′, but the package does not contain any assembly references that are compatible with that framework. For more information, contact the package author.

    I’m pretty new to WP8 programming so I’m kinda lost here.

  17. Hi Lex,

    Does it support multiple keys? If I want to get one record via two keys, how can I make it?


  18. I tried installing the nuget package. All versions right from 1.1 to 1.1.3 but they all failed for a WP8 app. I am using vs2012 update 2. Any ideas why? Thanks in Advance & thank you again for the work. This seems very promising & I would love to fiddle with it.

  19. How to load database from the installation folder in windows phone 8 app?
    I have tried few but no luck..
    db = new DbInstance(“appdata:/DbName”);

    db = new DbInstance(“appdata:/Lex.Db/DbName”);

    1. DbInstance path is always relative to local data folder. You need to copy files from installation folder to local data folder manually.

      This deployment problem could be addressed in next release as soon as time resource are available.

  20. I’m getting exception with the below code..Can you please give me a code for setting two components in one index

    .Automap(i => i.Qn_ID, true)
    .WithIndex(“Selection”, i => i.Qn_SubjectID + i.Qn_TopicID);

    var index = db.Table().IndexQueryByKey(“Selection”, “Anatomy”, “Neuro Anatomy”).ToList();

    1. I have got it…

      .Automap(i => i.Qn_ID, true)
      .WithIndex(“Selection”, i => i.Qn_SubjectID, i => i.Qn_TopicID);


      1. Sorry for delay. Glad, you’ve made it!

        I’m still looking for ways to simplify the API. In some places it could be … not that obvious 😉

        You feedback would be really appreciated.

  21. I have a very stupid question… but how do I update an existing record? Save obviously always adds a new record to the db.

    For the time being I just check if a record exist, take that, modify it, and call db.Table().Save() and put that modified record in it… and voila, there’s my update. But I guess it must be simplier 😉

    1. Let me check if I understood your question correctly:

      You want to update record, but you don’t know whether it exists or not? And in case it does not exist, you don’t want to add it to DB, right?

      And yes, Save() method does UPSET (UPDATE/INSERT) via primary key.

      1. Right, but I do want to add it to the DB if it does not exists.
        I got it working now… I just set the primary key wrong – I was referencing another Id (like product id) and I thought the PK has to be the of the table itself… but I just set the PK to be the and updating now is just fine. Sorry… being a DB beginner her 😉

        To clarify, my structure before was (autoincrement = true):
        id (PK) / prodId / text

        And now it’s just (autoincrement = true):
        prodId(PK) / text

  22. I am using lex.db in my windows store application , I ran Wack test and seems like if gets failed due to lex.db

    Supported API test

    FAILED Supported APIs Error Found: The supported APIs test detected the following errors: API GetFileAttributesW in kernel32.dll is not supported for this application type. Lex.Db.dll calls this API. API MoveFileW in kernel32.dll is not supported for this application type. Lex.Db.dll calls this API. Impact if not fixed: Using an API that is not part of the Windows SDK for Windows Store apps violates the Windows Store certification requirements. How to fix: Review the error messages to identify the API that is not part of the Windows SDK for Windows Store apps. Please note, C++ apps that are built in a debug configuration will fail this test even if it only uses APIs from the Windows SDK for Windows Store apps.

    Any idea what is wrong there and how this problem can be sorted out? I tried google it but seems like there is very small help available for lex.db.

  23. i have one entity in that i have one property of type list. while saving the data i am getting an exception that “Serialization of generic list” is not support in lexdb.

    can you please tell me what i need to do

  24. Hi Lex,
    i get an exception in the get_Path() method if i call db.Initialize()

    [System.NullReferenceException] {System.NullReferenceException: Object reference not set to an instance of an object.
    at Lex.Db.DbInstance.get_Path()} System.NullReferenceException

    I use Lex.Db in an abstract class library. I get the same exception if i create a new DbInstance with path and StorageFolder :(.

    I hope you will help me. Thx

    1. Xamarin.iOS support is currently unavailable due to iOS limitations on dynamic code generation Lex.DB heavy relies on (in particular Reflection.Emit and Linq.Expression.Compile).

      I’m in constant search for the different approach, but at this moment no known workarounds exist besides major rewrite of data serialization and instantiation logic using reflection what will heavily downgrade the performance of Lex.DB.

        1. I have just released Lex.Db 1.2 with Xamarin.iOS support. Took some time, but now it is truly cross-platform.

          Please keep me posted on results, especially performance related stuff.

          1. Lex,
            I am trying to use LexDB in Xamarin.iOS and it works perfectly in simulator but i am getting following error when testing on device: Attempting to JIT compile method ‘(wrapper delegate-invoke :invoke_callvirt_. Any idea what can be a reason? (LexDB, version

          2. Seems like some generic delegate is not precompiled AOT. Could you provide StackTrace?
            Also, could you also try to compile Lex.Db from sources using Xamarin.iOS compiler?

            It is possible, that VS2013 I use to produce Nuget packages is generating IL incompatible with iOS.
            Unfortunately, I have no iOS/OSX devices to develop on, I cannot test it myself.

  25. Lex,
    Please find the Stack below. I’ve also used Xamarin Scan (93% portable:
    at Lex.Db.Indexing.KeyIndex`2[TimeIt.iOS.TestModel,System.Guid].Update (App.iOS.TestModel instance, Int32 length) [0x00000] in :0
    at Lex.Db.DbTable`1[App.iOS.TestModel].WriteObjectData (IDbTableWriter writer,App
    .iOS.TestModel instance, System.Byte[] data, Int32 length) [0x00000] in :0
    at Lex.Db.DbTable`1[App.iOS.TestModel].Save (App.iOS.TestModel item) [0x00000] in :0
    at Lex.Db.DbInstance.Save[TestModel] (App.iOS.TestModel item) [0x00000] in :0
    at App.iOS.AppDelegate.FinishedLaunching (MonoTouch.UIKit.UIApplication app, MonoTouch.Foundation.NSDictionary options) [0x00117] in /Users/username/Projects/timeit-mobile/src/App.iOS/AppDelegate.cs:50
    at MonoTouch.UIKit.UIApplication.Main (System.String[] args, IntPtr principal, IntPtr delegate) [0x00005] in /Developer/MonoTouch/Source/monotouch/src/UIKit/UIApplication.cs:62
    at MonoTouch.UIKit.UIApplication.Main (System.String[] args, System.String principalClassName, System.String delegateClassName) [0x0001c] in /Developer/MonoTouch/Source/monotouch/src/UIKit/UIApplication.cs:45
    at App.iOS.Application.Main (System.String[] args) [0x00089] in /Users/username/Projects/timeit-mobile/src/App.iOS/Main.cs:28

    1. It looks like AOT compiler from Xamarin does not pregenerate several delegates. You may try to reference somewhere in your code Func<TimeIt.iOS.TestModel,System.Guid> delegate to make it precompiled in your app.

      It is probably needed for every combination entity/property types. I’ll check how to workaround this.

      1. Lex,
        I’ve tested some cases with different property types. Once type have property: Guid, DateTime, TimeSpan -> exception.
        As a temp solution I will use simple DTO to work with db.

  26. Lex,

    Can you please let me know when you plan to add Universal iOS app support. Lex.DB works perfect in classic apps but in Universal app I am getting errors below:

    – Property must be writable (link all assembly)
    – An exception was thrown by the type initializer for Lex.Db.Indexing.KeyIndex`2
    – Object reference not set to an instance of an object (link SDK assembly)
    P.S. Not sure why but can’t open Lex.Db iOS project in Xamarin on Mac.

  27. Lex,

    I’ve noticed that the serialization of my objects depends on the get; set; – but I was wondering if there’s a way I can make the set; only visible to Lex DB, and not other classes.

    I have tried using private set; and protected set; but then the table property is not serialized correctly.

    Any suggestions? I want to force our developers to use object contructor instead of setters, to reduce bugs.

  28. Thanks for sharing. An interesting library!
    The query speed is fast, but the writing speed is too slow. On my Macbook Air with Windows 8.1 installed, it takes a whole day to write 245Mb data. Any suggestion about this?

Leave a Reply

