All geek questions in one place

Improved or optimized way to filter customer records using three drop-down filters

I have one page name: CustomerList.aspx , on which I display a list of customers.

These are my tables and class files:

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 that the client is active on the system.
Inactive = false: means that the client is inactive on the system.

Deleted = true: means the client has been removed from the system

Deleted = false: means that the client is not removed from the system.

I provided the user with 3 filters for filtering customer records.

1) Drop-down list location

<select>
<option Text="All" Value="0" selected="true">
<option Text="London" Value="1">
<option Text="America" Value="2">
</select>

2) The expanded status with the value: all, active, inactive:

<select>
<option Text="All" Value="0" selected="true">
<option Text="Active" Value="1">
<option Text="Inactive" Value="2">
</select>

3) Drop-down statistics :

<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()

, , , .

- , ?

+4
4

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));

, . no 6 no 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");

data.Where , 6 9, :

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 , , , 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.IsActive == true x.IsActive == false x.SubscribeDate >= DateTime.Now.AddDays(-7). ||. :

(A || true) || (A || false)

return true , . , , /

, PredicateBuilder - "" (, ) .

OP, , - ( OP).

OP , OP , , , , , - , / .

+3

GetCustomer. , Linkkit

http://www.albahari.com/nutshell/predicatebuilder.aspx https://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);
}
+2

, 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;

:

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));  
}

ViewModels:

return data.Select(c => new CustomerVm {
    FullName = c.FullName,
    CustomerId = ...
});
+1

. - SQL .

100 ( Oracle):

  • pl/sql,
  • SQL
  • , " ", .. , (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 , ).

+1

Source: https://habr.com/ru/post/1628253/

More articles:


All Articles