Linq-to-SQL statement issue

Question

I am basically looking to bind a search query to a gridview which is nice, but this must be done by a users input query (sort of like a search function). I can get single values and rows returned, but how would I get it to search all columns in my database for the inputted values and return it?

My code so far is:

Void SearchFunction()
{
  TiamoDataContext context = new TiamoDataContext();

  var search from p in context.UserProfiles
      where p.DanceType == UserSearchString
      select p;

  UserSearchGrid.DataSource = search;
  UserSearchGrid.DataBind();
}

I tried p.equals but am pretty sure thats not the way to go about it.

Answer 1

It looks like your query sytax is a little off. It should look like:

var search = from p in context.UserProfiles
             where p.DanceType == UserSearchString
             select p;

Since you're trying to query multiple columns, you're going to have to chain the names of the columns you're looking at with or's:

var search = from p in context.UserProfiles
             where p.DanceType == UserSearchString ||
                 p.Name == UserSearchString ||
                 p.SomeOtherStringColumn == UserSearchString
             select p;

Or if you want to use the Lambda syntax:

var search = context.UserProfiles.Where(p => p.DanceType == UserSearchString ||
                 p.Name == UserSearchString ||
                 p.SomeOtherStringColumn == UserSearchString);

Keep in mind that using either of these methods implies that p.DanceType is a string type.

Answer 2

whatever solution you find, do remember to protect against evil users!! do not use the input as is, encode it or do whatever validation necessary!!

Answer 3

If you want it to search every column in the table, then you have to tell it to search every column in the table.

var search =
    from p in context.UserProfiles
    where 
        p.DanceType == UserSearchString ||
        p.Foo == UserSearchString ||
        p.Bar == UserSearchString
    select p;

That's all there is to it. There's no magic operator that will do it automatically (there can't be - some columns might not even be strings).

Keep in mind that this will likely be very slow, as the query optimizer won't be able to pick a single index that can handle this entire query.

As an aside, this "search" only tests for pure equality. You might want to use StartsWith or Contains for a prefix or substring search, respectively.