CODEWITHSUNDEEP
c#linqlinq-to-sql
frenchie
frenchie

Reputation: 51927

linq search multiple columns

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.

Upvotes: 5

Views: 15400

Answers (2)

NinjaNye
NinjaNye

Reputation: 7126

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.

Upvotes: 7

Anders Abel
Anders Abel

Reputation: 69260

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 ||.

Upvotes: 9

Related Questions