This is an old question but here is my implementation.
I′m using async connections as they provide better performance in mobile projects. The nuggets I installed are Sqlite.Net-PCL
/SQLite.Net.Async-PCL
on the Core project and the corresponding nuget on the Android project.
My Repository looks like this:
using System;
using System.Collections.Generic;
using Core.Models;
using SQLite.Net;
using System.Linq;
using SQLite.Net.Async;
using System.Threading.Tasks;
using System.Linq.Expressions;
namespace Core.Managers
{
public interface IRepository<T> where T : class, new()
{
Task<List<T>> Get();
Task<T> Get(int id);
Task<List<T>> Get<TValue>(Expression<Func<T, bool>> predicate = null, Expression<Func<T, TValue>> orderBy = null);
Task<T> Get(Expression<Func<T, bool>> predicate);
AsyncTableQuery<T> AsQueryable();
Task<int> Insert(T entity);
Task<int> Update(T entity);
Task<int> Delete(T entity);
}
public class Repository<T> : IRepository<T> where T : class, new()
{
private SQLiteAsyncConnection db;
public Repository(SQLiteAsyncConnection db)
{
this.db = db;
}
public AsyncTableQuery<T> AsQueryable() =>
db.Table<T>();
public async Task<List<T>> Get() =>
await db.Table<T>().ToListAsync();
public async Task<List<T>> Get<TValue>(Expression<Func<T, bool>> predicate = null, Expression<Func<T, TValue>> orderBy = null)
{
var query = db.Table<T>();
if (predicate != null)
query = query.Where(predicate);
if (orderBy != null)
query = query.OrderBy<TValue>(orderBy);
return await query.ToListAsync();
}
public async Task<T> Get(int id) =>
await db.FindAsync<T>(id);
public async Task<T> Get(Expression<Func<T, bool>> predicate) =>
await db.FindAsync<T>(predicate);
public async Task<int> Insert(T entity) =>
await db.InsertAsync(entity);
public async Task<int> Update(T entity) =>
await db.UpdateAsync(entity);
public async Task<int> Delete(T entity) =>
await db.DeleteAsync(entity);
}
}
Some examples on how to use it:
var connection = new SQLiteAsyncConnection(() => sqlite.GetConnectionWithLock());
await connection.CreateTablesAsync<Ingredient, Stock>();
IRepository<Stock> stockRepo = new Repository<Stock>(connection);
IRepository<Ingredient> ingredientRepo = new Repository<Ingredient>(connection);
var stock1 = new Stock {
IngredientId = 1,
DaysToExpire = 3,
EntryDate = DateTime.Now,
Location = StockLocations.Fridge,
MeasureUnit = MeasureUnits.Liter,
Price = 5.50m,
ProductName = "Leche Auchan",
Quantity = 3,
Picture = "test.jpg",
Family = IngredientFamilies.Dairy
};
var stockId = await stockRepo.Insert(stock1);
var all = await stockRepo.Get();
var single = await stockRepo.Get(72);
var search = await stockRepo.Get(x => x.ProductName.StartsWith("something"));
var orderedSearch = await stockRepo.Get(predicate: x => x.DaysToExpire < 4, orderBy: x => x.EntryDate);
If the Repository does not meet your query needs, you can use AsQueryable():
public async Task<List<Stock>> Search(string searchQuery, StockLocations location, IngredientFamilies family)
{
var query = stockRepo.AsQueryable();
if (!string.IsNullOrEmpty(searchQuery))
{
query = query.Where(x => x.ProductName.Contains(searchQuery) || x.Barcode.StartsWith(searchQuery));
}
if (location != StockLocations.All)
{
query = query.Where(x => x.Location == location);
}
if (family != IngredientFamilies.All)
{
query = query.Where(x => x.Family == family);
}
return await query.OrderBy(x => x.ExpirationDays).ToListAsync();
}
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…