One to Many relationship in SQLite Xamarin

Question

I am building a Xamarin mobile app, and I have a set of products that I get from a rest api. Each product can have n number of images. I have set the relationship of the table using SQLite-Net Extensions. However, when I insert the values into the database, Images field has null value in the Product table and the Image table in empty.

My Model

    public class Product
{
    [SQLite.Net.Attributes.PrimaryKeyAttribute]
    public int ID { get; set; }
    public string Name { get; set; }
    [OneToMany] 
    public List<Image> Images { get; set; }
}

public class Image
{
    [SQLite.Net.Attributes.PrimaryKeyAttribute, SQLite.Net.Attributes.AutoIncrement]
    public int ID { get; set; }
    [ForeignKey(typeof(Product))]     // Specify the foreign key
    public int ProductID { get; set; }
    public string Link { get; set; }
}

The code that I am using to insert values.

var db = new SQLiteConnection(new SQLitePlatformAndroid(), path);
var info = db.GetTableInfo("ProductDB");
db.CreateTable<ProductDB>();
db.CreateTable<ImageDB>();
db.DeleteAll<ProductDB>();
db.DeleteAll<ImageDB>();
db.InsertAllWithChildren(data);
db.UpdateAll(data);

This is how i deserialise json to object

public async Task<List<ProductDB>> GetAllProducts()
    {
        var httpClient = GetHttpClient();

        var response = await httpClient.GetAsync(ServiceEndPoints.GetFilmsUri).ConfigureAwait(false);

        if (response.IsSuccessStatusCode)
        {
            var content = response.Content;

            string jsonString = await content.ReadAsStringAsync().ConfigureAwait(false);

            return JsonConvert.DeserializeObject<List<ProductDB>>(jsonString);
        }
        return null;
    }

It will be great if someone can help me in identifying what am I doing wrong.

score 1 · Answer 1 · answered Mar 14 '16 at 15:42

db.InsertAllWithChildren(data);
db.UpdateAll(data);

You insert values with their child values, and then update with same values, with ProductID values being updated with null, as they should be set by database. If you want to update your data, your should set foreign key for each item being inserted, or use UpdateWithChildren

redent84 · Answer 2 · 2016-03-16T09:53:17.487

1

I guess that data is a List<Product>. In this case, you are only inserting products in the database and no images are being inserted.

You have two options here: insert images manually or use recursive operations.

To insert images manually, simply call InsertAll method with all your images before inserting your products:

List<Image> images = data.SelectMany(p => p.Images ?? new List<Image>());
conn.InsertAll(images);
conn.InsertAllWithChildren(data);

Using recursive operations is simpler but requires minor configuration. Enable cascade operations by setting the CascadeOperation property of the relationship attribute:

[OneToMany(CascadeOperations = CascadeOperation.All)] 
public List<Image> Images { get; set; }

Then, you can set the recursive parameter of any -WithChildren method to true to perform the cascade operation:

conn.InsertAllWithChildren(data, recursive: true);

edited Mar 16 '16 at 09:53

answered Mar 14 '16 at 17:28

redent84

18,901
4
62
85

I tried both these methods. For the first approach. When i debug through the code, I lost the debug control after the line. List images = data.SelectMany(p => p.Images).ToList();, and the focus does not return after the execution of that line. And I have checked the data, is not getting populated either. For the second approach, the code executes well, but there is no images listed. – Libin Joseph Mar 14 '16 at 20:53
Maybe some `Images` are null? I've added the null coalescing operator to solve it. What do you mean by 'there is no images listed'? Are you using a database browser or are you listing the elements programmatically? If it's the second, you should show your code. – redent84 Mar 16 '16 at 09:54

One to Many relationship in SQLite Xamarin

2 Answers2