CODEWITHSUNDEEP
c#linq-to-sql
David
David

Reputation: 1873

Using Linq To SQL to search for multiple strings in a field

I'm trying to use Linq to SQL to search a text column for multiple matching words, but the query is not doing what I expect.

Here is my sample code:

            string[] nameSearch = new string[2];
            nameSearch[0] = "John";
            nameSearch[1] = "Doe";

            var customers = context.Customers.AsQueryable();
            foreach (string name in nameSearch)
            {
                customers = customers.Where(r => r.CustName.Contains(name));
            }
            Data.Customer[] results = customers.ToArray();

The query that runs is:

    SELECT [t0].[CustName]
    FROM [dbo].[Customer] AS [t0]
    WHERE ([t0].[CustName] LIKE @p0) AND ([t0].[CustName] LIKE @p1)
    -- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [%Doe%]
    -- @p1: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [%Doe%]

How do I fix this query? (It's suppose to be searching for John and Doe, not Doe and Doe.)

Upvotes: 1

Views: 3182

Answers (4)

Brownman98
Brownman98

Reputation: 1065

string[] nameSearch = new string[2];
nameSearch[0] = "John";
nameSearch[1] = "Doe";


var result = customers.Where(r => nameSearch.Contains(r.CustName));

Upvotes: 1

Frédéric Hamidi
Frédéric Hamidi

Reputation: 262919

Your lambda expression is only evaluated when the query runs, i.e. during the call to ToArray(). By then, name is bound to the last item in your array.

You can use a local variable in your foreach loop in order to avoid referencing the outer name variable in your lambda expression:

var customers = context.Customers.AsQueryable();
for (string name in nameSearch) {
    string curName = name;
    customers = customers.Where(r => r.CustName.Contains(curName));
}
Data.Customer[] results = customers.ToArray();

Upvotes: 4

nixon
nixon

Reputation: 1972

No the above is wrong, it will overwrite customers every time !

customers = customers.Where(r => r.CustName.Contains("John") && r.CustName.Contains("Doe"));

Upvotes: 0

Marcelo Cantos
Marcelo Cantos

Reputation: 185842

The answer:

foreach (string name in nameSearch)
{
    string name_ = name;
    customers = customers.Where(r => r.CustName.Contains(name_));
}

The explanation:

The lambda function r => r.CustName.Contains(name) captures the name variable. This same variable is in scope for both iterations. Using a block-scope temporary breaks this link, so that each invocation of the lambda function picks up a different variable with a distinct value.

Upvotes: 2

Related Questions