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
COLLATE support in column definitions
Re: COLLATE support in column definitions
Ok, I've finally found the COLLATE support via
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.
introduced with version 5.3 as described in the history.SQLiteEntityProviderConfig.Instance.DatabaseScript.Column.StringColumnCollation
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.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: COLLATE support in column definitions
We will investigate the possibility to support such a feature in Entity Framework and post here about the results as soon as possible.
Re: COLLATE support in column definitions
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:
Please check the DDL script of the generated table to make sure that collation is applied.
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; }
}
}