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!

You cannot have a field type as "AgeRange" in table because SQLite only supports a small set of data types. Since the class AgeRange has only one property, why don't you add its int property into FacebookProfile and probably setting its annotation JsonIgnore? — VahidShir, Jan 29 '18 at 20:46
And in the setter of AgeRange property you can set min property of FacebookProfile from the value. — VahidShir, Jan 29 '18 at 20:55
FYI: Your tag of SQLite.net is not the same thing as the sqlite-net-pcl... i'll edit that for you. However, in general you should keep your db model objects to simple / standard (no custom) types if you want to use Sqlite-net in this way. — maplemale, Jan 29 '18 at 23:58

maplemale · Accepted Answer · 2018-01-30T18:31:34.837

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);
         }
}

}

Thank you for the response. But when I try to access the data from dbFacebookProfile by using FacebookProfile = conn.Table().FirstOrDefault(); it says I cannot access dbFacebookProfile with parameterless constructor. How do I access it? — suman_dh, Jan 30 '18 at 12:48
For selecting using conn.Table<>(), just add another constructor to my code that is parameter-less. Edited my example. — maplemale, Jan 30 '18 at 18:31

score 0 · Answer 2 · answered Jan 30 '18 at 06:48

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; }   
    }

How to connect two tables in Xamarin Forms?

2 Answers2