title: SQLite: Retrieve child element from object in list
body: <p>There is a <code>Basket</code>, which stores a <code>List&lt;Fruit&gt;</code>. Each <code>Fruit</code> has one <code>Pip</code>. If I store this relationship and retrieve it later, the <code>ForeignKey</code> <code>PipId</code> has a value, but the object <code>Pip</code> is <code>null</code>, despite I use <code>CascadeRead</code>.</p>
  
  <p>If I try to use <code>CascadeOperation.All</code> on <code>FruitList</code> I get <code>Constraint</code></p>
  
  <pre><code>  at SQLite.PreparedSqlLiteInsertCommand.ExecuteNonQuery (System.Object[] source) [0x00116] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:2507 
    at SQLite.SQLiteConnection.Insert (System.Object obj, System.String extra, System.Type objType) [0x0014b] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:1386 
    at SQLite.SQLiteConnection.Insert (System.Object obj) [0x00008] in /Users/fak/Dropbox/Projects/sqlite-net/src/SQLite.cs:1224 
    at SQLiteNetExtensions.Extensions.WriteOperations.InsertElement (SQLite.SQLiteConnection conn, System.Object element, System.Boolean replace, System.Reflection.PropertyInfo primaryKeyProperty, System.Boolean isAutoIncrementPrimaryKey, System.Collections.Generic.ISet`1[T] objectCache) [0x0005a] in C:\home\mk\work\frameworks\sqlite-net-extensions\SQLiteNetExtensions\Extensions\WriteOperations.cs:270 
    at SQLiteNetExtensions.Extensions.WriteOperations.InsertElements (SQLite.SQLiteConnection conn, System.Collections.IEnumerable elements, System.Boolean replace, System.Collections.Generic.ISet`1[T] objectCache) [0x00069] in C:\home\mk\work\frameworks\sqlite-net-extensions\SQLiteNetExtensions\Extensions\WriteOperations.cs:238 
    at SQLiteNetExtensions.Extensions.WriteOperations.InsertValue (SQLite.SQLiteConnection conn, System.Object value, System.Boolean replace, System.Boolean recursive, System.Collections.Generic.ISet`1[T] objectCache) [0x0002c] in C:\home\mk\work\frameworks\sqlite-net-extensions\SQLiteNetExtensions\Extensions\WriteOperations.cs:219 
    at SQLiteNetExtensions.Extensions.WriteOperations.InsertChildrenRecursive (SQLite.SQLiteConnection conn, System.Object element, System.Boolean replace, System.Boolean recursive, System.Collections.Generic.ISet`1[T] objectCache) [0x0004c] in C:\home\mk\work\frameworks\sqlite-net-extensions\SQLiteNetExtensions\Extensions\WriteOperations.cs:200 
    at SQLiteNetExtensions.Extensions.WriteOperations.InsertWithChildrenRecursive (SQLite.SQLiteConnection conn, System.Object element, System.Boolean replace, System.Boolean recursive, System.Collections.Generic.ISet`1[T] objectCache) [0x0002b] in C:\home\mk\work\frameworks\sqlite-net-extensions\SQLiteNetExtensions\Extensions\WriteOperations.cs:181 
    at SQLiteNetExtensions.Extensions.WriteOperations.InsertWithChildren (SQLite.SQLiteConnection conn, System.Object element, System.Boolean recursive) [0x00000] in C:\home\mk\work\frameworks\sqlite-net-extensions\SQLiteNetExtensions\Extensions\WriteOperations.cs:59 
    at SQLiteNetExtensionsAsync.Extensions.WriteOperations+&lt;&gt;c__DisplayClass1_0.&lt;InsertWithChildrenAsync&gt;b__0 () [0x00013] in C:\home\mk\work\frameworks\sqlite-net-extensions\SQLiteNetExtensionsAsync-PCL\Extensions\WriteOperations.cs:55 
    at System.Threading.Tasks.Task.InnerInvoke () [0x0000f] in &lt;d18287e1d683419a8ec3216fd78947b9&gt;:0 
    at System.Threading.Tasks.Task.Execute () [0x00010] in &lt;d18287e1d683419a8ec3216fd78947b9&gt;:0 
  --- End of stack trace from previous location where exception was thrown ---
    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in &lt;d18287e1d683419a8ec3216fd78947b9&gt;:0 
    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x0003e] in &lt;d18287e1d683419a8ec3216fd78947b9&gt;:0 
    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in &lt;d18287e1d683419a8ec3216fd78947b9&gt;:0 
    at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in &lt;d18287e1d683419a8ec3216fd78947b9&gt;:0 
    at System.Runtime.CompilerServices.TaskAwaiter.GetResult () [0x00000] in &lt;d18287e1d683419a8ec3216fd78947b9&gt;:0 
    at TestSQLite.Database+&lt;StoreBasketAsync&gt;d__5.MoveNext () [0x0021b] in C:\Users\some-user\Documents\Visual Studio 2015\Projects\TestSQLite\TestSQLite\TestSQLite\Database.cs:189 
  </code></pre>
  
  <p>Furthermore, I tried to use <code>recursive: true</code> on <code>InsertWithChildrenAsync()</code>, but <code>Pip</code> is also <code>null</code>. Here is the example:</p>
  
  <p>Data model</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class Basket
  {
      private string number;
      private List&lt;Fruit&gt; fruitList;
  
      [PrimaryKey]
      public string Number
      {
          get { return this.number; }
          set { this.number = value; }
      }
  
      public string Name { get; set; }
  
      [OneToMany(CascadeOperations = CascadeOperation.CascadeRead)]
      public List&lt;Fruit&gt; FruitList
      {
          get { return this.fruitList; }
          set { this.fruitList = value; }
      }
  
      public Basket()
      {
  
      }
  }
  
  public class Fruit
  {
      private string number;
      private Pip pip;
  
      [PrimaryKey]
      public string Number
      {
          get { return this.number; }
          set { this.number = value; }
      }
  
      public string Type { get; set;}
  
      [ForeignKey(typeof(Pip))]
      public string PipId { get; set; }
  
      [OneToOne]
      public Pip Pip
      {
          get { return this.pip; }
          set { this.pip = value; }
      }
  
      [ForeignKey(typeof(Basket))]
      public string BasketId { get; set; }
  
      public Fruit()
      {  
      }
  
  }
  
  public class Pip
  {
  
      private string number;
      private string title;
  
      [PrimaryKey]
      public string Number
      {
          get { return this.number; }
          set { this.number = value; }
      }
  
      public string Title
      {
          get { return this.title; }
          set { this.title = value; }
      }
  
      public Pip()
      {
  
      }
  }
  </code></pre>
  
  <p>Database operations</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class Database
  {
      private readonly SQLiteAsyncConnection database;
  
      public Database(string databasePath)
      {
          this.database = new SQLiteAsyncConnection(databasePath);
          this.database.CreateTableAsync&lt;Basket&gt;().Wait();
          this.database.CreateTableAsync&lt;Fruit&gt;().Wait();
          this.database.CreateTableAsync&lt;Pip&gt;().Wait();
      }
  
      public async Task&lt;Basket&gt; GetBasketAsync(string basketId)
      {
          try
          {
              var queryResult = await this.database.Table&lt;Basket&gt;().Where(b =&gt; b.Number == basketId).CountAsync();
              if (queryResult &gt; 0)
              {
                  return await this.database.GetWithChildrenAsync&lt;Basket&gt;(basketId, true);
              }
              else
              {
                  return null;
              }
          }
          catch(Exception ex)
          {
              System.Diagnostics.Debug.WriteLine(ex.Message);
              return null;
          }
      }
  
      public async Task&lt;Fruit&gt; GetFruitAsync(string number)
      {
          try
          {
              var queryResult = await this.database.Table&lt;Fruit&gt;().Where(f =&gt; f.Number == number).CountAsync();
              if (queryResult &gt; 0)
              {
                  return await this.database.GetWithChildrenAsync&lt;Fruit&gt;(number, true);
              }
              else
              {
                  return null;
              }
          }
          catch(Exception ex)
          {
              System.Diagnostics.Debug.WriteLine(ex.Message);
              return null;
          }
      }
  
      public async Task&lt;Pip&gt; GetPipAsync(string number)
      {
          try
          {
              var queryResult = await this.database.Table&lt;Pip&gt;().Where(p =&gt; p.Number == number).CountAsync();
              if (queryResult &gt; 0)
              {
                  return await this.database.GetAsync&lt;Pip&gt;(number);
              }
              else
              {
                  return null;
              }
          }
          catch(Exception ex)
          {
              System.Diagnostics.Debug.WriteLine(ex.Message);
              return null;
          }
      }
  
      public async Task StoreBasketAsync(Basket basket)
      {
          if (basket == null)
              return;
  
          try
          {
              await this.StoreFruitListAsync(basket.FruitList);
  
              var foundItem = await this.GetBasketAsync(basket.Number);
              if (foundItem != null)
              {
                  await this.database.UpdateWithChildrenAsync(basket);
              }
              else
              {
                  await this.database.InsertWithChildrenAsync(basket);
              }
          }
          catch (Exception ex)
          {
              System.Diagnostics.Debug.WriteLine(ex.Message);
          }
      }
  
      public async Task StoreFruitListAsync(List&lt;Fruit&gt; fruitList)
      {
          if (fruitList == null || fruitList.Count == 0)
              return;
  
          try
          {
              foreach (Fruit fruit in fruitList)
              {
                  await this.StorePipAsync(fruit.Pip);
  
                  var foundItem = await this.GetFruitAsync(fruit.Number);
                  if (foundItem != null)
                  {
                      await this.database.UpdateWithChildrenAsync(fruit);
                  }
                  else
                  {
                      await this.database.InsertWithChildrenAsync(fruit);
                  }
              }
          }
          catch (Exception ex)
          {
              System.Diagnostics.Debug.WriteLine(ex.Message);
          }
      }
  
      public async Task&lt;int&gt; StorePipAsync(Pip pip)
      {
          if (pip == null)
              return 0;
  
          try
          {
              var foundItem = await this.GetPipAsync(pip.Number);
              if (foundItem != null)
              {
                  return await this.database.UpdateAsync(pip);
              }
              else
              {
                  return await this.database.InsertAsync(pip);
              }
          }
          catch (Exception ex)
          {
              System.Diagnostics.Debug.WriteLine(ex.Message);
              return 0;
          }
      }
  }
  </code></pre>
  
  <p>Test case</p>
  
  <pre class="lang-cs prettyprint-override"><code>public MainPage()
  {
      InitializeComponent();
  
      Pip pip = new Pip();
      pip.Number = "4";
      pip.Title = "pip from apple";
  
      Fruit apple = new Fruit();
      apple.Number = "1";
      apple.Pip = pip;
  
      Basket basket = new Basket();
      basket.Number = "10";
      basket.Name = "grandma";
      basket.FruitList = new List&lt;Fruit&gt;() { apple };
  
      this.basket = basket;
  }
  
  protected override async void OnAppearing()
  {
      base.OnAppearing();
  
      await App.Database.StoreBasketAsync(this.basket);
      Basket existingBasket = await App.Database.GetBasketAsync(this.basket.Number);
  }
  </code></pre>
  
  <p>I'm using the latest <code>SQLiteNetExtensions.Async v2.0.0-alpha2</code> NuGet package. How do I retrieve the child element <code>Pip</code> correctly?</p>