我基于这篇文章
.NET Core Npgsql.EntityFrameworkCore ILikeExpression 实现了一个动态搜索。我做的是这样的:
我实现了[Searchable]属性,用它来标记需要执行搜索的属性。这些属性仅为字符串类型,如果需要,我可以解释如何搜索长整型和整型属性。
[AttributeUsage(AttributeTargets.Property)]
public class SearchableAttribute : Attribute
{
}
已经为IQueryable创建了一个扩展,它从搜索中接收输入字符串,并根据指定属性实现Like函数。
public static class QueryableExtension
{
public static IQueryable<TEntityDto> ExecuteQueryFilter<TEntityDto>(this IQueryable<TEntityDto> queryable, string query)
where TEntityDto : class, IEntityDto
{
if (string.IsNullOrEmpty(query))
{
return queryable;
}
var properties = typeof(TEntityDto).GetProperties()
.Where(p => p.PropertyType == typeof(string) &&
p.GetCustomAttributes(typeof(SearchableAttribute), true).FirstOrDefault() != null)
.Select(x => x.Name).ToList();
if (!properties.Any())
{
return queryable;
}
ParameterExpression entity = Expression.Parameter(typeof(TEntityDto), "entity");
var efLikeMethod = typeof(DbFunctionsExtensions).GetMethod("Like",
BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic,
null,
new[] { typeof(DbFunctions), typeof(string), typeof(string) },
null);
var pattern = Expression.Constant($"%{query}%", typeof(string));
Expression body = Expression.Constant(false);
foreach (var propertyName in properties)
{
var property = Expression.Property(entity, propertyName);
Expression expr = Expression.Call(efLikeMethod,
Expression.Property(null, typeof(EF), nameof(EF.Functions)), property, pattern);
body = Expression.OrElse(body, expr);
}
var expression = Expression.Lambda<Func<TEntityDto, bool>>(body, entity);
return queryable.Where(expression);
}
}
Dto对象本身看起来像这样:
public class CategoryDto : IEntityDto
{
public long Id { get; set; }
[Searchable]
public string Name { get; set; }
[Searchable]
public string IconKey { get; set; }
public long UploadId { get; private set; }
[Searchable]
public string UploadFileName { get; set; }
[Searchable]
public string CreatedBy { get; set; }
public DateTime Created { get; set; }
}
我在100万条记录中测试了这种搜索方法,对象名称由1到5个单词组成。搜索过程非常快。此处的性能优势在于表达式被转换为数据库端的LINQ to SQL。
DbFunctionsExtensions
和Like
。 - Ivan Stoev