SQLite.net SQLiteFunction不工作的LINQ到SQL [英] SQLite.net SQLiteFunction not working in Linq to SQL

问题描述

我创建使用System.Data.SQLite.SQLiteFunction在C＃中的自定义功能的SQLite了一把。它的伟大工程使用SQLiteDataAdapter执行查询的时候，这是行不通的，但是，使用LINQ to SQL时我得到的错误，说明该函数不存在。我明白了实体框架是首选，这是传统的应用程序，我没有更改此选项。我试图手动绑定功能到DataContext.Connection，没有骰子。

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 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.2012项目中，没有包括在项目中的文件，但在实际文件夹中的所有源文件。它们似乎被包含在称为System.Data.SQLite.Files.targets在一个文件中的溶液中。这是一个奇怪的设置给我。我将自己的自定义功能，以项目文件夹，但不包括他们在项目就像所有其他文件。然后，我将它们添加到System.Data.SQLite.Files.targets。我建立了解决方案，但会出现在大会。虽然我似乎可以将文件添加到大会和建立，修改现有的代码似乎没有任何影响。我走进SQLiteConnection类，并在Open方法增加了抛出新的异常，我在关键的地方加Console.Writeline，没有我在现有的代码修改，似乎使其进入编译的程序集。这样做的目的是试图建立我的自定义函数到System.Data.SQLite.dll而不是通过反射依靠自动加载。

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.

我猜的底线是的，我怎么能得到自定义SQLiteFunctions在LINQ中，无论是让他们，使他们的DLL部分加载他们应该或修改SQLite.Net的源代码的方式工作，以SQL。

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.

推荐答案

只是那一刻，我发现这个漂亮的片段的

Just that moment I found this nice snippet from this question

// from http://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);
}

而现在你只需要

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
}

您现在可以如何做到这一点的LINQ到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.

这篇关于SQLite.net SQLiteFunction不工作的LINQ到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！