LINQ Question (Contains)

[Ralf Rottmann \(www.24100.net\)]

I recently stumbled across a pretty interesting LINQ to SQL question and
wonder, whether anybody might have an answer. (I'm doing quite some
increasing LINQ evangelism down here in Germany.).

Assume I want to select rows from a database and check whether a specific
column contains keywords from a list of keywords. The following works just
fine:

List<string> searchTerms = new List<string>() { "Maria",
"Pedro" };

var query = from c in db.Customers
where searchTerms.Contains(c.ContactName)
select c;

dataGridView1.DataSource = query;

The problem with this code is, that c.ContactName has to match exactly
"Maria" or "Pedro". It does not include substring search, so given a
ContactName might be "Maria Foo" or "Pedro Bar" it does not return those
rows. Any idea as to how to achieve this without iterating through query in
a foreach loop?

Sidenote: Visual Studio 2008 IntelliSense indicates that there is a
Contains<> overload which accepts an IEqualityComparer type.
However, the following code compiles fine but throws a runtime exception:

SubstringComparer substringComparer = new SubstringComparer();

List<string> searchTerms = new List<string>() { "Maria",
"Pedro" };

var query = from c in db.Customers
where searchTerms.Contains<string>(c.ContactName,
substringComparer)
select c;

Any idea how to declare a constraint which returns rows which contain
keywords contained within a collection?

 

[Jon Skeet [C# MVP]]

I recently stumbled across a pretty interesting LINQ to SQL question and
wonder, whether anybody might have an answer. (I'm doing quite some
increasing LINQ evangelism down here in Germany.).

I don't have the the answer, I'm afraid, but does SQL support what you
want to do in the first place? What would you like the generated SQL to
be?

 

[Ralf Rottmann \(www.24100.net\)]

Jon,

Interesting question and I gotta admit, I asked myself the same question:
Can I do elegantly it in SQL? Well, in SQL I could combine multiple WHERE
clauses by OR. I wonder whether LINQ supports antyhing like that...

 

[Jon Skeet [C# MVP]]

Interesting question and I gotta admit, I asked myself the same question:
Can I do elegantly it in SQL? Well, in SQL I could combine multiple WHERE
clauses by OR. I wonder whether LINQ supports antyhing like that...

Yes, I suspect that if you do something like

var query = from cust in db.Customers
where cust.Name.Contains("Fred")
|| cust.Name.Contains("Ginger")
select cust;

it should work fine. (I haven't tested it, I have to say...)

 

[Nicholas Paldino [.NET/C# MVP]]

This should work, but I get the feeling that the list is dynamically
populated, so you wouldn't be able to expand the query out in this
situation.

If the list is dynamically created, the OP might be able to do something
like this:

// The initial query.
var query = from cust in db.Customers;

// Cycle through the items in the list.
for each (string searchTerm in searchTerms)
{
// Add the new search term to the query.
query = query.Where(customer => customer.Name.Contains(searchTerm));
}

This should build up the query and send it to SQL Server, I don't know
how much LINQ to SQL will beautify it. It ^should^ execute completely on
the database though.

 

[Jon Skeet [C# MVP]]

This should work, but I get the feeling that the list is dynamically
populated, so you wouldn't be able to expand the query out in this
situation.

If the list is dynamically created, the OP might be able to do something
like this:

// The initial query.
var query = from cust in db.Customers;

// Cycle through the items in the list.
for each (string searchTerm in searchTerms)
{
// Add the new search term to the query.
query = query.Where(customer => customer.Name.Contains(searchTerm));
}

This should build up the query and send it to SQL Server, I don't know
how much LINQ to SQL will beautify it. It ^should^ execute completely on
the database though.

That will generate a logical "and" though, instead of a logical "or". I
suspect that to get LINQ to SQL to do this, you'd actually need to
build the expression tree semi-manually. (You could probably use a
lambda expression for the method call and property access, and just
manually do the "OR" part - I'd have to try it to see.)

 

[Jon Skeet [C# MVP]]

That will generate a logical "and" though, instead of a logical "or". I
suspect that to get LINQ to SQL to do this, you'd actually need to
build the expression tree semi-manually. (You could probably use a
lambda expression for the method call and property access, and just
manually do the "OR" part - I'd have to try it to see.)

Hmm. It's actually reasonably - either that or I'm incompetent, which
isn't out of the question. I've got something which gets as far as
trying to evaluate the query, but then falls over. I'll keep trying for
a little while...

 

[Ralf Rottmann \(www.24100.net\)]

Guy, I'm glad that we're trying together. Honestly, I'm a huge fan of LINQ
to SQL and most of the language enhancements, however, I wonder if an almost
basic requirement as this one has not been addressed by LINQ, I'd wonder...
Let's keep trying to find the solution!

 

[Ralf Rottmann \(www.24100.net\)]

Here is my current solution:

List<string> searchTerms = new List<string>() { "Maria", "Pedro" };
List<Customer> results = new List<Customer>();

foreach (string searchTerm in searchTerms)
{
var query = from c in db.Customers
where c.ContactName.Contains(searchTerm)
select c;
results.AddRange(query);
}

What do you think?

 

[Jon Skeet [C# MVP]]

Here is my current solution:

List<string> searchTerms = new List<string>() { "Maria", "Pedro" };
List<Customer> results = new List<Customer>();

foreach (string searchTerm in searchTerms)
{
var query = from c in db.Customers
where c.ContactName.Contains(searchTerm)
select c;
results.AddRange(query);
}

What do you think?

Well, it will *work* - but it's not going to perform terribly well by
the time you've got lots of search terms.

This can certainly be done with a bit of work, but I was hoping to use
the C# compiler's built in expression tree support. Hmm.

 

[Jon Skeet [C# MVP]]

Guy, I'm glad that we're trying together. Honestly, I'm a huge fan of LINQ
to SQL and most of the language enhancements, however, I wonder if an almost
basic requirement as this one has not been addressed by LINQ, I'd wonder...
Let's keep trying to find the solution!

Okay, I've found it - but it's a bit ugly. Here's some code working
against Northwind:

static void Main(string[] args)
{
var list = new List<string> { "ille", "on" };

// Just to make things easier, start with "false"
Expression query = Expression.Constant(false);
ParameterExpression parameter = Expression.Parameter
(typeof(Customer), "cust");

foreach (string entry in list)
{
string copy = entry;
Expression city = Expression.Property(parameter, "City");
Expression contains = Expression.Call(city, "Contains",
null, Expression.Constant(copy));

query = Expression.OrElse(query, contains);
}

Expression<Func<Customer,bool>> where =
Expression.Lambda<Func<Customer,bool>>(query, parameter);

using (var db = new NorthwindDataContext())
{
db.Log = Console.Out;

var sqlQuery = db.Customers.Where(where);

foreach (Customer cust in sqlQuery)
{
Console.WriteLine("{0} {1}", cust.CompanyName, cust.City);
}
}
}

The hard bit using lambda expressions is trying to explain to the
system that it should use the same parameter for everything. I'm sure
it's doable somehow, I just don't know how yet. Expression.Invoke *may*
hold the key...

 

[Ralf Rottmann \(www.24100.net\)]

You're absolutely right... it's going to submit a single SQL command to the
DB for every item in the searchTerms collection... not that good... (and
unfortunately a string concatenated SQL command would do better here...)

So, let's elaborate further... (it's still fun for me).

 

[Nicholas Paldino [.NET/C# MVP]]

Ralf,

While I appreciate the desire to use LINQ in this situation, it would
seem that it would be easier to pass the list to SQL Server (through a
delimited string, most likely) and have a CLR routine which will return a
result set based on the delimited string.

Then, you can use the exists statement to check to see if the value is
like the values passed in.

For example, say you had those items, and you know that the pipe
character ("|") was not an allowed character in your search terms, you could
put together a string like so:

Maria|Pedro

And pass that to a stored procedure. Assuming you have a CLR
table-based function (in this case, lets assume it is xfn_Parse(@values as
nvarchar(max))), you could do this:

create procedure xsp_SearchCustomers
(
@searchTerms nvarchar(max)
)
as
--- Set no count on.
set nocount on

--- Perform the select.
select distinct
c.*
from
customers as c
inner join xfn_Parse(@searchTerms) as t on 1 = 1
where
--- Assume value is the parsed value, like "Maria" or "Pedro"
c.ContactName like (N'%' + t.Value + N'%')

You could use the exists statement as well, or use a CTE in order to
make sure that xfn_Parse is not called multiple times to produce a
table-valued result.

Regardless, it's a much simpler solution than what is offered in LINQ to
SQL. While I like LINQ to SQL, it should not be seen as a panacea for all
DB-related issues. Specifically, in specialized situations like this, using
SQL Server to use the work is easier, possibly more performant (you can
tinker with the query to get different performance profiles based on your
indexes and statistics) and IMO, more maintainable.

 

[Ralf Rottmann \(www.24100.net\)]

I finally found a pretty good solution and documented it here:

http://www.talentgrouplabs.com/blog...inq-queries--dynamic-where-clause-part-1.aspx

What do you think?

 

[Jon Skeet [C# MVP]]

I finally found a pretty good solution and documented it here:

http://www.talentgrouplabs.com/blog/archive/2007/11/25/dynamic-linq-q
ueries--dynamic-where-clause-part-1.aspx

What do you think?

Hmm... I worry a little bit about how this will scale over time. I
don't know the details of generated assemblies, but I wonder whether
you'll get a new assembly every time you run a query. Maybe not - I
really haven't looked at the details.

I'm sure there's a way of getting the expression tree stuff to work,
but it's still eluding me at the moment

 

[Ralf Rottmann \(www.24100.net\)]

It's actually not about a generated assembly. It's all static assemblies
that compile-once. The extension methods defined in Dynamic.cs parse an
expression provided as a simple string into a LINQ expression tree at
runtime.

So all they do is take away the burden of us having to do it ourselves.

I assume it scales just fine.

I'm still not happy as obviously this takes us a bit back into not so type
safe SQL String Command creation times... however, I'm still struggling with
fully understanding how to manually create an expression tree with these
dynamics myself.

Hmm... I need a "functional enlightment"...

 

[Jon Skeet [C# MVP]]

It's actually not about a generated assembly. It's all static assemblies
that compile-once. The extension methods defined in Dynamic.cs parse an
expression provided as a simple string into a LINQ expression tree at
runtime.

So all they do is take away the burden of us having to do it ourselves.

I assume it scales just fine.

I'm still not happy as obviously this takes us a bit back into not so type
safe SQL String Command creation times... however, I'm still struggling with
fully understanding how to manually create an expression tree with these
dynamics myself.

Did you take a look at the post I made which built the expression tree
without any lambda expressions? It's not the nicest piece of code in
the world, but it works and you don't need to worry about safe strings
etc.

Hmm... I need a "functional enlightment"...

To be honest, I think this is one of those cases where LINQ to SQL
just isn't going to do everything you want it to. You may well find
that eSQL for ADO.NET Entities works better in this respect when it
comes out.

One alternative is to only support a maximum number of search terms
like this - 10, say. Then you could use 11 "regular" LINQ query
expressions, one with 0 parameters, one with 1, one with 2 etc. I'm not
saying it's pleasant, but it's reasonably practical.

 

[Ralf Rottmann \(www.24100.net\)]

Jon,

Could you point me to the post you're refering to?

-Ralf

 

[Ralf Rottmann \(www.24100.net\)]

And here is the final "correct" and clean way of doing it in LINQ (as far as
I believe):

First I implemented a class PredicateExtensions:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;

namespace PlayingWithLinq
{
public static class PredicateExtensions
{
public static Expression<Func<T, bool>> True<T>() { return f =>
true; }
public static Expression<Func<T, bool>> False<T>() { return f =>
false; }

public static Expression<Func<T, bool>> Or<T>(this
Expression<Func<T, bool>> expr1,
Expression<Func<T,
bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2,
expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.Or(expr1.Body, invokedExpr),
expr1.Parameters);
}

public static Expression<Func<T, bool>> And<T>(this
Expression<Func<T, bool>> expr1,
Expression<Func<T,
bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2,
expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.And(expr1.Body, invokedExpr),
expr1.Parameters);
}
}
}

It primarily adds a set of Extension Methods (True, False, Or, And) to any
generic expression.

In the main code I can then do the following:

NorthwindDataContext db = new NorthwindDataContext();

string[] searchTerms = new string[] { "Maria", "Pedro" };

var predicate = PredicateExtensions.False<Customer>();

foreach (string searchTerm in searchTerms)
{
string temp = searchTerm;
predicate = predicate.Or(c=>c.ContactName.Contains(temp));
}

dataGridView1.DataSource = db.Customers.Where(predicate);

 

[Jon Skeet [C# MVP]]

Could you point me to the post you're refering to?

Nov 27, 8:27pm, message ID <MPG.
(e-mail address removed)>

It starts with:

"Okay, I've found it - but it's a bit ugly. Here's some code working
against Northwind:"

Jon