[Question] Check if any row exists - AnyAsync

[joseluisct]

Hello, I have not found an ORM direct way of checking if any row exists in a table such as AnyAsync() of Linq/Entity Framework.

Before I was using FirstOrDefaultAsync() != null or CountAsync() > 0, but I found that with Sqlite there is an efficient way of querying it as: $"SELECT EXISTS(SELECT 1 FROM {table} WHERE {condition} LIMIT 1)"

If would be nice to have an AnyAsync() method inside the library to use as the other ones. For now I have created an extension method to use on my projects doing so, I write it here in case it can be useful for someone:

/// <summary>Method that returns true if any register satisfies the condition</summary>
public static Task<bool> AnyAsync<T>(this AsyncTableQuery<T> asyncTableQuery, Expression<Func<T, bool>> expr)  
   where T : new()
{
   var list = new List<object>();
   var _innerQuery = asyncTableQuery.GetPrivateField<TableQuery<T>>("_innerQuery");
   var compileResult = _innerQuery.InvokeMethod("CompileExpr", expr.Body, list);
   string commandText = compileResult.GetProperty<string>("CommandText");     
   string sql = $"SELECT EXISTS(SELECT 1 FROM {typeof(T).Name} WHERE {commandText} LIMIT 1)";

    return _innerQuery.ReadAsync(conn =>
    {
        return conn.CreateCommand(sql, list.ToArray()).ExecuteScalar<bool>();
    });    
}

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 object InvokeMethod<T>(this T obj, string methodName, params object[] args)
{
    return typeof(T).GetTypeInfo().GetDeclaredMethod(methodName).Invoke(obj, args);
}

public static T GetPrivateField<T>(this object obj, string fieldName)
{
    return (T)obj.GetType().GetField(fieldName, BindingFlags.Instance | BindingFlags.NonPublic).GetValue(obj);
}

public static T GetProperty<T>(this object obj, string propertyName)
{
    return (T)obj.GetType().GetProperty(propertyName, BindingFlags.Instance | BindingFlags.Public).GetValue(obj);
}

To use the extension method from AsyncTableQuery: bool exist = await connection.Table<Agent>().AnyAsync(x => x.Name == name);

I wish more functionality of Linq integration like this could be added into the library. You can also check my other extension methods here: https://github.com/praeclarum/sqlite-net/issues/1180#issuecomment-1797078233

[mjo151]

I think the non-async version could be added to TableQuery pretty easily:

public bool Any()
{
    var selectCommand = Take(1).GenerateCommand("1");
    selectCommand.CommandText = $"select exists({selectCommand.CommandText})";
    return selectCommand.ExecuteScalar<bool>();
}

public bool Any(Expression<Func<T, bool>> predExpr)
{
    return Where(predExpr).Any();
}