User defined functions support

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
bairog
Posts: 111
Joined: Mon 29 Apr 2013 09:05

User defined functions support

Post by bairog » Thu 23 Jan 2014 07:20

Hello.
Is there any way to create custom function/stored procedure with dotConnect for SQLite?

P.S. I use dotConnect for SQLite 5.1.80 + Entity Framework 6.0.1.

Shalex
Site Admin
Posts: 8971
Joined: Thu 14 Aug 2008 12:44

Re: User defined functions support

Post by Shalex » Thu 23 Jan 2014 14:16

You can use custom functions either at ADO.NET level (http://blog.devart.com/sqlite-functions.html) or at Entity Framework level if XML mapping is used (http://blog.devart.com/model-defined-fu ... loper.html).

bairog
Posts: 111
Joined: Mon 29 Apr 2013 09:05

Re: User defined functions support

Post by bairog » Thu 23 Jan 2014 16:23

Well, in case I'm using Entity Framework Code-First, custom functions are not available for me, right?

Shalex
Site Admin
Posts: 8971
Joined: Thu 14 Aug 2008 12:44

Re: User defined functions support

Post by Shalex » Fri 24 Jan 2014 13:55

As a solution, create ADO.NET injections in your code which employ custom functions of ADO.NET level.

bairog
Posts: 111
Joined: Mon 29 Apr 2013 09:05

Re: User defined functions support

Post by bairog » Fri 24 Jan 2014 15:50

Sorry, I can't understand what exactly do you mean :)

Shalex
Site Admin
Posts: 8971
Joined: Thu 14 Aug 2008 12:44

Re: User defined functions support

Post by Shalex » Tue 28 Jan 2014 10:58

If you want to employ a plain ADO.NET code with the connection of the DbContext object, try this code:

Code: Select all

var connection = myDbContext.Database.Connection as SQLiteConnection;
connection.Open();
// ...
connection.Close();

bairog
Posts: 111
Joined: Mon 29 Apr 2013 09:05

Re: User defined functions support

Post by bairog » Thu 03 Dec 2015 07:53

I've defined a simple function

Code: Select all

public class ToLowerFunction<string>: SQLiteScalarFunction<string, string> {

    public ToLowerFunction(): base("ToLower") {
    }

    protected override double Execute(string arg, SQLiteConnection connection) {
      return arg.ToLower();
    }
  }
and used it like you adviced me

Code: Select all

var connection = myDbContext.Database.Connection as SQLiteConnection;
connection.Open();
ToLowerFunction<string> function = new ToLowerFunction<string>();
connection.RegisterFunction(function);

SQLiteCommand command = new SQLiteCommand("SELECT ToLower(Name) FROM Users", connection);
SQLiteDataReader reader = command.ExecuteReader();

List<string> userNames = new List<string>();
while (reader.Read())
     userNames.Add(reader[0].ToString());

reader.Close();
connection.UnRegisterFunction(function);
connection.Close();
1) Is that correct usage of user defined functions (ADO.NET level)?

2) Here you adviced me to use myDbContext.Database.Connection while in two other topics you adviced me to use ((IObjectContextAdapter)myDbContext).ObjectContext.Connection.
What's the difference between that two connections?
In what situations I have to use the first instead of the second and vice versa?

Shalex
Site Admin
Posts: 8971
Joined: Thu 14 Aug 2008 12:44

Re: User defined functions support

Post by Shalex » Fri 04 Dec 2015 12:04

bairog wrote:1) Is that correct usage of user defined functions (ADO.NET level)?
Your code looks to be correct. Our samples are available at http://blog.devart.com/sqlite-functions.html.
bairog wrote:2) Here you adviced me to use myDbContext.Database.Connection while in two other topics you adviced me to use ((IObjectContextAdapter)myDbContext).ObjectContext.Connection.
What's the difference between that two connections?
In what situations I have to use the first instead of the second and vice versa?
It is better to use ((IObjectContextAdapter)myDbContext).ObjectContext.Connection to control the lifetime of the connection. Refer to Diego Vega's considerations: http://blogs.msdn.com/b/diego/archive/2 ... ction.aspx.

bairog
Posts: 111
Joined: Mon 29 Apr 2013 09:05

Re: User defined functions support

Post by bairog » Wed 09 Dec 2015 18:03

One more question.
When I use EF Code-First in all my functions I return IQueryable<> (with help of Linq to Entities).
That is very flexible approach because data is actually retrieved from database (i.e. SQL query is called) when client wants (i.e. calls foreach or .ToList()).
Moreover client can apply filtering/sorting/etc. before retrieving data.

But in approach that you advised me for user defined functions SQL query is called in my code. Is there a way to return IQueryable<> to met my previous experience?

P.S. Returning List<>.AsQueryable() in my code (see my sample above) instead of List<> is not an option because SQL query is called in my code while filling a list.

Shalex
Site Admin
Posts: 8971
Joined: Thu 14 Aug 2008 12:44

Re: User defined functions support

Post by Shalex » Thu 17 Dec 2015 13:37

Here is a sample:

App.config

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <entityFramework>
    <providers>
      <provider invariantName="Devart.Data.SQLite" type="Devart.Data.SQLite.Entity.SQLiteEntityProviderServices, Devart.Data.SQLite.Entity, Version=5.3.543.6, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="Devart.Data.SQLite" />
      <add name="dotConnect for SQLite" invariant="Devart.Data.SQLite" description="Devart dotConnect for SQLite" type="Devart.Data.SQLite.SQLiteProviderFactory, Devart.Data.SQLite, Version=5.3.543.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="MyDbContext" connectionString="Data Source=..\..\Database.db;" providerName="Devart.Data.SQLite"/>
  </connectionStrings>
</configuration>
Program.cs

Code: Select all

using Devart.Data.SQLite;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication {
    class Program {
        static void Main(string[] args) {

            var monitor = new SQLiteMonitor() { IsActive = true };

            using (var myDbContext = new MyDbContext()) {
                var connection = myDbContext.Database.Connection as SQLiteConnection;
                connection.Open();
                var function = new ToLowerFunction();
                connection.RegisterFunction(function);

                //// ======== ADO.NET ==========
                //SQLiteCommand command = new SQLiteCommand("SELECT ToLower(Dname) FROM Dept", connection);
                //SQLiteDataReader reader = command.ExecuteReader();
                //List<string> dnames = new List<string>();
                //while (reader.Read())
                //    dnames.Add(reader[0].ToString());
                //reader.Close();

                // ======== EF ===========
                var items = myDbContext.Depts.Where(i => MyDbContext.ToLower(i.Dname) == "accounting");
                items.ToList();

                connection.UnRegisterFunction(function);
                connection.Close();
            }

        }
    }
 
    public class ToLowerFunction: SQLiteScalarFunction<string, string> {

        public ToLowerFunction()
            : base("ToLower") {
        }
        protected override string Execute(string arg, SQLiteConnection connection) {
            return arg.ToLower();
        }
    }
    
    public class MyDbContext : DbContext {
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder) {

            modelBuilder.Conventions.Add(new ToLowerFunctionConvention());
        }

        public DbSet<Dept> Depts { get; set; }

         [DbFunction("CodeFirstDatabaseSchema", "tolower")]
        public static string ToLower(string input) {

            throw new NotImplementedException();
        }
    }

    [System.ComponentModel.DataAnnotations.Schema.Table("Dept")]
    public class Dept {
        [System.ComponentModel.DataAnnotations.Key]
        public int Deptno { get; set; }
        public string Dname { get; set; }
        public string Loc { get; set; }
    }
    
    public class ToLowerFunctionConvention : IStoreModelConvention<EdmModel> {

        public void Apply(EdmModel item, DbModel model) {

            var inputParameter = FunctionParameter.Create("input", this.GetStringPrimitiveType(model), ParameterMode.In);

            var returnValue = FunctionParameter.Create("result", this.GetStringPrimitiveType(model), ParameterMode.ReturnValue);

            var function = this.CreateAndAddFunction(item, "tolower", new[] { inputParameter }, new[] { returnValue });
        }
        private EdmType GetStringPrimitiveType(DbModel model) {

            var typeUsage = TypeUsage.CreateStringTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String), true, false);
            return model.ProviderManifest.GetStoreType(typeUsage).EdmType;
        }
        private EdmFunction CreateAndAddFunction(EdmModel item, string name, IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues) {
            EdmFunctionPayload payload = new EdmFunctionPayload {
                StoreFunctionName = name,
                Parameters = parameters,
                ReturnParameters = returnValues,
                IsBuiltIn = true
            };
            EdmFunction function = EdmFunction.Create(name, "CodeFirstDatabaseSchema", item.DataSpace, payload, null);
            item.AddItem(function);
            return function;
        }
    }
}

bairog
Posts: 111
Joined: Mon 29 Apr 2013 09:05

Re: User defined functions support

Post by bairog » Mon 21 Dec 2015 06:11

Thx.
I've tested this approach in our software:

1) It terms of our software the following code in your example

Code: Select all

            using (var myDbContext = new MyDbContext()) {
                var connection = myDbContext.Database.Connection as SQLiteConnection;
                connection.Open();
                var function = new ToLowerFunction();
                connection.RegisterFunction(function);

                // ======== EF ===========
                var items = myDbContext.Depts.Where(i => MyDbContext.ToLower(i.Dname) == "accounting");
                //items.ToList();

                connection.UnRegisterFunction(function);
                connection.Close();

                return items; 
is located in DAL.dll inside a IQueryable<Dept> GetDepts() function (I've commented items.ToList() line in your code because all our functions return IQueryable<>).
Our client application will use that function as follows (apply it's own filter/sort and then enemerate objects)

Code: Select all

var FilteredDepts = DAL.GetDepts().Where(i => i.Loc.StartsWith("USA")).ToList();
And it will fail because ToLowerFunction is already unregistered ("SQLite error - no such function: tolower").
It will not fail if I don't perform unregistration.
Is that ok to use you code such way (I expect GC to collect unused objects in future)?

2) Is there a way to ensure custom function with given name is not already registered for a connection?
I mean other way than using

Code: Select all

try
{ connection.RegisterFunction(function);}
catch 
{ //function already exists}
3) I'm trying to implement StrLen custom function and I need Int32 input parameter. I use:

Code: Select all

private EdmType GetInt32PrimitiveType(DbModel model) {

            var typeUsage = TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32));
            return model.ProviderManifest.GetStoreType(typeUsage).EdmType;
        }
Is CreateDefaultTypeUsage correct in my case (because using CreateDecimalTypeUsage results in exception)?
Last edited by bairog on Tue 22 Dec 2015 12:57, edited 1 time in total.

Shalex
Site Admin
Posts: 8971
Joined: Thu 14 Aug 2008 12:44

Re: User defined functions support

Post by Shalex » Tue 22 Dec 2015 10:55

bairog wrote:It will not fail if I don't perform unregistration.
Is that ok to use you code such way (I expect GC to collect unused objects in future)?
That is OK.
bairog wrote:Is there a way to ensure custom function with given name is not already registered for a connection?
I mean other way than using

Code: Select all

try
{ connection.RegisterFunction(function);}
catch 
{ //function already exists}
At the moment dotConnect for SQLite doesn't provide an interface for getting registered functions for particular session.
Remarks:
1. There are no APIs for getting system functions in SQLite engine.
2. If you need, please confirm request for implementing interface to get a list of user defined functions (your own AND the ones predefined by provider) in dotConnect for SQLite.
bairog wrote:Is CreateDefaultTypeUsage correct in my case (because using CreateDecimalTypeUsage results in exception)?
Yes, it is.

bairog
Posts: 111
Joined: Mon 29 Apr 2013 09:05

Re: User defined functions support

Post by bairog » Tue 22 Dec 2015 11:15

Shalex wrote:2. If you need, please confirm request for implementing interface to get a list of user defined functions (your own AND the ones predefined by provider) in dotConnect for SQLite.
I confirm that request. It will be perfect if I can determine is specific UDF predefined by provider or added manually.

Shalex
Site Admin
Posts: 8971
Joined: Thu 14 Aug 2008 12:44

Re: User defined functions support

Post by Shalex » Tue 22 Dec 2015 11:47

bairog wrote:I confirm that request. It will be perfect if I can determine is specific UDF predefined by provider or added manually.
We will notify you when the corresponding functionality is implemented.

Shalex
Site Admin
Posts: 8971
Joined: Thu 14 Aug 2008 12:44

Re: User defined functions support

Post by Shalex » Thu 21 Jan 2016 14:44

The newest (5.3.583) build of dotConnect for SQLite includes the following functionality:
  • The SQLiteConnecton.IsFunctionRegistered() method is added to check if some user-defined function has already been registered for a current session
  • The new metadata collection is added: SQLiteConnection.GetSchema("UserDefinedFunctions") returns information about all user-defined functions registered for a current session

Post Reply