在 Entity Framework Core 中使用动态过滤器 [英] Using dynamic filters with Entity Framework Core
问题描述
我正在开发一个使用 Entity Framework Core 的应用程序(.Net Core 3.1、C# 8).
I'm developing an application (.Net Core 3.1, C# 8) that is using Entity Framework Core.
我想用多个过滤选项过滤一个表格.
I would like to filter a table with several filtering options.
我在 JSON 中获取过滤条件,并将其反序列化为一个对象.我想编写一个 where LINQ 查询,它将根据这些动态过滤选项过滤表.
I'm getting the filter conditions in JSON and I'm deserializing it into an object. I want to write a where LINQ query which will filter the table based on those dynamic filtering options.
问题是我需要使用许多选项和组合来管理过滤.
The twist is I need to manage the filtering with many options and combinations.
- 您可以过滤
market
、country
、vendor
,其余的过滤选项将为null
. - 您想过滤
country
和vendor
那么market
将为null
以及其余的过滤器选项.
- You can filter for the
market
,country
,vendor
and the rest of the filter options will benull
. - You would like to filter for
country
andvendor
then themarket
will benull
and also the rest of the filter options.
我正在查询一个巨大的表,因此编写一个完全转换为 SQL 的查询非常重要.
I'm querying a huge table, so it is important to write a query which translates fully into SQL.
以下代码无法正常工作.我正在寻找可以解决此问题的类似方法:
The following code is not working properly. I'm looking for something similar that can solve this issue:
var filters = new demoFilterEntity()
{
Market = new List<string>() { "LAT", "NAM" }
};
var filteredData = demoMainRepository.GetAll().Where(x =>
x.Market != null && (filters.Market != null ? filters.Market.Contains(x.Market) : false) &&
x.Country != null && (filters.Country != null ? filters.Country.Contains(x.Market) : false)).ToList();
我很感激有关如何解决此问题并动态管理过滤的建议.
I would appreciate suggestions on how I can get through this and manage the filtering dynamically.
推荐答案
如果您只有 AND
条件,您只需链接 Where
子句即可:
If you have only AND
conditions you can do it with just chaining Where
clauses:
var query = demoMainRepository.GetAll().Where(x => x.Market != null);
if(filters.Market != null)
{
query = query.Where(x => filters.Market.Contains(x.Market));
}
...
var filteredData = query.ToList();
另外,正如 @Lajos Arpad 所说,也许您需要考虑组合字段空检查(即 x.Market != null
) 与过滤器检查:
Also as @Lajos Arpad said maybe you need to consider combining fields null checks (i.e. x.Market != null
) with the filters checks:
var query = demoMainRepository.GetAll();
if(filters.Market != null)
{
query = query.Where(x => x.Market != null && filters.Market.Contains(x.Market));
}
...
var filteredData = query.ToList();
这篇关于在 Entity Framework Core 中使用动态过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!