SQLite-net, custom functions, and collections

It is not possible to define a custom function in SQLite-net that is able to use collections (sqlite does not work with managed code).

The code below presents an alternative that, although a hack, works.

	using SQLitePCL;
	public class Database
	{
	protected static SQLiteConnection _db;
	public BaseDb()
	{
	if( _db == null )
	{
	_db = new SQLiteConnection( [DatabasePath] );
	}
	}
	}
	public class Phrase
	{
	public int Id{ get; set; }
	public string Text{ get; set; }
	}
	public class TablePhrase : Database
	{
	private HashSet<int> _ids;
	public TablePhrase()
	{
	SQLitePCL.raw.sqlite3_create_function( _db.Handle, "IN_SET", 3, null, InSet );
	}
	private void InSet( sqlite3_context ctx, object user_data, sqlite3_value[] args )
	{
	var id = raw.sqlite3_value_int( args[ 0 ] );
	var text = raw.sqlite3_value_text( args[ 1 ] ).utf8_to_string();
	var pattern = raw.sqlite3_value_text( args[ 2 ] ).utf8_to_string();
	var result = 0;
	if( _ids.Contains( id ) == true
	&& Regex.IsMatch( text, pattern ) == true )
	{
	result = 1;
	}
	SQLitePCL.raw.sqlite3_result_int( ctx, result );
	}
	public List<Phrase> InSet( HashSet<int> ids, string pattern )
	{
	_ids = ids;
	var query = $"SELECT * FROM Phrase WHERE IN_SET( id, text, {pattern} )";
	var result = _db.Query<Phrase>( query );
	_ids = null;
	return result;
	}
	}

view raw a.cs hosted with ❤ by GitHub