CODEWITHSUNDEEP
c#linq-to-sql
Haroon
Haroon

Reputation: 3472

Linq to sql querying multiple columns - query help

I have a text field on my screen where users can search for a few things from one input field:

  1. Address
  2. Postcode
  3. Client name
  4. ID

What is the best way to write a query that will query these columns?

How should I take the input in, should I split string on space? or comma?

User input = 67 pottors lane 99
where "67 pottors lane" is address
"99" is/could be ID

What I have so far:

//q = querystring
//filteredlist = extension method for the table that I am querying
//clientlist = list of clients I have passed in as a param

//Here is my query for 

if (!string.IsNullOrEmpty(q))
{
  var result = q.Trim();
   filteredlist = filteredlist
   .Where(x => x.ID.Contains(q) || x.SubjectPropertyAddress1.Contains(q)
   || x.SubjectPropertyPostCode.Contains(q)
   || clientlist.Any(y=> x.ClientID == y.ClientID && (y.ID.Contains(q) || y.Name.Contains(q))  ));
}

NOTE: I will make use of indexing using sql.

Upvotes: 0

Views: 1156

Answers (3)

Pleun
Pleun

Reputation: 8920

Perhaps an idea: make an extra column in your database with all the columns you want to search for as one big concattenated string. Then do a free text search of your input string versus that field in the DB.

Since L2SQL does not support free text search, you need to do it in stored procedure in that case or do like here (http://stackoverflow.com/questions/67706/linqtosql-and-full-text-search-can-it-be-done)

Upvotes: 1

Simon Williams
Simon Williams

Reputation: 1046

I have done a very similar thing in the past. I split the search string on the space character and restricted it to a maximum of 6 search "words", so that the linq is still manageable.

I came up with something like the following:

string[] words = q.ToLower().Split(' ');
string[] wordsFixed = new string[] {"", "", "", "", "", "" };

for(int i = 0; i < 6 && i < words.Length; i++)
    wordsFixed[i] = words[i];

    var data = from item in list
                    where (item.ID + item.Name + item.Address1 + item.Address2 + item.PostCode).ToLower().Contains(wordsFixed[0]) &&
                          (item.ID + item.Name + item.Address1 + item.Address2 + item.PostCode).ToLower().Contains(wordsFixed[1]) &&
                          (item.ID + item.Name + item.Address1 + item.Address2 + item.PostCode).ToLower().Contains(wordsFixed[2]) &&
                          (item.ID + item.Name + item.Address1 + item.Address2 + item.PostCode).ToLower().Contains(wordsFixed[3]) &&
                          (item.ID + item.Name + item.Address1 + item.Address2 + item.PostCode).ToLower().Contains(wordsFixed[4]) &&
                          (item.ID + item.Name + item.Address1 + item.Address2 + item.PostCode).ToLower().Contains(wordsFixed[5])
                    select item;

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

Ouch - is there no way for you to split the input into separate fields for ID, Address, Postcode and Name?

If so, you can keep appending Where clauses, like

var query = filteredList;
if (!String.IsNullOrEmpty(id))
{
    query = query.Where(c => c.ID.Contains(id))
}
if (!String.IsNullOrEmpty(name))
{
    query = query.Where(c => c.Name.Contains(name))
}

.. same for Name, address etc

Otherwise, your query resembles more of a search engine query than a RDBMS query.

Upvotes: 0

Related Questions