sqlite-net
sqlite-net copied to clipboard
praeclarum
Select
He means Connection.Table<Deez>().Select(x=>x.Nuts).Where(y=> y.Size == "Big"); I think.
In sql Select comes first :)
But yes, having more LINQ integration would be nice.
I have written these extension methods to do a SelectAsync:
/// <summary>Method that returns a List of rows with selected properties</summary>
public static Task<List<TResult>> ToListAsync<T, TResult>(this AsyncTableQuery<T> asyncTableQuery, Func<T, TResult> selec) where T : new()
{
return asyncTableQuery.ExecuteQueryAsync(selec, x => x.ToList());
}
/// <summary>Method that returns a row with selected properties that satisfies a condition</summary>
public static Task<TResult> FirstOrDefaultAsync<T, TResult>(this AsyncTableQuery<T> asyncTableQuery, Expression<Func<T, bool>> pred, Func<T, TResult> selec) where T : new()
{
return asyncTableQuery.Where(pred).Take(1).ExecuteQueryAsync(selec, x => x.FirstOrDefault());
}
/// <summary>Method that returns a row with selected properties</summary>
public static Task<TResult> FirstOrDefaultAsync<T, TResult>(this AsyncTableQuery<T> asyncTableQuery, Func<T, TResult> selec) where T : new()
{
return asyncTableQuery.Take(1).ExecuteQueryAsync(selec, x => x.FirstOrDefault());
}
private static Task<TEnd> ExecuteQueryAsync<T, TResult, TEnd>(this AsyncTableQuery<T> asyncTableQuery, Expression<Func<T, TResult>> selec, Func<IEnumerable<TResult>, TEnd> selec2) where T : new()
{
var type = typeof(TResult);
var _innerQuery = asyncTableQuery.GetPrivateField<TableQuery<T>>("_innerQuery");
string selectionList = null;
bool isPrimitive = type.IsPrimitiveType();
if (isPrimitive) selectionList = selec.GetPropName();
else
{
selectionList = string.Join(", ", type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly).Select(x => x.Name));
}
return _innerQuery.ReadAsync(conn =>
{
var command = (SQLiteCommand)_innerQuery.InvokeMethod("GenerateCommand", selectionList);
return type.IsGenericType || isPrimitive ? selec2(command.ExecuteDeferredQuery<T>().Select(selec.Compile())) : selec2(command.ExecuteDeferredQuery<TResult>());
});
}
private static Task<TResult> ReadAsync<T, TResult>(this TableQuery<T> _innerQuery, Func<SQLiteConnectionWithLock, TResult> read)
{
return Task.Factory.StartNew(()=>
{
var connection = (SQLiteConnectionWithLock)_innerQuery.Connection;
using (connection.Lock())
{
return read(connection);
}
}, CancellationToken.None, TaskCreationOptions.DenyChildAttach, TaskScheduler.Default);
}
public static T GetPrivateField<T>(this object obj, string fieldName)
{
return (T)obj.GetType().GetField(fieldName, BindingFlags.Instance | BindingFlags.NonPublic).GetValue(obj);
}
public static object InvokeMethod<T>(this T obj, string methodName, params object[] args)
{
return typeof(T).GetTypeInfo().GetDeclaredMethod(methodName).Invoke(obj, args);
}
private static bool IsPrimitiveType(this Type type)
{
return type == typeof(object) || Type.GetTypeCode(type) != TypeCode.Object;
}
public static string GetPropName<T,TResult>(this Expression<Func<T, TResult>> exp)
{
var body = exp.Body as MemberExpression;
if (body == null)
{
var ubody = (UnaryExpression)exp.Body;
body = ubody.Operand as MemberExpression;
}
return body.Member.Name;
}
The usage from AsyncTableQuery is:
var result = await connection.Table<Atraq>().Where(x => x.Id > 976).ToListAsync(x => new { x.Id, x.Certificate });
or
var result = await connection.Table<Atraq>().Where(x => x.Id > 976).ToListAsync(x => new Val { Id = x.Id, Certificate = x.Certificate });
If the type you return it's a class, then it's maped to that class, else if it's a generic or primitive, it's mapped to table class and then to that type.
I wish more functionality of Linq integration like this could be added into the library. You can also check my AnyAsync() extension method here: https://github.com/praeclarum/sqlite-net/issues/1197#issue-1977503446
