更好或优化的方式通过3下拉过滤器来过滤客户记录 [英] Better or optimized way to filter customer records by 3 dropdown filters
本文介绍了更好或优化的方式通过3下拉过滤器来过滤客户记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个页面名称为： CustomerList.aspx 在对此我显示的客户名单
这是我的表和类文件也：
公共部分类客户
{
公众诠释客户ID {搞定;组; }
公共字符串全名{获得;组; }
公共字符串EMAILID {搞定;组; }
公众诠释CustomerLocation {搞定;组; }
公共布尔IsActive {搞定;组; }
公共BOOL删除{搞定;组; }
公众的DateTime SubscribeDate {搞定;组; }
公共场所_Location;
} 公共部分类位置
{
公众诠释LocationId {搞定;组; }
公共字符串名称{;组; }
}
无效=真：指客户是活跃在系统中搜索。
未激活=假：指客户在系统处于非活动状态
移除=真：装置客户从系统中去除
删除=假：是指客户未从系统中删除
。我公司将为用户提供3过滤器来过滤客户记录。
1）位置下拉
＆LT;选择＆GT;
＆LT;选项文本=所有VALUE =0选择=真正的＆GT;
＆LT;选项文本=伦敦VALUE =1＆GT;
＆LT;选项文本=美国VALUE =2＆GT;
＆LT; /选择＆GT;2）状态下拉与值：全部，有效，无效
＆LT;选择＆GT;
＆LT;选项文本=所有VALUE =0选择=真正的＆GT;
＆LT;选项文本=活动VALUE =1＆GT;
＆LT;选项文本=无效VALUE =2＆GT;
＆LT; /选择＆GT;3）统计数据下拉
＆LT;选择＆GT;
＆LT;选项文本=所有VALUE =所有选择=真正的＆GT;
＆LT;选项文本=活跃客户VALUE =所有＆GT;
＆LT;选项文本=最近的订阅客户VALUE =订阅＆GT;
＆LT;选项文本=最近退订客户VALUE =退订＆GT;
＆LT; /选择＆GT;由于我的页面加载，我想在我的网格显示的客户名单。
这是我的code：
公共无效DisplayCustomersList（）
{
数据表list=GetCustomers(Convert.ToInt16(ddlLocation.SelectedValue),Convert.ToInt16(ddlStatus.SelectedValue),ddlstats.SelectedValue);
Grid1.DataSource =清单;
Grid1.DataBind（）;
}
公共GetCustomers的数据表（INT LocationId，诠释ActiveId，字符串数据）
{
使用（VAR上下文=新MyContext（））
{
从context.CustomersÇVAR数据=
其中，c.Removed ==假
新选择
{
全名= c.FullName，
c.CustomerID，
c._Location.Name，
c.IsActive，
c.SubscribeDate，
c.Removed
};
如果（LocationId = 0＆放大器;！＆放大器;！ActiveId = 0）
{
如果（ActiveId == 1）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.LocationId == LocationId＆放大器;＆安培; x.IsActive ==真放;＆安培; x.Removed == FALSE））;
}
否则如果（ActiveId == 2）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.LocationId == LocationId＆放大器;＆安培; x.IsActive ==假放;＆安培; x.Removed == FALSE））;
}
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.LocationId == LocationId＆放大器;＆安培; x.Removed == FALSE））;
} 如果（LocationId = 0＆安培;！＆安培;！统计=）
{
如果（统计==所有）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.LocationId == LocationId＆放大器;＆安培; x.IsActive ==真放;＆安培; x.Removed == FALSE））;
}
否则，如果（统计==订阅）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）及＆放大器; x.Removed ==假放大器;＆放大器; x.LocationId == LocationId））;
}
} 如果（ActiveId = 0＆安培;！＆安培;！统计=）
{
如果（ActiveId == 1）
{
如果（统计==所有）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT;（x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）|| x.IsActive == TRUE）||（x.Removed ==假）||（x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）|| x.IsActive ==假）））;
}
否则，如果（统计==订阅）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）及和放大器; x.Removed ==假放;＆安培; x.IsActive == TRUE））;
}
否则，如果（统计==退订）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）及＆放大器; x.Removed ==假放大器;＆放大器; x.IsActive ==假））;
} }
否则如果（ActiveId == 2）
{
如果（统计==所有）
{
MyContext.CopyToDataTable（
data.Where（X =＆GT;（x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）|| x.IsActive ==假）及及（x.Removed ==假）））;
}
否则，如果（统计==订阅）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）及＆放大器; x.Removed ==假放大器;＆放大器; x.IsActive ==假））;
}
否则，如果（统计==退订）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）及＆放大器; x.Removed ==假放大器;＆放大器; x.IsActive ==假））;
}
}
} 如果（统计！=）
{
如果（统计==所有）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.IsActive ==真放;＆安培; x.Removed == FALSE））;
}
否则，如果（统计==订阅）
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）及和放大器; x.Removed ==假放;＆安培; x.IsActive == TRUE））;
}
其他
{
返回
MyContext.CopyToDataTable（
data.Where（X =＆GT; x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）及＆放大器; x.IsActive ==假放大器;＆放大器; x.Removed ==假））;
} }
}
}在所有的
3下拉菜单中选择指数的变化情况我打电话是这样这样的功能：DisplayCustomersList（）所以，我只是想问问你，这是执行器以适当的方式或本code能以更好的方式来进行优化。
有谁能够提供我更好的解决方案或者如果可能的话???更好的方式优化这种code
解决方案这问题不
predicateBuilder进行优化，但它需要细心和系统性的分析首先...考虑您的predicate使确定因子
考虑你的情况下，把他们的全部14个这样在一起，你实际上可以看到，你只有三的决定因素，即：
LocationId，ActiveId和统计：无LocationId ActiveId统计结果
1不为0 1不在乎data.Where（X =＆GT; x.LocationId == LocationId＆放大器;＆安培; x.IsActive ==真放;＆安培; x.Removed ==假）
2不为0 2不在乎data.Where（X =＆GT; x.LocationId == LocationId＆放大器;＆安培; x.IsActive ==假放;＆安培; x.Removed == FALSE））;
3不为0不是0-2不在乎data.Where（X =＆GT; x.LocationId == LocationId＆放大器;＆安培; x.Removed == FALSE））;
4不为0不在乎所有data.Where（X =＆GT; x.LocationId == LocationId＆放大器;＆安培; x.IsActive ==真放;＆安培; x.Removed ==假）
5不为0不在乎订阅data.Where（X =＆GT; x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）及＆放大器; x.Removed ==假放大器;＆放大器; x.LocationId == LocationId））;
6不在乎1所有data.Where（X =＆GT;（x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）|| x.IsActive == TRUE）||（x.Removed == FALSE） ||（x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）|| x.IsActive ==假）））;
7不在乎1订阅data.Where（X =＆GT; x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）及和放大器; x.Removed ==假放;＆安培; x.IsActive ==真））;
8不在乎1退订data.Where（X =＆GT; x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）及和放大器; x.Removed ==假放;＆安培; x.IsActive ==假））;
9不在乎2所有data.Where（X =＆GT;（x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）|| x.IsActive ==假）及及（x.Removed ==假）））;
10不在乎2订阅data.Where（X =＆GT; x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）及和放大器; x.Removed ==假放;＆安培; x.IsActive ==假））;
11不在乎2退订data.Where（X =＆GT; x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）及和放大器; x.Removed ==假放;＆安培; x.IsActive ==假））;
12不关心不在乎所有data.Where（X =＆GT; x.IsActive ==真放;＆安培; x.Removed == FALSE））;
13不关心不在乎订阅data.Where（X =＆GT; x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）及和放大器; x.Removed ==假放;＆安培; x.IsActive ==真））;
14不在乎不关心退订data.Where（X =＆GT; x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）及和放大器; x.Removed ==假放;＆安培; x.IsActive == FALSE））;其次，考虑结果的模式
我观察到你的结果是小例外相当确定性。除了结果没有
6并没有9，您的查询predicates实际上可以分为四基本组件（6和9略）。它们是：COMP1：x.LocationId == LocationId
COMP2：x.IsRemoved ==假
COMP3：x.IsActive ==真
COMP4：x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）和查询的逻辑很简单：
COMP1和放大器;＆安培; COMP2和放大器;＆安培; COMP3＆功放;＆安培; COMP4把它们与12例（不包括案件
6和9）在一起，你会得到：简化：
DC =不在乎
采用A =
NA =不适用 QueryComponents
没有LocationId ActiveId统计COMP1 COMP2 COMP3 COMP4
1不为0 1 DC A A是NA
2不为0 2直流A A无NA
3不为0不是0-2 DC A A NA NA
4不为0 DC所有A A是NA
5不为0 DC认购A A呐
7 DC 1订阅NA A是一
8 DC 1退订呐否A
10 DC 2订阅呐否A
11 DC 2退订呐否A
12 DC-DC都NA A是一
13 DC-DC订阅NA A是一
14 DC-DC退订呐否A全部打破后，我们可以看到映射
现在，可以看出，该查询组件可以一起决定因素映射回
COMP1：仅适用于如果LocationId不为0
COMP2：始终应用//这是非常好的！
COMP3：是= 1，4，7，12，13; NA = 3,5;无= 2，8，10，11，14
COMP4：不应用时LocationId，除了对案件5 0您可以开始翻译理念融入到codeS ...
因此，我们可以做一些帮助标志（有其中4）或以确定如果查询组件时，应列入不，是这样的：
布尔LocationIdNotApplied = LocationId == 0; //为COMP1
布尔IsActiveNotApplied = LocationId = 0＆安培;！＆安培; （ActiveId℃，|| ActiveId→2 ||统计=订阅）; //对于要施加或不COMP3
布尔IsActiveFalse =（LocationId = 0＆安培;！＆安培; ActiveId == 2）||统计==退订|| （ActiveId == 2和;＆放大器;统计==订阅）; //为COMP3是假的
布尔DateApplied = LocationId == 0 || （LocationId = 0＆安培;！＆安培;统计==订阅）;那么你的
data.Where除外6和9 可以简化如下：data.Where（X =＆GT;（x.LocationId == || LocationId LocationIdNotApplied）// COMP1
＆功放;＆安培; x.IsRemoved ==虚假// COMP2
＆功放;＆安培; （（x.IsActive ==！IsActiveFalse）|| IsActiveNotApplied）// COMP3
＆功放;＆安培; （x.SubscribeDate＆GT;！= DateTime.Now.AddDays（-7）|| DateApplied））// COMP4这是12例显著简化成为1的情况下，你只需要添加额外的两种情况，3例共而非原件14表壳！
把它们一起放入code
公共GetCustomers的数据表（INT LocationId，诠释ActiveId，字符串数据）
{
使用（VAR上下文=新MyContext（））
{
从context.CustomersÇVAR数据=
其中，c.Removed ==假
新选择
{
全名= c.FullName，
c.CustomerID，
c._Location.Name，
c.IsActive，
c.SubscribeDate，
c.Removed
}; 布尔LocationIdNotApplied = LocationId == 0; //为COMP1
布尔IsActiveNotApplied = LocationId = 0＆安培;！＆安培; （ActiveId℃，|| ActiveId→2 ||统计=订阅）; //对于要施加或不COMP3
布尔IsActiveFalse =（LocationId = 0＆安培;！＆安培; ActiveId == 2）||统计==退订|| （ActiveId == 2和;＆放大器;统计==订阅）; //为COMP3是假的
布尔DateApplied = LocationId == 0 || （LocationId = 0＆安培;！＆安培;统计==订阅）; 如果（LocationId == 0安培;＆安培; ActiveId == 1安培;＆安培;统计==所有）{//案例6
返回MyContext.CopyToDataTable（
data.Where（X =＆GT;（x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）|| x.IsActive == TRUE）||（x.Removed ==假）||（x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）|| x.IsActive ==假）））;
}否则如果（LocationId == 0＆放大器;＆放大器; ActiveId == 2和;＆放大器;统计==所有）{//外壳9
返回MyContext.CopyToDataTable（
data.Where（X =＆GT;（x.SubscribeDate＆GT = DateTime.Now.AddDays（-7）|| x.IsActive ==假）及及（x.Removed ==假）））;
}其他{//其他情况
返回MyContext.CopyToDataTable（
data.Where（X =＆GT;（x.LocationId == || LocationId LocationIdNotApplied）// COMP1
＆功放;＆安培; x.IsRemoved ==虚假// COMP2
＆功放;＆安培; （（x.IsActive ==！IsActiveFalse）|| IsActiveNotApplied）// COMP3
＆功放;＆安培; （x.SubscribeDate＆GT;！= DateTime.Now.AddDays（-7）|| DateApplied）））// COMP4
}
}
}最后说明
您案件6竟觉得奇怪，我：
data.Where（X =＆GT;（x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）|| x.IsActive == TRUE）||（X。去掉==假）||（x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）|| x.IsActive == FALSE）））;请注意您同时拥有
x.IsActive ==真和x.IsActive ==虚假为x.SubscribeDate＆GT; = DateTime.Now.AddDays（-7）。而你与结合起来||。这就好比说：（A ||真）|| （A ||假）和始终将
返回true不管是什么。您可能要再次检查，你甚至可以进一步简化/最后说明并道歉
于是去我对这种情况下的解决方案，而不
predicateBuilder- 它需要细心和系统性（或什么其实我的意思是一步一步）分析所有可能的情况。我有，因为我的道歉的OP不能完全测试我已经提出了缺乏完整的测试资源（不像OP）的code。
但如果OP发现有我错过办案或未在原来的问题摆在OP，最起码的步骤，$ P $在psented我解决上述应的仍然是有用的为OP，使他/她自己的认真分析他/她的实际情况。
I have one page name as:CustomerList.aspx on which i am displaying list of customers.
This is my table and class files too:
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:Means customer is active in the system.
Inactive=false:Means customer is inactive in the system.Removed=true:Means customer is removed from the system
Removed=false:Means customer is not removed from the system.
I will provide user with 3 filters to filter customer records.
1)Location Dropdown
<select> <option Text="All" Value="0" selected="true"> <option Text="London" Value="1"> <option Text="America" Value="2"> </select>2)Status Dropdown with Value:All,Active,Inactive:
<select> <option Text="All" Value="0" selected="true"> <option Text="Active" Value="1"> <option Text="Inactive" Value="2"> </select>3)Stats Data Dropdown:
<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>As my page is loaded i want to display customers list in my grid.
This is my code:
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)); } } } }On all
3 dropdown selected index change eventi am just calling this function like this:DisplayCustomersList()So i just want to ask you that is this a proper way to perform filters or this code can be optimize in a better way.
can anybody provide me better solution or optimize this code in better way if possible???
解决方案This problem can be optimized without
PredicateBuilder, but it requires careful and "systematic" analysis.
To begin with... Consider Your Predicate Making Determining Factor
Consider your case, putting all 14 of them together like this, you actually can see that you only have three determining factors, namely:
LocationId,ActiveId, andstats: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));
Next, consider the pattern of your results
I observe that your result is quite deterministic with small exceptions. Apart from result no
6and no9, your query predicates can actually be separated into four basic components (6and9omitted). They are:comp1: x.LocationId == LocationId comp2: x.IsRemoved == false comp3: x.IsActive == true comp4: x.SubscribeDate >= DateTime.Now.AddDays(-7)And the query logic is simply:
comp1 && comp2 && comp3 && comp4Putting them together with the 12 cases (excluding the cases
6and9), you would get: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
After all the breaking down, we can see the Mapping
Now, it can be seen that the query components can be mapped back together with the determining factors:
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
You can start translating the concept into codes...
Thus, we can make some helping flags (there are 4 of them) to determine if a query component should be included or not, like this:
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");Then your
data.Wherefor all cases except6and9can be simplified like this:data.Where(x => (x.LocationId == LocationId || LocationIdNotApplied) //comp1 && x.IsRemoved == false //comp2 && ((x.IsActive == !IsActiveFalse) || IsActiveNotApplied) //comp3 && (x.SubscribeDate >= DateTime.Now.AddDays(-7) || !DateApplied)) //comp4This is a significant simplification for 12 cases becoming 1 case, and you only need to add the additional two cases, totalling in 3 cases rather than the original 14 cases!
Putting Them together into code
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 } } }
Last Notes
Your case 6 actually seems strange to me:
data.Where(x => (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == true) || (x.Removed == false) || (x.SubscribeDate >= DateTime.Now.AddDays(-7) || x.IsActive == false)));Note that you have both
x.IsActive == trueandx.IsActive == falseforx.SubscribeDate >= DateTime.Now.AddDays(-7). And you combine it with||. This is like saying:(A || true) || (A || false)And will always
return trueno matter what. You might want to check again and you may even simplify further/
Last Remarks and Apologize
Thus goes my solution for this case without
PredicateBuilder- it requires careful and "systematic" (or, what I actually mean is step by step) analysis on all possible cases.I have to apologize the OP because I cannot fully test the code which I have proposed for lacking of complete testing resources (unlike the OP).
But if the OP finds that there is a case which I miss to handle or is not put by the OP in the original question, at the very least, the steps presented in my solution above should still be useful for the OP to make his/her own careful analysis for his/her actual cases.
这篇关于更好或优化的方式通过3下拉过滤器来过滤客户记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
