There is a Basket, which stores a List<Fruit>. Each Fruit has one Pip. If I store this relationship and retrieve it later, the ForeignKey PipId has a value, but the object Pip is null, despite I use CascadeRead.
If I try to use CascadeOperation.All on FruitList I get Constraint
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+<>c__DisplayClass1_0.<InsertWithChildrenAsync>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 <d18287e1d683419a8ec3216fd78947b9>:0
at System.Threading.Tasks.Task.Execute () [0x00010] in <d18287e1d683419a8ec3216fd78947b9>:0
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in <d18287e1d683419a8ec3216fd78947b9>:0
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x0003e] in <d18287e1d683419a8ec3216fd78947b9>:0
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in <d18287e1d683419a8ec3216fd78947b9>:0
at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in <d18287e1d683419a8ec3216fd78947b9>:0
at System.Runtime.CompilerServices.TaskAwaiter.GetResult () [0x00000] in <d18287e1d683419a8ec3216fd78947b9>:0
at TestSQLite.Database+<StoreBasketAsync>d__5.MoveNext () [0x0021b] in C:\Users\some-user\Documents\Visual Studio 2015\Projects\TestSQLite\TestSQLite\TestSQLite\Database.cs:189
Furthermore, I tried to use recursive: true on InsertWithChildrenAsync(), but Pip is also null. Here is the example:
Data model
public class Basket
{
private string number;
private List<Fruit> fruitList;
[PrimaryKey]
public string Number
{
get { return this.number; }
set { this.number = value; }
}
public string Name { get; set; }
[OneToMany(CascadeOperations = CascadeOperation.CascadeRead)]
public List<Fruit> 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()
{
}
}
Database operations
public class Database
{
private readonly SQLiteAsyncConnection database;
public Database(string databasePath)
{
this.database = new SQLiteAsyncConnection(databasePath);
this.database.CreateTableAsync<Basket>().Wait();
this.database.CreateTableAsync<Fruit>().Wait();
this.database.CreateTableAsync<Pip>().Wait();
}
public async Task<Basket> GetBasketAsync(string basketId)
{
try
{
var queryResult = await this.database.Table<Basket>().Where(b => b.Number == basketId).CountAsync();
if (queryResult > 0)
{
return await this.database.GetWithChildrenAsync<Basket>(basketId, true);
}
else
{
return null;
}
}
catch(Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
}
public async Task<Fruit> GetFruitAsync(string number)
{
try
{
var queryResult = await this.database.Table<Fruit>().Where(f => f.Number == number).CountAsync();
if (queryResult > 0)
{
return await this.database.GetWithChildrenAsync<Fruit>(number, true);
}
else
{
return null;
}
}
catch(Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
}
public async Task<Pip> GetPipAsync(string number)
{
try
{
var queryResult = await this.database.Table<Pip>().Where(p => p.Number == number).CountAsync();
if (queryResult > 0)
{
return await this.database.GetAsync<Pip>(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<Fruit> 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<int> 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;
}
}
}
Test case
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<Fruit>() { 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);
}
I'm using the latest SQLiteNetExtensions.Async v2.0.0-alpha2 NuGet package. How do I retrieve the child element Pip correctly?
Now I read the documentation and it states
Cascade read operations allow you to fetch a complete relationship tree from the database starting at the object that you are fetching and continuing with all the relationships with
CascadeOperationsset toCascadeRead
My Fruit class now looks like this
public class Fruit
{
[OneToOne(CascadeOperations = CascadeOperation.CascadeRead)]
public Pip Pip
{
get { return this.pip; }
set { this.pip = value; }
}
}
and it works as expected. I thought, that CascadeRead is only needed if I have an object, which has some further relationships, but that is not true. You need CascadeRead on all objects, which should get fetched recursively, regardless of how the object is build up.