Unable to get Item from SQLite.Net Async PCL

Question

I am struggling to get an Item by ID using the asynchronous API of SQLite.Net Async PCL. Here is my model class

public class Invoice : IEntityBase
{
    public Invoice()
    {
        LineItems = new List<LineItem>();
        DateCreated = DateTime.Now;                
    }

    [PrimaryKey, AutoIncrement, Column("_id")]
    public int Id { get; set; }
    public DateTime DateCreated { get; set; }
    public int Term { get; set; }
    public bool Paid { get; set; }
    public decimal Total { get; set; }
    public string Notes { get; set; }

    [OneToMany(CascadeOperations = CascadeOperation.All)] 
    public List<LineItem> LineItems { get; set; }    

}

And the LineItems that has a One to Many relationship here

[PrimaryKey, AutoIncrement, Column("_id")]
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public decimal Price { get; set; }
public string Category { get; set; }
public int Qty { get; set; }

[ForeignKey(typeof(Invoice))]
public int InvoiceId { get; set; }

[ManyToOne]
public Invoice Invoice { get; set; }

Here is the constructor:

public SQLiteAsyncConnection DbConnection;

public InvoiceDatabase(ISQLitePlatform platform, string databasePath)
{
    if (DbConnection == null)
    {
        var connectionAsync = new Func<SQLiteConnectionWithLock>(() =>
                new SQLiteConnectionWithLock
                    (
                        platform,
                        new SQLiteConnectionString(databasePath, false) 
                    )
                );
        DbConnection = new SQLiteAsyncConnection(connectionAsync);
        DbConnection.CreateTableAsync<Invoice>();                    
        DbConnection.CreateTableAsync<LineItem>();                                  
    }             
}

Other CRUD methods (Insert, GetALL) is working except getting an Invoice by ID, and both Visual Studio and Xamarin Studio are not giving me any useful stacktrace.

Here is the Get Method

private readonly InvoiceDatabase _database;

public InvoiceRepository(ISQLitePlatform platform, string databasePath)
{
    if (_database == null)
    {
        _database = new InvoiceDatabase(platform, databasePath);
    }
}

public async Task<Invoice> GetInvoice(int id)
{
    var result = await _database.DbConnection.Table<Invoice>()
                            .Where(t => t.Id == id)
                            .FirstOrDefaultAsync();
    return result;
}

I am passing in the Android implementation of SQLite, and like I said the Database is created but I am unable to get the Invoice object back, I even tried

public Task<Invoice> GetInvoiceWithChildren(int id)
{
    return _database.DbConnection.GetWithChildrenAsync<Invoice>(id);
}

Any Help will be greatly appreciated.

Your question is confusing. You state you tried two different constructors, but you also say that all the other CRUD operations work. If the CRUD operations work, obviously the DB is created so the constructor is not the issue. Are you getting an error on the Get operation? Or is it just returning null? — Jason, Mar 24 '15 at 23:54
I will disagree that my question is confusing. I shared the steps I have taken and stated that I am having problem with only the Get Item by ID and that Visual Studio is not give me any meaningful error. Thanks for your time anyway. — Val Okafor, Mar 25 '15 at 00:21

score 3 · Accepted Answer · edited Jun 23 '15 at 18:13

After three days of chasing shadows it turned out that it is just a very simple thing that is tripping me up. I am tying to save a List of objects like so

[OneToMany(CascadeOperations = CascadeOperation.All)] 
public List<LineItem> LineItems { get; set; }

I missed the part of the documentation that repeats the fact that SQLite.Net is a lightweight ORM - that point could not be stressed enough so you will have to remove your full size ORM hats such EF. So after reading from the SQLite-Net Extension documentation which says

Text blobbed properties Text-blobbed properties are serialized into a text property when saved and deserialized when loaded. This allows storing simple objects in the same table in a single column. Text-blobbed properties have a small overhead of serializing and deserializing the objects and some limitations, but are the best way to store simple objects like List or Dictionary of basic types or simple relationships.

I change my proptery like so and everything is now working as expected. Off now to dealing with the nuances of Async and Await

[TextBlob("LineItemBlobbed")]
public List<LineItem> LineItems { get; set; }

public string LineItemBlobbed { get; set; }

Unable to get Item from SQLite.Net Async PCL

1 Answers1