代码之家 › 专栏 › 技术社区 › testing

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

sqlite-net-extensions sqlite-net xamarin sqlite c#

testing · 技术社区 · 7 年前

Basket List<Fruit> 每个 Fruit 有一个 Pip 。如果我存储此关系并稍后检索,则 ForeignKey PipId 具有值,但对象 null CascadeRead .

CascadeOperation.All 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

此外,我试图使用 recursive: true 在…上 InsertWithChildrenAsync() Pip公司 也是 无效的

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 Pip公司 正确地

1 回复 | 直到 7 年前

testing 7 年前

现在我读了 documentation

级联读取操作允许您从正在获取的对象开始并继续从数据库中获取完整的关系树 所有与的关系 CascadeOperations 设置为 CascadeRead

Fruit 课堂现在看起来像这样

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

它按预期工作。我想 级联读取 只有当我有 object 级联读取 在所有对象上,无论对象是如何构建的,都应该递归获取。