Linq to sql querying multiple columns - query help

Question

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

Address
Postcode
Client name
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.

Pleun · Accepted Answer · 2011-09-09 11:02:00Z

1

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)

answered Sep 9, 2011 at 11:02

Pleun

8,9102 gold badges32 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Haroon Over a year ago

sounds interesting, I would have to look into this, only problem is though, the only small problem is I would always need to update the queried data. I heard of Lucefene, but not sure if that was a possibility? I am eventually going down the sproc route anyway

Pleun Over a year ago

you can use a an update trigger to update the concat columns automatically

StuartLC · Accepted Answer · 2011-09-09 09:00:30Z

0

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.

answered Sep 9, 2011 at 9:00

StuartLC

108k18 gold badges225 silver badges294 bronze badges

2 Comments

Haroon Over a year ago

lol - I could split but it will ruin my UI big time, I would rather not if I can, I am doing what you describe above + the query I have listed in my question. My query is a get request than can have multiple params, e.g. status, or type - in these instances I do exactly what you have said above, I just want my big search box to filter on address/postcode/id/companyname if I can...

Haroon Over a year ago

How bad will my query above bite me? Is it really that bad?

Simon Williams · Accepted Answer · 2011-09-09 09:28:45Z

0

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;

answered Sep 9, 2011 at 9:28

Simon Williams

1,0663 gold badges13 silver badges27 bronze badges

1 Comment

Simon Williams Over a year ago

What you have to be aware of, if this is linq to sql, is that the query needs to be translateable to sql. e.g. string.Format won't work.

Collectives™ on Stack Overflow

Linq to sql querying multiple columns - query help

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related