Creating Complex Tables using SQLite.Net

August 22, 2016

Based on this earlier post, I returned to my test project to find that it didn’t actually pass anymore. The first issue was that the test couldn’t locate Sqllite3.dll.

Thanks to this question on every programmers favourite web site, I realised that I needed to add an extension for SQLite.

The next issue was when I tried to add a test to create a complex table:

Don’t know about [EntityName].

What I mean by a complex table, and the thing that CreateTable doesn’t like, is an entity that references another entity (the same is true if you try to reference the same entity).

Finally, you’re not allowed to use the System.Object type:

A fix

It is my understanding that, if you can get it to work with SQLite, Entity Framework will solve this problem for you. I took more of a roll-your-own approach. The key issue here is that you want SQLite to not try to serialise the second object; and for this, you can use the [Ignore] attribute:



private ProductCategory \_category;
 
[Ignore]
public ProductCategory Category
{
    get { return \_category; }
    set
    {
        \_category = value;
        RaisePropertyChanged();
    }
}

So, that stops it crashing. The next part is largely dependent on your data: if you have a basic primary / foreign key one-to-n mapping then this will work; however, anything more complex, and you’ll probably have to write a custom abstraction for the data. All that said, my solution starts with a base entity class:



public abstract class BaseDataEntity : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;
 
    protected void RaisePropertyChanged([CallerMemberName] string propertyName = null)
    {
        var handler = PropertyChanged;
        if (handler != null)
            handler(this, new PropertyChangedEventArgs(propertyName));
    }
 
    public abstract string Key { get; set; }
}

The PropertyChanged goes without saying (it’s like the roads), but the interesting part is the Key. Based on the code above, each entity needs a uniquely identifiable key; in this case, it’s a string; however, this can be any primitive type. In the derived class, you’ll need to map the type to the data; for example:


 
[Ignore]
public ProductCategory Category
{
    get { return \_category; }
    set
    {
        \_category = value;
        RaisePropertyChanged();
    }
}
 
public string CategoryRef
{
    get { return Category.Key; }
    set
    {
        if (Category == null)
        {
            Category = new ProductCategory()
            {
                Key = value
            };
        }
    }
}

I’ll explain CategoryRef later on.

The next thing is to put some logic into the data access (I simply created a DataAccessBase class to sit on top of the SQLite data manipulation. Here is the abstraction to add a record:


 
public void Add(Type objType, object t)
{
    \_connection.Insert(t);
 
    // Look for any ignored properties
    var ignored = objType                
        .GetProperties().Where(p =>
            p.CustomAttributes.Any(a => a.AttributeType == typeof(SQLite.Net.Attributes.IgnoreAttribute)));
 
    // For each, look for a reference
    foreach (var p in ignored)
    {
        // Determine the type first and get the value
        Type propertyType = p.PropertyType;
        var propertyValue = p.GetValue(t);
 
        // Recursively call this function to add the reference
        Add(propertyType, propertyValue);
    }
}

As you can see, for each reference, it simply recursively calls itself to add that, too. This will spectacularly fall down if you have a circular reference (so don’t do that, or don’t use this if you do).

Next is the Get:


 

public T Get<T>(string key) where T : class
{
    T data = \_connection.Get<T>(key);
 
    var ignored = typeof(T)
        .GetProperties()
        .Where(p => p.GetCustomAttributes(typeof(SQLite.Net.Attributes.IgnoreAttribute), inherit: true).Any())
        .ToList();
 
    // For each, look for a reference
    foreach (var p in ignored)
    {
        // Get the reference property
        string propName = $"{p.Name}Ref";
        PropertyInfo refProp = typeof(T).GetProperty(propName);
 
        // Get its value (we know it's a string)
        string val = refProp.GetValue(data).ToString();
 
        // Determine the type first and get the value
        Type propertyType = p.PropertyType;
        
        var mapping = \_connection.TableMappings.First(m => m.MappedType == propertyType);
        var rec = \_connection.Find(val, mapping);
 
        // Update the property
        p.SetValue(data, rec);
 
    }
}

Here, I’m using a convention based approach; so, where I reference a separate class, I also have separate property called [Class]Ref; which you can see earlier on in the example of the entity code.

Conclusion

And that’s it; I make no assertions about edge cases, but for basic data that references other basic data, this works fine.

Acknowledgements

I have a great deal of help in creating this, but mainly just from random search results; however, I did ask a question on Stack Overflow