title: Xamarin Forms - Using SQLite and SQLite Extensions to implement foreign keys
body: <p>I'm new to Xamarin forms and am up to the point where I now want to be persisting data entered by the user to an Sqlite db. Thankfully, there all plenty of examples to <strong>get you started</strong>, but thats as far as the help goes... I'm trying to implement a relationship between two entities <strong>'Session'</strong> and <strong>'HandHistory'</strong>.</p>
  
  <p>A Session can have multiple HandHistories - immediately I saw that some sort of foreign key would be needed here to link these tables/entities together. I read in multiple articles and stack overflow questions that the standard 'sqlite-net-pcl' (by Frank A.Krueger) package offers nothing in terms of foreign keys, and that in order to acquire the functionality I needed to use the SQLiteNetExtensions library. I referred to this article for help: </p>
  
  <p><a href="https://bitbucket.org/twincoders/sqlite-net-extensions/overview" rel="nofollow noreferrer">https://bitbucket.org/twincoders/sqlite-net-extensions/overview</a></p>
  
  <p>My entities look like this:</p>
  
  <p>Session:</p>
  
  <pre><code>using SQLite;
  using SQLiteNetExtensions.Attributes;
  
  namespace PokerSession.Models
  {
  [Table("Session")]
  [AddINotifyPropertyChangedInterface]
  public class Session
  {
      public Session(bool newSession)
      {
          if (newSession)
          {
              CurrentlyActive = true;
              //HandHistories = new ObservableCollection&lt;HandHistory&gt;();
          }
      }
  
      [PrimaryKey, AutoIncrement]
      public int Id { get; set; }
  
      public SessionType SessionType { get; set; } = SessionType.Tournament;
  
      public string Location { get; set; }
  
      public DateTime Date { get; set; } = DateTime.Now;
  
      public string GeneralNotes { get; set; }
  
      public int MoneyIn { get; set; }
  
      public int MoneyOut { get; set; }
  
      public int ProfitLoss
      {
          get
          {
              var p = MoneyOut - MoneyIn;
              if (p &lt; 0)
                  return 0;
              return p;
          }
      }
  
  
      /// &lt;summary&gt;
      /// If the session has not been completed, set this to true
      /// &lt;/summary&gt;
      public bool CurrentlyActive { get; set; }   
  
  
      [OneToMany(CascadeOperations = CascadeOperation.All)]
      public ObservableCollection&lt;HandHistory&gt; HandHistories { get; set; }    
  }
  </code></pre>
  
  <p>}</p>
  
  <p>HandHistory:</p>
  
  <pre><code>using SQLite;
  using SQLiteNetExtensions.Attributes;
  
  namespace PokerSession.HandHistories
  {
  [Table("HandHistory")]
  [AddINotifyPropertyChangedInterface]
  public class HandHistory
  {
      public HandHistory()
      {
  
      }
  
      [PrimaryKey, AutoIncrement]
      public int Id { get; set; }
  
      [ForeignKey(typeof(Session))]
      public int SessionId { get; set; }
  
      [ManyToOne]
      public Session Session { get; set; }
  }
  </code></pre>
  
  <p>}</p>
  
  <p>I also followed this article for the platform specific implementations for obtaining the SQLiteConnection for the local db:</p>
  
  <p><a href="https://developer.xamarin.com/guides/xamarin-forms/application-fundamentals/databases/" rel="nofollow noreferrer">https://developer.xamarin.com/guides/xamarin-forms/application-fundamentals/databases/</a></p>
  
  <p><strong>The error I'm getting:</strong></p>
  
  <blockquote>
    <p>'SQLiteConnection' does not contain a definition for 'UpdateWithChildren' and the best extension method overload 'WriteOperations.UpdateWithChildren(SQLiteConnection, object)' requires a receiver of type 'SQLiteConnection' PokerSession.Android, PokerSession.iOS  C:\Poker Notes Live\PokerSession\PokerSession\PokerSession\Services\DataService.cs  46  Active</p>
  </blockquote>
  
  <pre><code>        private SQLiteConnection _database;
  
      public DataService()
      {
          _database = DependencyService.Get&lt;ISqLite&gt;().GetConnection();
  
          _database.GetTableInfo("HandHistory");
          _database.CreateTable&lt;Session&gt;();
          _database.CreateTable&lt;HandHistory&gt;();
  
  
          var session = new Session(false)
          {
              Location = "Test Location",
              Date = new DateTime(2017, 08, 26),
              MoneyIn = 35,
              MoneyOut = 0,
              SessionType = SessionType.Tournament,
              GeneralNotes = "blah blah"
          };
          var hh = new HandHistory();
  
          _database.Insert(session);
          _database.Insert(hh);
  
          session.HandHistories = new ObservableCollection&lt;HandHistory&gt; {hh};
  
          _database.UpdateWithChildren(session);
      }
  </code></pre>
  
  <p>So basically it's not allowing me to use the SQLite Extension methods with my SQLiteConnection object (_database) which is confusing as this is the whole point behind the Extension methods? Surely they're made to work with the SQLiteConnection object?? I've also noticed through my playing around that there seems to be two different types of SQLiteConnection... The one I'm currently using is in the 'SQLite' namespace, and another one in the SQLite.Net namespace. I have checked the one in the SQLite.Net namespace and it does seem to like the Extension methods but it requires me to change my platform specific implementation for obtaining the SQLiteConnection, but it would fail at runtime (complaining about my Session entity not having a PK??).</p>
  
  <p>Quite a long winded question I know but it's better to provide more information than not enough, and I'm sure there must be others experiencing similar problems so please comment and offer any help possible, thank you.</p>