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

Question

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

Frédéric Hamidi · Accepted Answer · 2010-12-06 17:18:49Z

4

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();

answered Dec 6, 2010 at 17:18

Frédéric Hamidi

264k42 gold badges497 silver badges486 bronze badges

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

1 Comment

VVS Over a year ago

Right, you don't want to capture the loop variable.

Marcelo Cantos · Accepted Answer · 2010-12-06 17:17:13Z

2

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.

answered Dec 6, 2010 at 17:17

Marcelo Cantos

187k40 gold badges338 silver badges366 bronze badges

Comments

Brownman98 · Accepted Answer · 2010-12-06 17:24:54Z

1

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


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

answered Dec 6, 2010 at 17:24

Brownman98

1,0651 gold badge7 silver badges17 bronze badges

Comments

nixon · Accepted Answer · 2010-12-06 17:18:25Z

0

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

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

answered Dec 6, 2010 at 17:18

nixon

1,9725 gold badges21 silver badges45 bronze badges

1 Comment

Marcelo Cantos Over a year ago

If, by "the above", you mean @Frédéric's or my answer (answers with the same vote are ordered randomly), then no, they aren't wrong. Each pass of the loop will replace the customers LINQ expression with a new expression that applies a Where clause to the original expression. It is a fairly stock-standard technique for building up complex queries.

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

The answer:

The explanation:

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

The answer:

The explanation:

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related