linq search multiple columns

Question

I want to search for a string in multiple columns using ling-to-sql and I'm wondering how to write the where clause. This is what I have: I'm passing a list of IDs to search as well as a search term:

public List<long> Seach(string TheSearchTerm, List<long> TheIDs)
{

using (SomeDataContext TheDC = new SomeDataContext())
{
    var TheOutput = (from t in TheDC.SomeTable

                     where TheIDs.Contains(t.ID) &&
                      where "TheSearchTerm is in one of the columns"

                     select t.ID).ToList();
    }
}

How do I write the second where clause the searches for all the columns? I thought of writing a where clause for each column but I'm wondering if there's a better way.

Thanks.

score 9 · Accepted Answer · answered Aug 17 '13 at 19:38

9

var TheOutput = (from t in TheDC.SomeTable

                 where TheIDs.Contains(t.ID) && (
                 t.column1.Contains(TheSearchTerm) ||
                 t.column2.Contains(TheSearchTerm) ||
                 t.column3.Contains(TheSearchTerm) )           
                 select t.ID).ToList();
}

You should only have one where clause and combine checks of all columns with ||.

answered Aug 17 '13 at 19:38

Anders Abel

67,989
17
150
217

Is there anyway to not explicitly include the column names? – frenchie Aug 17 '13 at 19:42
Not with pure linq-to-sql. You could however make a stored procedure that searches all character type columns and then call that SP from the L2S context. – Anders Abel Aug 17 '13 at 19:47
This isn't an ideal search. Lets say you have author and title columns. Steven King, The Shining wouldn't respond to a searchterm "King Shining" but you probably would want it to. – Michael G Jan 23 '21 at 00:54

score 7 · Answer 2 · answered Oct 01 '15 at 22:21

I have a nuget package called SearchExtensions which solves this very issue:

var result = TheDC.SomeTable.Search(t => t.Id).EqualTo(TheIDs)
                            .Search(t => t.Column1, t => t.Column2, t => t.Column3)
                            .Containing(TheSearchTerm)
                            .Select(t => t.ID).ToList();

Alternatively, if you wanted to search against all string properties, you could do the following:

var result = TheDC.SomeTable.Search(t => t.Id).EqualTo(TheIDs)
                            .Search().Containing(TheSearchTerm)
                            .Select(t => t.ID).ToList();

Both of these methods will create Expressions Trees to send the correct instructions to sql server meaning only the matching records are returned from the database.

Amazing! It solved my problem. I was looking for searching from multiple columns with ranking. — habib, Sep 18 '19 at 06:36
Very nice package. Is there a way to search all columns? Like with `table.Search().Containing(Keyword)` but on non string columns too (numbers eventually). — Isu, Oct 03 '22 at 17:22

linq search multiple columns

2 Answers2

Linked