Repository.cs

using System;
using System.Collections.Generic;
using System.Diagnostics.Contracts;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using SQLite;

namespace MobCat.Data.Repositories
{
    public interface IDataRepository<TEntity, TKey>
    {
        Task CreateAsync(CancellationToken cancellationToken = default(CancellationToken));
        Task TruncateAsync(CancellationToken cancellationToken = default(CancellationToken));
        Task DropAsync(CancellationToken cancellationToken = default(CancellationToken));

        Task<int> CountAsync(CancellationToken cancellationToken = default(CancellationToken));

        Task<List<TEntity>> FindAsync(CancellationToken cancellationToken = default(CancellationToken));
        Task<List<TEntity>> FindAsync<TOrderKey, TThenKey>(
            Expression<Func<TEntity, bool>> predicate,
            Expression<Func<TEntity, TOrderKey>> orderBy = null,
            Expression<Func<TEntity, TOrderKey>> orderByDescending = null,
            Expression<Func<TEntity, TThenKey>> thenBy = null, 
            Expression<Func<TEntity, TThenKey>> thenByDescending = null, 
            CancellationToken cancellationToken = default(CancellationToken));

        Task<TEntity> FindByIdAsync(TKey primaryKey, CancellationToken cancellationToken = default(CancellationToken));
        Task<TEntity> FirstOrDefaultAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default(CancellationToken));

        Task<bool> DeleteAsync(TEntity entity, CancellationToken cancellationToken = default(CancellationToken));
        Task<bool> DeleteAsync(TKey primaryKey, CancellationToken cancellationToken = default(CancellationToken));

        Task<bool> InsertAsync(TEntity entity, CancellationToken cancellationToken = default(CancellationToken));
        Task<bool> InsertOrReplaceAsync(TEntity entity, CancellationToken cancellationToken = default(CancellationToken));
        Task<bool> UpdateAsync(TEntity entity, CancellationToken cancellationToken = default(CancellationToken));
        Task<bool> InsertAllAsync(IEnumerable<TEntity> entities, CancellationToken cancellationToken = default(CancellationToken));
        Task<bool> UpdateAllAsync(IEnumerable<TEntity> entities, CancellationToken cancellationToken = default(CancellationToken));
    }

    public class DataRepository<TEntity, TKey> : IDataRepository<TEntity, TKey> where TEntity : new()
    {
        readonly Lazy<SQLiteAsyncConnection> _database;

        public DataRepository(Lazy<SQLiteAsyncConnection> database)
        {
            Contract.Requires(database != null);

            _database = database;
        }

        protected SQLiteAsyncConnection Connection => _database.Value;
        protected AsyncTableQuery<TEntity> Query => Connection.Table<TEntity>();

        public Task CreateAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return Connection.CreateTableAsync<TEntity>();
        }

        public Task TruncateAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return Task.Run(() =>
            {
                var conn = Connection.GetConnection();
                using (conn.Lock())
                {
                    conn.DeleteAll<TEntity>();
                }
            }, cancellationToken);
        }

        public Task DropAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return Connection.DropTableAsync<TEntity>();
        }

        public Task<int> CountAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return Query.CountAsync();
        }

        public Task<List<TEntity>> FindAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return Query.ToListAsync();
        }

        public Task<List<TEntity>> FindAsync<TOrderKey, TThenKey>(
            Expression<Func<TEntity, bool>> predicate, 
            Expression<Func<TEntity, TOrderKey>> orderBy = null, 
            Expression<Func<TEntity, TOrderKey>> orderByDescending = null, 
            Expression<Func<TEntity, TThenKey>> thenBy = null, 
            Expression<Func<TEntity, TThenKey>> thenByDescending = null, 
            CancellationToken cancellationToken = default(CancellationToken))
        {
            Contract.Requires(predicate != null);

            cancellationToken.ThrowIfCancellationRequested();

            var query = Query.Where(predicate);
            if (orderBy != null || orderByDescending != null) 
            {
                if (orderBy != null)
                {
                    query = query.OrderBy(orderBy);
                }
                else if (orderByDescending != null)
                {
                    query = query.OrderByDescending(orderByDescending);
                }

                if (thenBy != null)
                {
                    query = query.ThenBy(thenBy);
                }
                else if (thenByDescending != null)
                {
                    query = query.ThenByDescending(thenByDescending);
                }
            }

            return query.ToListAsync();
        }

        public Task<TEntity> FindByIdAsync(TKey primaryKey, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return Connection.FindAsync<TEntity>(primaryKey);
        }

        public Task<TEntity> FirstOrDefaultAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return Query.Where(predicate).FirstOrDefaultAsync();
        }

        public async Task<bool> DeleteAsync(TEntity entity, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return await Connection.DeleteAsync(entity).ConfigureAwait(false) > 0;
        }

        public async Task<bool> DeleteAsync(TKey primaryKey, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            var entity = await Connection.FindAsync<TEntity>(primaryKey).ConfigureAwait(false);
            cancellationToken.ThrowIfCancellationRequested();
            return await Connection.DeleteAsync(entity).ConfigureAwait(false) > 0;
        }

        public async Task<bool> InsertAsync(TEntity entity, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return await Connection.InsertAsync(entity).ConfigureAwait(false) > 0;
        }

        public async Task<bool> InsertOrReplaceAsync(TEntity entity, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return await Connection.InsertOrReplaceAsync(entity).ConfigureAwait(false) > 0;
        }

        public async Task<bool> UpdateAsync(TEntity entity, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return await Connection.UpdateAsync(entity).ConfigureAwait(false) > 0;
        }

        public async Task<bool> InsertAllAsync(IEnumerable<TEntity> entities, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return await Connection.InsertAllAsync(entities).ConfigureAwait(false) > 0;
        }

        public async Task<bool> UpdateAllAsync(IEnumerable<TEntity> entities, CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            return await Connection.UpdateAllAsync(entities).ConfigureAwait(false) > 0;
        }
    }
}