C# Deserialize, then insert into SQLite database

Question

I have the following JSON for model Project, which I cannot change:

[
    {
        "id": 3,
        "description": "Project A"
    },
    {
        "id": 6,
        "description": "Project B",
        "updated_at": "2017-07-13T09:30:51+02:00",
        "created_at": "2017-07-13T09:30:51+02:00"
    }
]

I have the following JSON for model Task, which I cannot change:

[
    {
        "id": 3,
        "project": {
            "id": 3,
            "description": "Project A"
        },
        "description": "Task 1"
    },
    {
        "id": 6,
        "project": {
            "id": 3,
            "description": "Project A"
        },
        "description": "Task 2"
    },
    {
        "id": 9,
        "project": {
            "id": 3,
            "description": "Project A"
        },
        "description": "Task 3"
    },
    {
        "id": 12,
        "project": {
            "id": 6,
            "description": "Project B"
        },
        "description": "Task 4"
    }
]

I have the following 2 models:

using SQLite.Net.Attributes;
using SQLiteNetExtensions.Attributes;

namespace BettyMultiModel.Resources.Model
{
    public class Models
    {

    }

    class Project : Models
    {

        public int Id { get; set; }
        public string Description { get; set; }

    }

    class Task : Models
    {

        public int Id { get; set; }
        public string Description { get; set; }   

    }
}

I use this method to get the JSON into the SQLite database:

// Insert Records of type TType into database.
private async Task<string> InsertRecords<TType>(HttpResponseMessage response)
        where TType : Models
{
    try
    {
        DataBase db = new DataBase();

        string responseString = await response.Content.ReadAsStringAsync();
        var responseBody = JsonConvert.DeserializeObject<List<TType>>(responseString);


        foreach (TType item in responseBody)
        {
            bool resultInsert = db.Insert(item);
        }

        return ResponseToMessage(response, "Insert Records");
    }
    catch (Exception ex)
    {
        Log.Info("DeserializeObject", ex.Message);
        throw;
    }            

}

This works just fine when I only want to have the Id and Description properties of both models. But I would also like to store the relation between the Project model and the Task model / project id at the Task model.

When I parse the JSON with the tasks at http://json2csharp.com/, I get the following suggestion for the models:

public class Project
{
    public int id { get; set; }
    public string description { get; set; }
}

public class RootObject // Which is Task
{
    public int id { get; set; }
    public Project project { get; set; }  // <-------- Extra property
    public string description { get; set; }
}

Using the models like this - with RootObject renamed to Task - results in a Exception: NotSupportedException: Don't know about BettyMultiModel.Resources.Model.Project

So I changed the classes to:

using SQLite.Net.Attributes;
using SQLiteNetExtensions.Attributes;

namespace BettyMultiModel.Resources.Model
{
    public class Models
    {

    }

    class Project : Models
    {

        [PrimaryKey]
        public int Id { get; set; }
        public string Description { get; set; }

    }

    class Task : Models
    {

        [PrimaryKey]
        public int Id { get; set; }
        public string Description { get; set; }

        [OneToMany]
        public Project project { get; set; }

    }
}

The Exception is not thrown any longer, but when I have a look in the db file, there are still just the Id and Description columns and no reference to a relation between the 2 models.

What am I missing here?

EDIT: Added method that creates the tables

public void ClearTable<TType>()
        where TType : Models
{
    try
    {
        Log.Info("Folder DB: ", dbPath);

        using (var connection = new SQLiteConnection(platform, dbPath))
        {
            connection.DropTable<TType>();
            connection.CreateTable<TType>();
        }
    }
    catch (SQLiteException ex)
    {
        Log.Info("SQLiteEx", ex.Message);
    }

}

EDIT: Almost there...

I also made a little mistake, a project has many tasks, so the attribute should be [ManyToOne]. The tables in the db look OK now. The project id is just not inserted in the ProjectId column, inside the Task model. Every row has value 0. I guess I need to figure out how to map the id of the nested project object to the ProjectId property of the class Task.

I've tried using the Newtonsoft.Json namespace like this, which inserts 1 row in the Task model and then starts throwing errors because of record the uniqueness of the id. JsonProperty("id") identifies it as the Id of Task, not of Project:

[JsonProperty("id"), ForeignKey(typeof(Project))]
public int ProjectId { get; set; }

These are the models now:

class Project : Models
{

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

    public string Description { get; set; }

}

class Task : Models
{

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

    public string Description { get; set; }

    [ForeignKey(typeof(Project))]
    public int ProjectId { get; set; }

    [ManyToOne]
    public Project Project { get; set; }

}

Answer 1

Assuming, in your question you're using the Twin Coders SQLite-Net-Extensions package I think there is an error in how you define your model.

If you look at the Many to one section in linked Bitbucket page you need to define a ForeignKey attribute in addition to the ManyToOne attribute. This is their example (notice the Bus and BusId properties on the Person class):

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

    public string PlateNumber { get; set; }
}

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

    public string Name { get; set; }

    [ForeignKey(typeof(Bus))]
    public int BusId { get; set; }

    [ManyToOne]
    public Bus Bus { get; set; }
}

With your code, I believe all you have to do is use the following model:

class Project : Models
{
    [PrimaryKey]
    public int Id { get; set; }

    public string Description { get; set; }

    // The id of the task used in the relationship (this gets stored in the DB)
    [ForeignKey(typeof(Task))]
    public int TaskId { get; set; }

    // Meaning you have many projects to one task
    [ManyToOne]
    public Task Task { get; set; }
}

class Task : Models
{
    [PrimaryKey]
    public int Id { get; set; }

    public string Description { get; set; }
}