Filtered Query for a Context Object Using Linq to SQL

Question

I tried to find some examples of my approach, but all the questions were not close enough to what I was trying to achieve.

for TL; DR, the question is: how to make it work like in a regular sql query?

using C # - Winforms with SqlCompact4 and Linq to SQL

my script includes a form with all the relevant columns of the Db table as available filters for the query

and then in the event of changing the text of each filter filterbox as a filter, the data source for the gridview is updated accordingly and because I allow filtered search in many of them, I tried to avoid using some additional lines of code.

so let's say that we focus only on 4 columns

custID, name, email address, cellPhone

Each has its own corresponding TextBox. I am trying to make a request as follows:

first, I systematically collect the entire text field into a list

var AllFormsSearchFiltersTBXLst = new List<TextBox>();

which collects all tbx in the current form

var AllFormsSearchFiltersTBXLst = [currentFormHere].Controls.OfType<TextBox>();

so now I have all the text fields as filters, regardless of whether they have any value then check who has any value in it.

forech textbox in this filter text box if the length of the text is greater than zero

this means that the current filter is active

then .. the second list of AllFormsACTIVESearchfiltersTBXLst will contain only active filters

What I was trying to achieve was in the same way that I did not need to specify each of the objects in the text field, I just sorted through each of them as a collection and did not have to specify each id through it

dbContext,

, tbxName

query = db.Where(db=>db.email.Contains(TbxEmail.Text));

10-15

, , , , .

 using (SqlCeConnection ClientsConn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Conn_DB_RCL_CRM2014"].ConnectionString))
        {
            System.Data.Linq.Table<ContactsClients> db = null;
            // get all column names from context 
            var x =(System.Reflection.MemberInfo[]) typeof(ContactsClients).GetProperties();

            using (DB_RCL_CRM2014Context Context = new DB_RCL_CRM2014Context(ClientsConn))
            {
                if (!Filtered)
                    db = Context.ContactsClients;//.Where(client => client.Name.Contains("fler"));
                else
                {
                    db = Context.ContactsClients;
                    // filters Dictionary contains the name of textBox and its value 
                    // I've named TBX as Columns names specially so i could equalize it to the columns names when needed to automate 
                    foreach (KeyValuePair<string,string> CurFltrKVP in FiltersDict)
                    {
                        foreach (var memberInfo in x)
                        {
                         // couldn't find out how to build the query 
                        }

                    }


                }

                BindingSource BS_Clients = new BindingSource();
                BS_Clients.DataSource = db;
                GV_ClientInfo_Search.DataSource = BS_Clients;

, sql,

foreach

var q = "where";

   foreach(tbx CurTBX in ALLFILTERTBX)
   {
    q +=CurTBX.Name +" LIKE '%"  + CurTBX.Text + "%'";
    // and some checking  of last element in list off cores 
   }

... sql-?

Answer 1

, db , : db.email ( 'email'). , -. switch , : email, name .. - :

// Create a list for the results
var results = new List<YourDBResultTypeHere>();

foreach(tbx CurTBX in ALLFILTERTBX)
{
   switch(CurTBX.Name) {
      case "email":
         results.AddRange(db.Where(db => db.email.Contains(tbx.Text)).ToList());
         break;
      case "name":
         results.AddRange(db.Where(db => db.name.Contains(tbx.Text)).ToList());
         break;
   }
}

Answer 2

    void UpdateGridViewData(bool Filtered=false, Dictionary<string,string> FiltersDict = null)
    {

        using (SqlCeConnection ClientsConn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Conn_DB_RCL_CRM2014"].ConnectionString))
        {
            System.Data.Linq.Table<ContactsClients> db = null;
            IEnumerable<ContactsClients> IDB = null;
            BindingSource BS_Clients = new BindingSource();
            System.Reflection.MemberInfo[] AllDbTblClientsColumns = (System.Reflection.MemberInfo[])typeof(ContactsClients).GetProperties();

            using (DB_RCL_CRM2014Context Context = new DB_RCL_CRM2014Context(ClientsConn))
            {
                if (!Filtered)
                {
                    db = Context.ContactsClients;
                    BS_Clients.DataSource = db;
                }
                else
                {
                    string fltr = "";
                    var and = "";
                    if (FiltersDict.Count > 1) and = "AND";
                    for (int i = 0; i < FiltersDict.Count; i++)
                    {
                        KeyValuePair<string, string> CurFltrKVP = FiltersDict.ElementAt(i);
                        if (i >= FiltersDict.Count-1) and = "";
                        for (int j = 0; j < AllDbTblClientsColumns.Length; j++)
                        {

                            if (AllDbTblClientsColumns[j].Name.Equals(CurFltrKVP.Key))
                            {

                                fltr += string.Format("{0} Like '%{1}%' {2} ", AllDbTblClientsColumns[j].Name, CurFltrKVP.Value, and);


                            }

                        }

                    }
                    try
                    {
                        IDB = Context.ExecuteQuery<ContactsClients>(
                          "SELECT * " +
                          "FROM ContactsCosmeticsClients " +
                          "WHERE " + fltr

                        );
                        BS_Clients.DataSource = IDB;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

                }



                GV_ClientInfo_Search.DataSource = BS_Clients;

            }
        }
    }