How to Call SQLite User Defined Function with C# LINQ Query

Question

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

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/

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

Calling DB Function with Entity Framework 6

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>

I add this code

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

If I try this code

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

I get this error

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.

If I try this code

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

I get this error

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

What can I do from here?

Answer 1

Woohoo! I got it working.

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.

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

And then call it like this

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.

Good luck!