SQLite.net SQLiteFunction not working in Linq to SQL

Question

I've created a handful of custom SQLite functions in C# using System.Data.SQLite.SQLiteFunction. It works great when using SQLiteDataAdapter to execute queries, it does not work, however, when using Linq to SQL I get errors stating that the function does not exist.

I guess the bottom line is, how can I get the Custom SQLiteFunctions to work in Linq to SQL? Either by getting them to load the way they are supposed to or by modifying the source code of SQLite.Net so they are part of the dll?

Note: I understand Entity Framework is preferred, this is legacy application and I do not have the option to change this. I tried binding the functions manually to the DataContext.Connection, no dice.

---

Background regarding an attempt to modify System.Data.SQLite: I tried downloading the source code, I can successfully build from source, but the source code is a little puzzling to me.

• In the System.Data.SQLite.2012 project, there are no files included in the project, but all the source files exist in the actual folder. They appear to be included in the solution in a file called System.Data.SQLite.Files.targets. This is a strange setup to me.
• I added my custom functions to the project folder, but did not include them in the project just like all the other files. I then added them to the System.Data.SQLite.Files.targets.
• I built the solution and they do appear in the assembly. Although I can seem to add files to the assembly and build, modifying the existing code seems to have no affect.
• I went into the SQLiteConnection class and added a throw new Exception in the Open method, I've added Console.Writeline in key places, nothing I modify in the existing code seems to make it into the compiled assembly.

The goal of this was to try and build my custom functions into the System.Data.SQLite.dll rather than rely on auto loading through reflection.

Answer 1

Just that moment I found this nice snippet from this question

// from https://stackoverflow.com/questions/172735/create-use-user-defined-functions-in-system-data-sqlite
// taken from http://sqlite.phxsoftware.com/forums/p/348/1457.aspx#1457
[SQLiteFunction(Name = "REGEXP", Arguments = 2, FuncType = FunctionType.Scalar)]
public class RegExSQLiteFunction : SQLiteFunction {
    public override object Invoke(object[] args) {
        return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args[1]), Convert.ToString(args[0]));
    }
}

But didn't find how to use it. Now there's a SQLiteConnection.BindFunction method. It's ugly so I made a little extension method:

public static void BindFunction(this SQLiteConnection connection, SQLiteFunction function) 
{
    var attributes = function.GetType().GetCustomAttributes(typeof(SQLiteFunctionAttribute), true).Cast<SQLiteFunctionAttribute>().ToArray();
    if (attributes.Length == 0) {
        throw new InvalidOperationException("SQLiteFunction doesn't have SQLiteFunctionAttribute");
    }
    connection.BindFunction(attributes[0], function);
}

And now you just have to

using (var connection = new SQLiteConnection( "Data Source=YourDB.sqlite" )) 
{
    connection.Open(); // Connection must be open to bind a function

    connection.BindFunction(new RegExSQLiteFunction());

    // Here create a command, and try REGEXP, for example
    // SELECT * FROM "table" WHERE "column" REGEXP '(?i)\btest\b'
    // looks for the word 'test', case-insensitive in a string column
}

Now how you can do it in LINQ to SQL, I don't exactly know because I've got my own SQL on LINQ IQueryProvider. This is how you can do it with the basic IDbConnection, IDbCommand, IDbDataParameter and IDataReader interfaces and your custom SQLiteFunction.