COLLATE support in column definitions

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
talbrecht
Posts: 10
Joined: Wed 26 Oct 2016 14:06

COLLATE support in column definitions

Post by talbrecht » Wed 09 Nov 2016 14:29

Hi,

is there any chance that dotConnect for SQLite will support COLLATE statements within column definitions? E.g. SQLite.CodeFirst supports this with its CollateAttribute.

According to Devart.Data.SQLite.SQLiteCollationFunction dotConnect already allows the definition of custom collation functions and their usage within SQL queries. But how can I apply them in conjunction with LINQ-to-Entities?

In a test project the custom collation function was called back by SQLite if I had created the table manually. Can I configure an entity in such a way that the SQL generated by dotConnect during database initialization/migration maps to column definitions with "COLLATE {collation-name}" as described by SQLite here?

Thanks in advance,
Thomas

talbrecht
Posts: 10
Joined: Wed 26 Oct 2016 14:06

Re: COLLATE support in column definitions

Post by talbrecht » Wed 09 Nov 2016 15:23

Ok, I've finally found the COLLATE support via
SQLiteEntityProviderConfig.Instance.DatabaseScript.Column.StringColumnCollation
introduced with version 5.3 as described in the history. ;)

Unfortunately the support is restricted by enum type to the predefined ones described here. String property and predefined string constants would have been the better solution in this case allowing custom collation names, too. :?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: COLLATE support in column definitions

Post by Pinturiccio » Fri 11 Nov 2016 15:33

We will investigate the possibility to support such a feature in Entity Framework and post here about the results as soon as possible.

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

Re: COLLATE support in column definitions

Post by Shalex » Fri 09 Dec 2016 19:08

The new config.DatabaseScript.Column.StringColumnCustomCollationName option is implemented for setting user-defined function for comparing string columns: viewtopic.php?f=29&t=34689.

The use case:

Code: Select all

using Devart.Data.SQLite;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {
            var monitor = new Devart.Data.SQLite.SQLiteMonitor() { IsActive = true };

            var config = Devart.Data.SQLite.Entity.Configuration.SQLiteEntityProviderConfig.Instance;
            config.DatabaseScript.Column.StringColumnCustomCollationName = "my_collation_function";

            var context = new MySQLiteContext();
            context.Database.Delete();
            context.Database.Create();

            var record = new T1 { Id = 1, Data = "a" };
            context.T1s.Add(record);
            context.SaveChanges();

            var result = context.T1s.Where(x => x.Data == "A").ToList();
        }
    }

    public class MyCollationFunction : SQLiteCollationFunction {

        public MyCollationFunction()
          : base("my_collation_function") {
        }

        public static MyCollationFunction Instance = new MyCollationFunction();

        protected override int Compare(string param1, string param2, SQLiteConnection connection) {

            return String.Compare(param1, param2, StringComparison.CurrentCultureIgnoreCase);
        }
    }

    public class T1 {

        public int Id { get; set; }
        public string Data { get; set; }
    }

    public class MySQLiteContext : DbContext {

        public MySQLiteContext()
          : base(CreateConneciton(), contextOwnsConnection: true) {
        }

        private static SQLiteConnection CreateConneciton() {

            var connection = new SQLiteConnection(@"Data Source=D:\1.db;FailIfMissing=false;");
            connection.StateChange += Connection_StateChange;
            return connection;
        }

        private static void Connection_StateChange(object sender, System.Data.StateChangeEventArgs e) {

            if (e.CurrentState == System.Data.ConnectionState.Open) {
                var connection = (SQLiteConnection)sender;
                connection.RegisterFunction(MyCollationFunction.Instance);
            }
        }
        public DbSet<T1> T1s { get; set; }
    }
}
Please check the DDL script of the generated table to make sure that collation is applied.

Post Reply