SQLite.net SQLiteFunction 在 Linq to SQL 中不起作用

问题描述

我使用 System.Data.SQLite.SQLiteFunction 在 C# 中创建了一些自定义 SQLite 函数.使用 SQLiteDataAdapter 执行查询时效果很好，它不起作用，但是，当使用 Linq to SQL 时，我收到错误消息，指出该函数不存在.

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.

我想最重要的是，我怎样才能让自定义 SQLiteFunctions 在 Linq to SQL 中工作?要么让它们按照预期的方式加载，要么通过修改SQLite.Net 所以它们是 dll 的一部分?

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?

注意:我了解 Entity Framework 是首选，这是遗留应用程序，我无法更改它.我尝试将函数手动绑定到 DataContext.Connection，没有骰子.

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.

尝试修改 System.Data.SQLite 的背景:我尝试下载源代码，我可以成功地从源代码构建，但是源代码对我来说有点令人费解.

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.

• 在System.Data.SQLite.2012项目中，项目中不包含任何文件，但所有源文件都存在于实际文件夹中.它们似乎包含在名为 System.Data.SQLite.Files.targets 的文件中的解决方案中.这对我来说是一个奇怪的设置.
• 我将自定义函数添加到项目文件夹中，但没有像所有其他文件一样将它们包含在项目中.然后我将它们添加到 System.Data.SQLite.Files.targets.
• 我构建了解决方案，它们确实出现在程序集中.虽然我似乎可以将文件添加到程序集并构建，但修改现有代码似乎没有任何影响.
• 我进入 SQLiteConnection 类并在 Open 方法中添加了一个 throw new Exception，我在关键位置添加了 Console.Writeline，我在现有代码中修改的任何内容似乎都没有进入编译程序集.

这样做的目的是尝试将我的自定义函数构建到 System.Data.SQLite.dll 中，而不是依赖于通过反射自动加载.

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.

推荐答案

就在那一刻我发现了这个不错的片段 来自这个问题

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]));
    }
}

但是没有找到如何使用它.现在有一个 SQLiteConnection.BindFunction 方法.很丑所以我做了一个小扩展方法:

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);
}

现在你只需要

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)test'
    // looks for the word 'test', case-insensitive in a string column
}

现在如何在 LINQ to SQL 中执行此操作，我不完全知道，因为我在 LINQ IQueryProvider 上有自己的 SQL.这是使用基本 IDbConnection、IDbCommand、IDbDataParameter 和 IDataReader 接口以及自定义 SQLiteFunction 实现的方法.

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.