ef core 2.0 使用包含过滤器搜索多列 [英] ef core 2.0 search multiple columns with contains filter

问题描述

假设实体，Patient存在于上下文中，它是使用数据库优先的方法生成的，所以我不能修改数据库.

Assume entity, Patient exists in the context, which has been generated using the database-first approach, so I can not modify the database.

public class Patient
{
   public string Id { get; set; }
   public string Surname { get; set; }
   public string Forename { get; set; }
}

我想按姓名过滤患者，例如名字或姓氏或全名.我目前有以下查询:

I would like to filter patients by name e.g. first name or last name or full name. I currently have the following query:

await query
         .Where(p => p.Forename != null && p.Forename.ToLower().Contains(filter) ||
                     p.Surname != null && p.Surname.ToLower().Contains(filter))
         .ToListAsync();

这显然只单独检查forename/surname 列.如果过滤器是全名，则不起作用.

This obviously only checks the forename/surname columns individually. If the filter is the full name, it does not work.

我尝试在 where 子句中进行字符串插值，以针对名字和姓氏的组合应用 contains 过滤器，但它在 ef 核心中不受支持并在本地执行.由于数据库中有超过一百万的患者，因此在应用程序中本地执行查询不是一种选择，必须在数据库中完成(搜索需要一分钟以上).

I tried string interpolation within the where clause to apply the contains filter against the combination of forename and surname but it is not supported in ef core and executes locally. Since there are over a million patients in the database, executing the query locally in the application is not an option and must be done in the database (it takes over a minute for a search).

有什么办法可以解决这个问题吗?

Is there any way to solve this?

推荐答案

插值不能被 EF 翻译.

Interpolation can't be translated by EF.

只需使用+"运算符连接如下字符串:

Just use the '+' operator to concatenate the strings like below:

await query
     .Where(p => p.Forename.ToLower().Contains(filter) ||
                 p.Surname.ToLower().Contains(filter)) || 
                 (p.Forename + " " + p.Surname).ToLower().Contains(filter))
     .ToListAsync();

这篇关于ef core 2.0 使用包含过滤器搜索多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！