Linq Search more than one column in database table RSS

• • Reply
  

  MangoMM

  Member

  65 Points

  55 Posts

  Linq Search more than one column in database table

  Apr 12, 2012 08:57 PM|LINK

  I have the following code to search for a customer by their name.

  I wish to modify this code to allow a search for a customer by:

  • a.Name
  • a.Email
  • a.CompanyName

  //
          // POST: /CustomerManager/Search?q=smith
          public ActionResult Search(string q)
          {
              q = HttpUtility.HtmlEncode(q);
              if (q == "") return RedirectToAction("Index");
              else
              {
                  var customers = db.Customers.Include("CustomerTypes").Where(a => a.Name.Contains(q) || q == null).Take(10);
                  return View(customers);
              }
          }

  I have tried the following with no luck:

   var customers = db.Customers.Include("CustomerTypes").Where(a => a.Name.Contains(q) || a.Email.Contains(q) || a.CompanyName.Contains(q) || q == null).Take(10);

  
  
  

  contains Linq search

• • Reply
  

  sravanisrav

  Member

  470 Points

  107 Posts

  Re: Linq Search more than one column in database table

  Apr 13, 2012 06:42 AM|LINK

  Hello,

  Here is a sample example to search the database table and fetch the records in grid view using Linq:

  protected void btnSearch_Click(object sender, EventArgs e)
      {
          gvAllAdmins.DataSource = GetFilteredRecords(ddlState.SelectedValue, ddlCity.SelectedValue, ddlRoles.SelectedValue);
          gvAllAdmins.DataBind();
          ViewState["sortDirection"] = SortDirection.Ascending;
      }

   public IEnumerable<Login> GetFilteredRecords(string stateId = "-99",
          string cityId = "-99", string roleId = "-99", string sortExpr = "none", string sortDir = "ASC")
      {
          var qry = dc.Logins.Where(x => x.FKRoleId > 1);
          if (stateId != "-99")
          {
              qry = qry.Where(x => x.FKStateID == Convert.ToInt32(stateId));
          }
          if (cityId != "-99")
          {
              qry = qry.Where(x => x.FKCityID == Convert.ToInt32(cityId));
          }
          if (roleId != "-99")
          {
              qry = qry.Where(x => x.FKRoleId == Convert.ToInt32(roleId));
          }
          if (sortExpr != "none")
          {
              if (sortDir == "ASC")
              {
                  switch (sortExpr)
                  {
                      case "Name": qry = qry.OrderBy(x => x.Name);
                          break;
                      case "emailId": qry = qry.OrderBy(x => x.emailId);
                          break;
                      case "FKStateID": qry = qry.OrderBy(x => x.FKStateID);
                          break;
                      case "IsBlocked": qry = qry.OrderBy(x => x.IsBlocked);
                          break;
                      default:
                          break;
                  }
              }
              else if (sortDir == "DESC")
              {
                  switch (sortExpr)
                  {
                      case "Name": qry = qry.OrderByDescending(x => x.Name);
                          break;
                      case "emailId": qry = qry.OrderByDescending(x => x.emailId);
                          break;
                      case "FKStateID": qry = qry.OrderByDescending(x => x.FKStateID);
                          break;
                      case "IsBlocked": qry = qry.OrderByDescending(x => x.IsBlocked);
                          break;
                      default:
                          break;
                  }
              }
          }
          return qry;
      }
  For more information on Linq follow the below link:
  http://www.bestdotnettraining.com/Online/Training/LINQ/LINQ-to-SQL/109 

  viewstate contains Linq sorting search

  Mark as answer,if my post helps you..
  
  Thanks & Regards
  Sravani
  
  
• • Reply
  

  mm10

  Contributor

  6409 Points

  1184 Posts

  Re: Linq Search more than one column in database table

  Apr 13, 2012 09:15 AM|LINK

  Try this, it searches in all columns (Name, Email, CompanyName) for the string q and all records if q is null or empty:

  
  q = q.ToLower();
   var customers = db.Customers.Include("CustomerTypes").Where(a => a.Name.ToLower().Contains(q) || a.Email.ToLower().Contains(q) || a.CompanyName.ToLower().Contains(q) || string.IsNullOrEmpty(q)).Take(10);

  Please 'Mark as Answer' if this post helped you.
  • Marked as answer by MangoMM on Apr 13, 2012 12:05 PM
• • Reply
  

  MangoMM

  Member

  65 Points

  55 Posts

  Re: Linq Search more than one column in database table

  Apr 13, 2012 12:05 PM|LINK

  thanks for your reply sravanisrav, but I don't know what you are doing here. I don't know how your example helps me.

  • Edited by MangoMM on Apr 13, 2012 12:07 PM
  • Edited by MangoMM on Apr 13, 2012 12:08 PM
  • Edited by MangoMM on Apr 13, 2012 12:08 PM
• • Reply
  

  MangoMM

  Member

  65 Points

  55 Posts

  Re: Linq Search more than one column in database table

  Apr 13, 2012 12:07 PM|LINK

  mm10

  Try this, it searches in all columns (Name, Email, CompanyName) for the string q and all records if q is null or empty:

  
  q = q.ToLower();
   var customers = db.Customers.Include("CustomerTypes").Where(a => a.Name.ToLower().Contains(q) || a.Email.ToLower().Contains(q) || a.CompanyName.ToLower().Contains(q) || string.IsNullOrEmpty(q)).Take(10);

  Thanks, this works, but I don't know why it makes a difference using .ToLower??? surely this just makes all the query text and the searchable text lower case? if all my query is in the correct case, my original query should have worked anyway?

• • Reply
  

  mm10

  Contributor

  6409 Points

  1184 Posts

  Re: Linq Search more than one column in database table

  Apr 13, 2012 12:08 PM|LINK

  I compare the lower case values for the properties of a customer with the lower case value of the search string which means the search is not case sensitive.

  Please 'Mark as Answer' if this post helped you.
• • Reply
  

  MangoMM

  Member

  65 Points

  55 Posts

  Re: Linq Search more than one column in database table

  Apr 13, 2012 12:11 PM|LINK

  Thanks again MM10.