Trouble forming data structure for SQLite in C#

Question

I am trying to build a very simple Xamarin app that functions similarly to a Exercise/Day Planner. The SQLite database should hold a "Day"s table, and every day should relate to an "Activity"s table which it can query that days "Activity"s. These "Activity"s should be of a certain type, for simplicity lets say either "Walk", "Run" or "Swim". The user should then be able to perform basic CRUD operations on this database to recall previous days and it's associated activities. Ideally, using the SQLiteNetExtensions library, I should be able to make a simple call to retrieve data:

SQLiteAsyncConnection connection = DependencyService.Get<ISQLiteDb>().GetConnection();
var day = await connection.GetWithChildrenAsync<Day>(primaryKey, recursive = true);

Models (simplified):

using SQLite;
using SQLiteNetExtensions.Attributes;
using System;
using System.Collections.Generic;

public class Day {

    public Day()
    {
        Date = DateTime.Now;
    }

    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public DateTime Date { get; set; }

    [OneToMany]
    public List<Activity> Activities { get; set; }
}

public abstract class Activity
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [ManyToOne(typeof(Day))]
    public Day Day { get; set; }

    [ForeignKey]
    public int DayRefId { get; set; }

    public double Input { get; set; }

    public virtual double OutputKms { get; set; }
}

public class Walk : Activity 
{
    public override double OutputKms { get { return Input * 3; } }
}

public class Run: Activity 
{
    public override double OutputKms { get { return Input * 5; } }
}

public class Swim: Activity 
{
    public override double OutputKms { get { return Input * 3; } }
}

I am initializing my database with the following code:

using SQLite;
using Xamarin.Forms;
using SQLiteNetExtensionsAsync.Extensions;    

public async static Task InitDb()
{
    SQLiteAsyncConnection connection = DependencyService.Get<ISQLiteDb>().GetConnection();

    await connection.CreateTableAsync<Day>();
    await connection.CreateTableAsync<Activity>();
    await connection.CreateTableAsync<Walk>();
    await connection.CreateTableAsync<Run>();
    await connection.CreateTableAsync<Swim>();
}

The problem is that SQLite doesn't like tables based on abstract classes. On the line where I try to create the "Activity" table I get the following compilation error:

Error CS0310 'Activity' must be a non-abstract type with a public parameterless constructor in order to use it as parameter 'T'

I know working with abstract classes is supported in Entity Framework but I would rather stick to using SQLite if possible.

This seems like a simple problem but has been tricky for me to work out without resorting to the simplest poco objects and writing lots of repetitive code.

My question is how can I better restructure my code, what libraries can I use or what kind of mapping can I write in order to implement this. Appreciate any feedback. Thank you!

score 1 · Accepted Answer · answered Dec 13 '19 at 16:23

All of the types in Sqlite.net and Sqlite.net Extensions are T : new() so you are not going to be able to use abstract classes. Code like this:

[OneToMany]
public List<Activity> Activities { get; set; }

where Activity is an abstract class is not going to work, as it would be very hard to work out what table the data should come from.

It may construct the tables for Activity, Walk, Swim, Run (If you make the Activity not abstract) but Sqlite.net Extensions won't be able to create the relationships and thus won't pull back the data.

Personally I wouldn't have the logic of this property in the model that you are persisting, (You may have done this to simplify the question and there is more logic):

public override double OutputKms { get { return Input * 3; } }

You could refactor to have whatever the multiplier of 3, 5, 3 in Walk, Run, Swim out to property or store an activity type enum in the activity class and not make it abstract like:

public class Activity
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [ManyToOne(typeof(Day))]
    public Day Day { get; set; }

    [ForeignKey]
    public int DayRefId { get; set; }

    public double Input { get; set; }

    public ActivityType ActivityType { get; set; }
}

public enum ActivityType
{
    Walk,
    Run,
    Swim
}

Another option would be to create different models for the database that are Pocos that map to the more complex app models. Possibly using AutoMapper

Thank you so much for taking the time to write such a comprehensive answer. I never considered using an enum for the activity type. I think that's the way to go. — Oigeen, Dec 13 '19 at 23:43

Trouble forming data structure for SQLite in C#

1 Answers1