如何使用C#LINQ查询调用SQLite用户定义函数 [英] How to Call SQLite User Defined Function with C# LINQ Query

问题描述

使用SQLite和C#，有人尝试过在LINQ查询中调用UDF吗?

With SQLite and C#, has anyone tried calling a UDF within a LINQ query?

在线搜索时，我发现这与在C#中创建UDF函数有关

Searching online, I found this about creating a UDF function in C#

http: //www.ivankristianto.com/howto-make-user-defined-function-in-sqlite-ado-net-with-csharp/

至于在LINQ to Entities中调用函数，我在这里有解决方法

As for calling a function in LINQ to Entities, I have the solution here

使用实体框架6调用DB函数

这是我到目前为止所得到的.我创建数据库模型并使用linq到SQLite.

Here's what I got so far. I create my database model and linq to SQLite.

我将其添加到数据库模型文件中:

I add this into the database model file:

<Function Name="fn_CalculateSomething" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" ReturnType="real">
  <Parameter Name="height" Type="real" Mode="In" />
  <Parameter Name="depth" Type="real" Mode="In" />
</Function>

我添加此代码

public partial class MyModelTable {
    [DbFunction("MyModel.Store", "fn_CalculateSomething")]
    public float? DbGetValue(float height, float depth, float ratio) {
        List<ObjectParameter> parameters = new List<ObjectParameter>(3);
        parameters.Add(new ObjectParameter("height", height));
        parameters.Add(new ObjectParameter("depth", depth));
        var lObjectContext = ((IObjectContextAdapter)this).ObjectContext;
        var output = lObjectContext.
             CreateQuery<float>("MyModel.Store.fn_CalculateSomething(@height, @depth)", parameters.ToArray())
            .Execute(MergeOption.NoTracking)
            .FirstOrDefault();
        return output;
    }
}

[SQLiteFunction(Name = "fn_CalculateSomething", Arguments = 2, FuncType = FunctionType.Scalar)]
public class fn_CalculateSomething : SQLiteFunction {
    public override object Invoke(object[] args) {
        float Height = (float)args[0];
        float Depth = (float)args[1];
        return Height * Depth;
    }
}

如果我尝试此代码

listBox1.DataSource = (from v in context.MyModelTable
                        select v.fn_CalculateSomething(5, 5)).ToList();

我收到此错误

The specified method 'System.Nullable`1[System.Single] fn_CalculateSomething(Single, Single)' on the type 'SQLiteTest.MyModelTable' cannot be translated into a LINQ to Entities store expression because its return type does not match the return type of the function specified by its DbFunction attribute.

如果我尝试此代码

context.MyModelTable.First().fn_CalculateSomething(5, 5);

我收到此错误

Unable to cast object of type 'System.Data.Entity.DynamicProxies.MyModelTable_935D452DE6F9E3375A6D180DF5A662168FD2DE164BA93C588D9CDCA1909630EB' to type 'System.Data.Entity.Infrastructure.IObjectContextAdapter'.

我可以从这里做什么?

推荐答案

糟糕！我知道了.

首先，在构建查询时不会调用DbGetValue函数，而只会在代码中直接调用它.因此，其中的代码无关紧要，并且当它被调用时，您可以直接计算值而无需调用数据库.

First, the DbGetValue function doesn't get called when building a query, only when called directly in the code. So the code within it is irrelevant, and when it does get called, you can calculate the value directly without calling the database.

第二，即使该函数在模型中定义为实数"，但如果将返回值更改为double而不是float，它将起作用.不知道为什么.

Second, even though the function is defined as "real" in the model, it will work if you change the return value to double instead of float. No idea why.

第三，您必须将函数绑定到这样的连接上

Third, you must bind the function to the connection with something like this

public static class ExtensionMethods {
    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 (NaturalGroundingVideosEntities context = new NaturalGroundingVideosEntities()) {
    SQLiteConnection Conn = (SQLiteConnection)context.Database.Connection;
    Conn.Open();
    Conn.BindFunction(new fn_CalculateSomething());

    listBox1.DataSource = (from v in context.RatingCategories
                                   select v.DbGetValue(5, 5, 5)).ToList();
}

这将起作用！然后，您可能想在更方便的位置进行函数绑定，但是您了解了.

This will work! Then, you might want to do the function binding at a location that is more convenient, but you get the idea.

祝你好运！

这篇关于如何使用C#LINQ查询调用SQLite用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！