SQLite:从列表中的对象中检索子元素

Question

有一个Basket，它存储一个List<Fruit>。每个Fruit都有一个Pip。如果我存储这个关系并在以后检索它，那么ForeignKey PipId有一个值，但是对象Pip是null，尽管我使用了CascadeRead。

如果我试图在FruitList上使用FruitList，就会得到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 

此外，我尝试在InsertWithChildrenAsync()上使用InsertWithChildrenAsync()，但Pip也是null。下面是一个例子：

数据模型

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()
    {

    }
}

数据库操作

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

测试用例

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

我正在使用最新的SQLiteNetExtensions.Async v2.0.0-alpha2 NuGet包。如何正确检索子元素Pip？

Answer 1

现在我读了文档，它说

级联读取操作允许您从数据库中获取一个完整的关系树，从您要获取的对象开始，并继续使用CascadeOperations CascadeRead 的所有关系。

我的Fruit类现在看起来像这样

public class Fruit
{
    [OneToOne(CascadeOperations = CascadeOperation.CascadeRead)]
    public Pip Pip
    {
        get { return this.pip; }
        set { this.pip = value; }
    }    
}

它的工作就像预期的那样。我想，只有当我有一个有更多关系的CascadeRead时，才需要object，但事实并非如此。您需要所有对象上的CascadeRead，不管对象是如何构建的，都应该以递归的方式获取这些对象。