Devart Forums

Code: Select all

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

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

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

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>

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

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; 

Code: Select all

var FilteredDepts = DAL.GetDepts().Where(i => i.Loc.StartsWith("USA")).ToList();

Code: Select all

try
{ connection.RegisterFunction(function);}
catch 
{ //function already exists}

Code: Select all

private EdmType GetInt32PrimitiveType(DbModel model) {

            var typeUsage = TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32));
            return model.ProviderManifest.GetStoreType(typeUsage).EdmType;
        }