通过 3 个下拉过滤器过滤客户记录的更好或优化的方法
本文关键字：记录 更好 方法 优化 客户 过滤 过滤器 通过 | 更新日期: 2023-09-27 18:34:05
我有一个页面名称为：CustomerList.aspx我正在上面显示客户列表。
这也是我的表和类文件：
public partial class Customer
{
public int CustomerID { get; set; }
public string FullName { get; set; }
public string EmailId { get; set; }
public int CustomerLocation { get; set; }
public bool IsActive { get; set; }
public bool Removed { get; set; }
public DateTime SubscribeDate { get; set; }
public Location _Location;
}
public partial class Location
{
public int LocationId { get; set; }
public string Name { get; set; }
}
Inactive=true：表示客户在系统中处于活动状态><。非活动=假：表示客户在系统中处于非活动状态。
已删除=true：表示客户已从系统中删除
已删除=假：表示客户未从系统中删除。
我将为用户提供 3 个过滤器来过滤客户记录。
1(位置下拉菜单
<select>
<option Text="All" Value="0" selected="true">
<option Text="London" Value="1">
<option Text="America" Value="2">
</select>
2(状态下拉列表的值：全部，活动，非活动：
<select>
<option Text="All" Value="0" selected="true">
<option Text="Active" Value="1">
<option Text="Inactive" Value="2">
</select>
3(统计数据下拉列表：
<select>
<option Text="All" Value="all" selected="true">
<option Text="Active Customers" Value="all">
<option Text="Recent subscribe customers" Value="subscribe">
<option Text="Recent unsubscribe customers" Value="unsubscribe">
</select>
加载页面时，我想在我的网格中显示客户列表。
这是我的代码：
public void DisplayCustomersList()
{
DataTable list=GetCustomers(Convert.ToInt16(ddlLocation.SelectedValue),Convert.ToInt16(ddlStatus.SelectedValue),ddlstats.SelectedValue);
Grid1.DataSource = list;
Grid1.DataBind();
}
public DataTable GetCustomers(int LocationId, int ActiveId, string stats)
{
using (var context = new MyContext())
{
var data = from c in context.Customers
where c.Removed == false
select new
{
FullName = c.FullName,
c.CustomerID,
c._Location.Name,
c.IsActive,
c.SubscribeDate,
c.Removed
};
if (LocationId != 0 && ActiveId != 0)
{
if (ActiveId == 1)
{
return
MyContext.CopyToDataTable(
data.Where(x => x.LocationId == LocationId && x.IsActive == true && x.Removed == false));
}
else if(ActiveId==2)
{
return
MyContext.CopyToDataTable(
data.Where(x => x.LocationId == LocationId && x.IsActive == false && x.Removed == false));
}
return
MyContext.CopyToDataTable(
data.Where(x => x.LocationId == LocationId && x.Removed==false));
}
if (LocationId != 0 && stats != "")
{
if (stats == "all")
{
return
MyContext.CopyToDataTable(
data.Where(x => x.LocationId == LocationId && x.IsActive == true && x.Removed == false));
}
else if (stats == "subscribe")
{
return
MyContext.CopyToDataTable(
data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.LocationId==LocationId));
}
}
if (ActiveId != 0 && stats != "")
{
if(ActiveId==1)
{
if(stats=="all")
{
return
MyContext.CopyToDataTable(
data.Where(x => (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == true) || (x.Removed == false) || (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == false)));
}
else if (stats == "subscribe")
{
return
MyContext.CopyToDataTable(
data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == true));
}
else if (stats == "unsubscribe")
{
return
MyContext.CopyToDataTable(
data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == false));
}
}
else if(ActiveId==2)
{
if (stats == "all")
{
MyContext.CopyToDataTable(
data.Where(x => (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == false) && (x.Removed == false)));
}
else if (stats == "subscribe")
{
return
MyContext.CopyToDataTable(
data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == false));
}
else if (stats == "unsubscribe")
{
return
MyContext.CopyToDataTable(
data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == false));
}
}
}
if (stats != "")
{
if (stats == "all")
{
return
MyContext.CopyToDataTable(
data.Where(x => x.IsActive == true && x.Removed == false));
}
else if (stats == "subscribe")
{
return
MyContext.CopyToDataTable(
data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive==true));
}
else
{
return
MyContext.CopyToDataTable(
data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.IsActive == false && x.Removed == false));
}
}
}
}
在所有3 dropdown selected index change event我只是像这样调用这个函数：DisplayCustomersList()
所以我只想问你，这是执行过滤器的正确方法，还是可以更好地优化此代码。
如果可能的话，任何人都可以为我提供更好的解决方案或以更好的方式优化此代码吗???
这个问题可以在没有PredicateBuilder的情况下进行优化，但它需要仔细和"系统"的分析。
首先...考虑您的谓词决定因素
考虑一下您的情况，将它们中的 14 个放在一起，您实际上可以看到您只有三个决定因素，即：LocationId、ActiveId 和 stats：
No LocationId ActiveId stats result
1 not 0 1 don't care data.Where(x => x.LocationId == LocationId && x.IsActive == true && x.Removed == false)
2 not 0 2 don't care data.Where(x => x.LocationId == LocationId && x.IsActive == false && x.Removed == false));
3 not 0 not 0-2 don't care data.Where(x => x.LocationId == LocationId && x.Removed == false));
4 not 0 don't care all data.Where(x => x.LocationId == LocationId && x.IsActive == true && x.Removed == false)
5 not 0 don't care subscribe data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.LocationId == LocationId));
6 don't care 1 all data.Where(x => (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == true) || (x.Removed == false) || (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == false)));
7 don't care 1 subscribe data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == true));
8 don't care 1 unsubscribe data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == false));
9 don't care 2 all data.Where(x => (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == false) && (x.Removed == false)));
10 don't care 2 subscribe data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == false));
11 don't care 2 unsubscribe data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == false));
12 don't care don't care all data.Where(x => x.IsActive == true && x.Removed == false));
13 don't care don't care subscribe data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == true));
14 don't care don't care unsubscribe data.Where(x => x.SubscribeDate >= DateTime.Now.AddDays(-7) && x.Removed == false && x.IsActive == false));
接下来，考虑结果的模式
我观察到您的结果非常确定，只有小例外。除了结果没有6和没有9之外，你的查询谓词实际上可以分为四个基本组成部分(6和省略9(。它们是：
comp1: x.LocationId == LocationId
comp2: x.IsRemoved == false
comp3: x.IsActive == true
comp4: x.SubscribeDate >= DateTime.Now.AddDays(-7)
查询逻辑很简单：
comp1 && comp2 && comp3 && comp4
将它们与 12 个案例(不包括6和9案例(放在一起，您将获得：
Simplification:
DC = don't care
A = applied
NA = not applied
QueryComponents
No LocationId ActiveId stats comp1 comp2 comp3 comp4
1 not 0 1 DC A A Yes NA
2 not 0 2 DC A A No NA
3 not 0 not 0-2 DC A A NA NA
4 not 0 DC all A A Yes NA
5 not 0 DC subscribe A A NA A
7 DC 1 subscribe NA A Yes A
8 DC 1 unsubscribe NA A No A
10 DC 2 subscribe NA A No A
11 DC 2 unsubscribe NA A No A
12 DC DC all NA A Yes A
13 DC DC subscribe NA A Yes A
14 DC DC unsubscribe NA A No A
在所有分解之后，我们可以看到映射
现在，可以看到查询组件可以与决定因素一起映射回：
comp1: Applied only when LocationId is not 0
comp2: Always applied //this is very good!
comp3: Yes = 1, 4, 7, 12, 13; NA = 3, 5; No = 2, 8, 10, 11, 14
comp4: Not Applied when LocationId is 0 except on case 5
您可以开始将概念转换为代码...
因此，我们可以制作一些帮助标志(其中有 4 个(来确定是否应该包含查询组件，如下所示：
bool LocationIdNotApplied = LocationId == 0; //for comp1
bool IsActiveNotApplied = LocationId != 0 && (ActiveId < 0 || ActiveId > 2 || stats = "subscribe"); //for comp3 to be applied or not
bool IsActiveFalse = (LocationId != 0 && ActiveId == 2) || stats == "unsubscribe" || (ActiveId == 2 && stats == "subscribe"); //for comp3 to be false
bool DateApplied = LocationId == 0 || (LocationId != 0 && stats == "subscribe");
然后，除6和9之外的所有情况的data.Where可以像这样简化：
data.Where(x => (x.LocationId == LocationId || LocationIdNotApplied) //comp1
&& x.IsRemoved == false //comp2
&& ((x.IsActive == !IsActiveFalse) || IsActiveNotApplied) //comp3
&& (x.SubscribeDate >= DateTime.Now.AddDays(-7) || !DateApplied)) //comp4
这是一个显着的简化，将 12 个案例变为 1 个案例，您只需要添加额外的两个案例，总共 3 个案例，而不是原来的 14 个案例！
将它们组合到代码中
public DataTable GetCustomers(int LocationId, int ActiveId, string stats)
{
using (var context = new MyContext())
{
var data = from c in context.Customers
where c.Removed == false
select new
{
FullName = c.FullName,
c.CustomerID,
c._Location.Name,
c.IsActive,
c.SubscribeDate,
c.Removed
};
bool LocationIdNotApplied = LocationId == 0; //for comp1
bool IsActiveNotApplied = LocationId != 0 && (ActiveId < 0 || ActiveId > 2 || stats = "subscribe"); //for comp3 to be applied or not
bool IsActiveFalse = (LocationId != 0 && ActiveId == 2) || stats == "unsubscribe" || (ActiveId == 2 && stats == "subscribe"); //for comp3 to be false
bool DateApplied = LocationId == 0 || (LocationId != 0 && stats == "subscribe");
if(LocationId == 0 && ActiveId == 1 && stats == "all"){ //case 6
return MyContext.CopyToDataTable(
data.Where(x => (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == true) || (x.Removed == false) || (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == false)));
} else if (LocationId == 0 && ActiveId == 2 && stats == "all"){ //case 9
return MyContext.CopyToDataTable(
data.Where(x => (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == false) && (x.Removed == false)));
} else { //other cases
return MyContext.CopyToDataTable(
data.Where(x => (x.LocationId == LocationId || LocationIdNotApplied) //comp1
&& x.IsRemoved == false //comp2
&& ((x.IsActive == !IsActiveFalse) || IsActiveNotApplied) //comp3
&& (x.SubscribeDate >= DateTime.Now.AddDays(-7) || !DateApplied))) //comp4
}
}
}
最后的笔记
你的案例 6 对我来说实际上很奇怪：
data.Where(x => (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == true) || (x.Removed == false) || (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == false)));
请注意，您同时具有用于x.SubscribeDate >= DateTime.Now.AddDays(-7)的x.IsActive == true和x.IsActive == false。你把它和||结合起来.这就像在说：
(A || true) || (A || false)
无论如何，都会return true。您可能想再次检查，甚至可以进一步简化/
最后一句话和道歉
因此，我对这种情况的解决方案没有PredicateBuilder - 它需要对所有可能的情况进行仔细和"系统"(或者，我实际上的意思是逐步(分析。
必须向OP道歉，因为我无法完全测试我提出的缺乏完整测试资源的代码(与OP不同(。
但是，如果OP发现有一个我错过处理的案例，或者OP没有在原始问题中提出，至少，我上面解决方案中提出的步骤应该仍然有助于OP对他/她的实际案例进行自己的仔细分析。
您可以在 GetCustomer 方法中优化搜索条件。请看一下 林克基特
这里的基本示例
http://www.albahari.com/nutshell/predicatebuilder.aspxhttps://www.nuget.org/packages/LinqKit/
使用 PredicateBuilder
下面介绍如何使用 PredicateBuilder 解决前面的示例：
IQueryable<Product> SearchProducts (params string[] keywords)
{
var predicate = PredicateBuilder.False<Product>();
foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or (p => p.Description.Contains (temp));
}
return dataContext.Products.Where (predicate);
}
首先，我不会再使用DataTable，而是创建一个这样的ViewModel：
public class CustomerVm
{
public string FullName { get; set; }
public int CustomerID { get; set; }
public string LocationName { get; set; }
public bool IsActive { get; set; }
public DateTime SubscribeDate { get; set; }
}
然后创建一个基本查询来吸引客户：
var data = from c in context.Customers
where c.Removed == false;
根据下拉列表值操作查询的 where 条件：
if(ActiveId == 1)
{
data = data.Where(c => c.IsActive);
}
else if(ActiveId == 1)
{
data = data.Where(c => c.IsActive);
}
if (LocationId != 0)
{
data = data.Where(c => c.LocationId == LocationId);
}
if (stats == "subscribe")
{
data = data.Where(c => c.IsActive
&& c.SubscribeDate >= DateTime.Now.AddDays(-7));
}
else if (stats == "unbsubscribe")
{
data = data.Where(c => !c.IsActive
&& c.SubscribeDate >= DateTime.Now.AddDays(-7));
}
最后，执行查询并将结果作为视图模型返回：
return data.Select(c => new CustomerVm {
FullName = c.FullName,
CustomerId = ...
});
我发现另一种方法更具可扩展性。性能的真正重大改进是在纯SQL中实际执行此过滤，并且仅将匹配的数据移动到应用程序逻辑中。
我能够通过以下方式使这种查询速度提高 100 倍(使用 Oracle(：
- 添加接受搜索条件的 PL/SQL 过程
- 构建动态 SQL 语句并在那里绑定搜索条件
- 确保没有"空条件"，即如果未设置位置，则我们不会在 where (location=in_location 或 in_location 为 null(
- 这给出了一些固定的 SQL，其中文本和执行计划将被缓存
有关基础知识的更多信息，请访问 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908
数据库是为在无数条记录上进行数据访问、过滤和数据连接而构建的。我发现利用这一点比任何具有严重限制的ORM工具都具有竞争优势(您放弃了数据库端处理的全部功能，放弃了所有高级SQL，例如分区，并且无法访问Oracle文本等技术，因为您无法控制查询放宽(。