Join operation in SQLite in Xamarin form app

Question

I am new in Xamarin forms. I have created two tables & trying to perform Join operation on these two tables but I am unable to join the table.

I have already gone through these links but no success

Link1 Link2

Can anyone help me how to join two tables in Xamarin forms. Below is my code for JOIN operation

public Thoughts GetQueryForAllRecords(int ID)
{
    var q = connection.Query<Thoughts>("SELECT Thought,CreatedOn, AssignedToName, AssignedOn FROM Thoughts JOIN AssignedThought ON Thoughts.ID = AssignedThought.ID where Thoughts.ID=?;", ID);

    return q.Last(t => t.ID == ID);
}

But above code only returns 'Thoughts' table recods not from 'AssignedThought' table.

Please suggest what am I doing wrong here.

score 2 · Accepted Answer · answered Apr 24 '17 at 08:03

I think you can take a look to sqlite-net-extensions

SQLite-Net Extensions is a very simple ORM that provides one-to-one, one-to-many, many-to-one, many-to-many, inverse and text-blobbed relationships on top of the sqlite-net library. sqlite-net is an open source, minimal library to allow .NET and Mono applications to store data in SQLite 3 databases. SQLite-Net Extensions extends its funcionality to help the user handle relationships between sqlite-net entities.

public class Stock
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        [MaxLength(8)]
        public string Symbol { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]      // One to many relationship with Valuation
        public List<Valuation> Valuations { get; set; }
    }


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

            [ForeignKey(typeof(Stock))]     // Specify the foreign key
            public int StockId { get; set; }
            public DateTime Time { get; set; }
            public decimal Price { get; set; }

            [ManyToOne]      // Many to one relationship with Stock
            public Stock Stock { get; set; }
        }

Here's how we'll create, read and update the entities:

var db = Utils.CreateConnection();
db.CreateTable<Stock>();
db.CreateTable<Valuation>();

var euro = new Stock() {
    Symbol = "€"
};
db.Insert(euro);   // Insert the object in the database

var valuation = new Valuation() {
    Price = 15,
    Time = DateTime.Now,
};
db.Insert(valuation);   // Insert the object in the database

// Objects created, let's stablish the relationship
euro.Valuations = new List<Valuation> { valuation };

db.UpdateWithChildren(euro);   // Update the changes into the database
if (valuation.Stock == euro) {
    Debug.WriteLine("Inverse relationship already set, yay!");
}

// Get the object and the relationships
var storedValuation = db.GetWithChildren<Valuation>(valuation.Id);
if (euro.Symbol.Equals(storedValuation.Stock.Symbol)) {
    Debug.WriteLine("Object and relationships loaded correctly!");
}

Join operation in SQLite in Xamarin form app

1 Answers1