How to connect two tables in Xamarin Forms?

Question

This might be a silly question but I am having trouble saving in sqlite. I have used facebook login from XF successfully, but also want to save the info from there in sqlite-net-pcl. Two models are:

public class FacebookProfile
    {
        public string Name { get; set; }
        public string Locale { get; set; }
        public string Link { get; set; }
        [OneToOne]
        [JsonProperty("age_range")]
        public AgeRange AgeRange { get; set; }
        [JsonProperty("first_name")]
        public string FirstName { get; set; }
        [JsonProperty("last_name")]
        public string LastName { get; set; }
        public string Gender { get; set; }
        public bool IsVerified { get; set; }
        [PrimaryKey]
        public int  Id { get; set; }
    }

public class AgeRange
    {
        public int Min { get; set; }
    } 

I have successfully downloaded the info to my FacebookProfile model and can display it. But, when I am trying to save it in my sqlite database, AgeRange property of FacebookProfile saves as null in the database and it should have a certain value. The code for saving using sqlite connection is:

using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.DB_PATH))
            {
                conn.CreateTable<FacebookProfile>();
                conn.Insert(fp);   //fp is the FacebookProfile model info gotten from Facebook API and until this point, fp has agerange min value 
             }

After this point the agerange inserted in database is saved as null, I dont know why or I might be doing something wrong. So, when I try to retrieve this using following code in the viewmodel:

using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.DB_PATH))
            {
                conn.CreateTable<FacebookProfile>();
                FacebookProfile = conn.Table<FacebookProfile>().FirstOrDefault();
            }

The FacebookProfile retrieved from database has agerange value null but others I can get correct information. What might be the problem? Please help!

Answer 1

Sqlite-Net-pcl is not going to recognize custom poco objects such as:

public AgeRange AgeRange { get; set; }

Keep in mind, that Sqlite-net-pcl is basically just a wrapper that saves you the hassle of interacting directly with the Sqlite library. So, instead of having to do things like this:

SQLiteCommand insertSQL = new SQLiteCommand("INSERT INTO FacebookProfile (Locale, Link, AgeRange, FirstName, LastNameGender, IsVerified, Id) VALUES (?,?,?,?,?,?,?)", conn);

insertSQL.Parameters.Add(facebookProfile.Locale);
    insertSQL.Parameters.Add(facebookProfile.Link);
    insertSQL.Parameters.Add(facebookProfile.AgeRange); //Assuming this is an int and not a custom type
    insertSQL.Parameters.Add(facebookProfile.FirstName);
    insertSQL.Parameters.Add(facebookProfile.LastNameGender);
    insertSQL.Parameters.Add(facebookProfile.IsVerified);
    insertSQL.Parameters.Add(facebookProfile.Id);

insertSQL.ExecuteNonQuery();

You can simply say:

using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.DB_PATH))
            {
                conn.CreateTable<FacebookProfile>();
                conn.Insert(fp);   //fp is the FacebookProfile model info gotten from Facebook API and until this point, fp has agerange min value 
             }

But, it doesn't know what to do with your AgeRange class. Is that a string? Is, that an int? What is it as far as Sqlite is concerned? As others have said in the comments, the wrapper doesn't know how to interpret that class into a database type such as STRING, NUMERIC etc.

In general, I have had better success (when utilizing sqlite-net-pcl) creating an exact copy of a table structure as an intermediary class object and inserting/updating those via your parent class of FacebookProfile. This is referred to in programming patterns as the Anemic domain model where: "Anemic domain model is the use of a software domain model where the domain objects contain little or no business logic (validations, calculations, business rules etc.)."

In your case it may look something like this:

public class dbFacebookProfile
{
    dbFacebookProfile(){}

    dbFacebookProfile(FacebookProfile fbProfile)
    {
        this.Name = fbProfile.Name;
        this.Locale = fbProfile.Locale;
        this.AgeRange fbProfile.AgeRange.Min; //or, however you want to modify this object to get the age range (Min?) from the object.
    }

public SaveToDB()
{
using (SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(App.DB_PATH))
        {
            conn.CreateTable<this>();
            conn.Insert(this);
         }
}

}

Answer 2

I think since both FacebookProfile and AgeRange can be said to be logically related you could mix these two into one table:

public class FacebookProfile
    {
        public string Name { get; set; }
        public string Locale { get; set; }
        public string Link { get; set; }
        [OneToOne]
        [Ignore]
        [JsonProperty("age_range")]
        public AgeRange AgeRange
        {
           get => _ageRange;
           set
           {
             _ageRange = value;
             AgeMin = value?.Min ?? 0;
           }
        }
        [JsonProperty("first_name")]
        public string FirstName { get; set; }
        [JsonProperty("last_name")]
        public string LastName { get; set; }
        public string Gender { get; set; }
        public bool IsVerified { get; set; }
        [PrimaryKey]
        public int  Id { get; set; }

        [JsonIgnore]
        public int  AgeMin{ get; set; }   
    }