编写一个在 EF Core 中不区分大小写的搜索查询? [英] Write a search query which is case insensitive in EF Core?

问题描述

我想问一个关于 SQL Server 和 EF Core 的问题.数据库中的排序规则是 Latin1_CI_AS，我想编写一个包含土耳其语字符的搜索查询.

在数据库中，'personnel' 表中有一个名为SELİM"的记录.当我在 EF Core 中编写这样的查询时:

 公共异步任务>获取(PersonnelGetPayload 有效载荷){if (payload.Name != null)查询 = query.Where(x => x.Name.Contains(payload.Name));}

如果我的搜索条件是selim"，则列表为空.

我没有机会将数据库中的排序规则更改为土耳其语，因为我们的应用程序是多语言的.我认为其他语言会有问题.还是我错了?

我还写了字符串扩展名.但是，当将 LINQ 查询转换为 SQL 时，所有记录都会进入服务层，因为 LIKE 运算符没有分配 WHERE 子句.在sql端运行这个条件非常重要.如果我把所有的数据集都带到服务层去查询，那会花费我很多.

当我在数据库中键入查询时，我可以解决这个问题:

SELECT * FROM Personnel WHERE Name LIKE 'selim' COLLATE Turkey_CI_AS

我想如果我可以在 EF Core 上操作整理，我会解决这个问题.

解决方案

我已经测试了 like 函数，但结果并不像 op 所说的那样正确.所以只剩下一种选择了.即创建拦截器并实现自定义逻辑.我创建了一个如下所示的示例:

 公共类后缀{public const string Collat​​e = "--Collat​​e";}公共类 Collat​​ionDbCommandInterceptor : DbCommandInterceptor{private const string Collat​​eSyntax = " collat​​e turkish_ci_as";公共覆盖 InterceptionResultReaderExecuting(DbCommand 命令, CommandEventData eventData, InterceptionResult result){var args = command.Parameters.OfType().Where(t => t.DbType == DbType.String && t.Value.ToString().EndsWith(Suffixes.Collat​​e)).ToList();如果(args.Count <= 0)返回 base.ReaderExecuting(command, eventData, result);foreach(args 中的 var 参数){parameter.Value = parameter.Value.ToString().Replace(Suffixes.Collat​​e, "");var equal = $"= {parameter.ParameterName}";var ixs = AllIndexesOf(command.CommandText, 相等);#pragma warning disable CA2100//检查 SQL 查询是否存在安全漏洞foreach(ixs 中的 var eq){command.CommandText = command.CommandText.Insert(eq+equality.Length,Collat​​eSyntax);}#pragma warning restore CA2100//检查 SQL 查询是否存在安全漏洞}返回 base.ReaderExecuting(command, eventData, result);}私有静态 IEnumerableAllIndexesOf(string str, string value){if (string.IsNullOrEmpty(value))throw new ArgumentException("要查找的字符串不能为空", nameof(value));var index = new List();for (var index = 0; ; index += value.Length){index = str.IndexOf(value, index);如果(索引== -1)返回索引；索引.插入(0，索引)；}}}

配置:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){如果(！optionsBuilder.IsConfigured){....optionsBuilder.AddInterceptors(new Collat​​ionDbCommandInterceptor());....}}

用法:

var kadayif = $"kadayıf{Suffixes.Collat​​e}";var william = $"Wİlliam{Suffixes.Collat​​e}";var auths = ctx.Authors.Where(t => t.FirstName == william ||t.LastName == kadayif).ToList();//返回 William Shakespeare 和 Abuzer Kadayıf

逻辑是创建一个拦截器，在查询中传递的sql参数中寻找特定的后缀.将查询特定的排序规则注入到最终的 sql 命令文本中.我试图涵盖一些高级场景，如参数重用.它可能需要更多改进.

请注意，此示例适用于 Entity Framework Core 3.0，这是拦截器引入的版本.在早期的 ef 核心版本中拦截是一个小技巧.您可以参考此链接了解更多信息.>

I want to ask a question about SQL Server and EF Core. Collation in the database is Latin1_CI_AS and I want to write a search query that contains Turkish characters.

In the database, there is a record named "SELİM" in the 'personnel' table. When I write a query like this in EF Core:

    public async Task<IList<PersonnelGetDto>> Get(PersonnelGetPayload payload)
        {
           if (payload.Name != null)
                query = query.Where(x => x.Name.Contains(payload.Name)); 
        }

The list is empty if my search condition is "selim".

I don't have a chance to change the collation in the database to Turkish because our application is multilingual. I think there will be a problem with other languages. Or am I wrong?

I also wrote the string extension. However, when converting a LINQ query to SQL, all records come to the service layer because the LIKE operator does not assign the WHERE clause. It's very important to run this condition on the sql side. If I take all the dataset to the service layer and query it, it will cost me a lot.

I can solve the problem when I type a query in the database like this:

SELECT * FROM Personnel WHERE Name LIKE 'selim' COLLATE Turkish_CI_AS

I think if I can manipulate collate on EF Core I will solve the problem.

解决方案

I have tested the like function but it is not resulting correct as op stated. So only one option left remains. Which is to create an interceptor and implement custom logic. I have created a sample like below :

   public class Suffixes
    {
        public const string Collate = "--Collate";
    }

    public class CollationDbCommandInterceptor : DbCommandInterceptor
    {
        private const string CollateSyntax = " collate turkish_ci_as";

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            var args = command.Parameters.OfType<DbParameter>()
                           .Where(t => t.DbType == DbType.String && t.Value.ToString().EndsWith(Suffixes.Collate)).ToList();
            if (args.Count <= 0)
                return base.ReaderExecuting(command, eventData, result);

            foreach (var parameter in args)
            {
                parameter.Value = parameter.Value.ToString().Replace(Suffixes.Collate, "");
                var equality = $"= {parameter.ParameterName}";

                var ixs = AllIndexesOf(command.CommandText, equality);

#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
                foreach (var eq in ixs)
                {
                    command.CommandText = command.CommandText.Insert(eq+equality.Length,CollateSyntax);

                }
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities

            }



            return base.ReaderExecuting(command, eventData, result);
        }

        private static IEnumerable<int> AllIndexesOf(string str, string value)
        {
            if (string.IsNullOrEmpty(value))
                throw new ArgumentException("the string to find may not be empty", nameof(value));
            var indexes = new List<int>();
            for (var index = 0; ; index += value.Length)
            {
                index = str.IndexOf(value, index);
                if (index == -1)
                    return indexes;
                indexes.Insert(0,index);
            }
        }
    }

Configuration :

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
               ....  
                optionsBuilder.AddInterceptors(new CollationDbCommandInterceptor());
                ....
            }
        }

Usage :

var kadayif = $"kadayıf{Suffixes.Collate}";
var william = $"Wİlliam{Suffixes.Collate}";            
var auths = ctx.Authors.Where(t =>   t.FirstName == william ||t.LastName == kadayif).ToList(); 
// returns William Shakespeare and Abuzer Kadayıf

The logic is to create an interceptor that seeks a specific suffix in sql parameters passed in the query. Injects query specific collation in to the final sql command text. I tried to cover some advanced scenarios like parameter reuse. It may require more improvements.

Please note that this example is for Entity Framework Core 3.0 which is the version that interceptors introduced. Interception in earlier ef core versions is a bit trick. You can refer to this link for further information.

这篇关于编写一个在 EF Core 中不区分大小写的搜索查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！