User defined functions support

[bairog]

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]

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]

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

[Shalex]

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

[bairog]

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

[Shalex]

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]

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]

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.

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]

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]

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]

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)?

[Shalex]

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.

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.

Is CreateDefaultTypeUsage correct in my case (because using CreateDecimalTypeUsage results in exception)?

Yes, it is.

[bairog]

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]

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]

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