By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,361 Members | 1,700 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,361 IT Pros & Developers. It's quick & easy.

LINQ Question (Contains)

P: n/a
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<stringsearchTerms = 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<stringsearchTerms = 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?
--

-------------------------------
http://www.24100.net

Nov 25 '07 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Ralf Rottmann (www.24100.net) <Be*****@live.comwrote:
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?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #2

P: n/a
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]" <sk***@pobox.comwrote in message
news:MP*********************@msnews.microsoft.com. ..
Ralf Rottmann (www.24100.net) <Be*****@live.comwrote:
>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?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #3

P: n/a
Ralf Rottmann (www.24100.net) <Be*****@live.comwrote:
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...)

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #4

P: n/a
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP*********************@msnews.microsoft.com. ..
Ralf Rottmann (www.24100.net) <Be*****@live.comwrote:
>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...)

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #5

P: n/a
Nicholas Paldino [.NET/C# MVP] <mv*@spam.guard.caspershouse.comwrote:
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 - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #6

P: n/a
Jon Skeet [C# MVP] <sk***@pobox.comwrote:
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.)
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...

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #7

P: n/a
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!
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP*********************@msnews.microsoft.com. ..
Jon Skeet [C# MVP] <sk***@pobox.comwrote:
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.)

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

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #8

P: n/a
Here is my current solution:

List<stringsearchTerms = new List<string>() { "Maria", "Pedro" };
List<Customerresults = 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?

"Ralf Rottmann (www.24100.net)" <Be*****@live.comwrote in message
news:B4**********************************@microsof t.com...
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<stringsearchTerms = 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<stringsearchTerms = 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?
--

-------------------------------
http://www.24100.net
Nov 25 '07 #9

P: n/a
Ralf Rottmann (www.24100.net) <Be*****@live.comwrote:
Here is my current solution:

List<stringsearchTerms = new List<string>() { "Maria", "Pedro" };
List<Customerresults = 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 - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #10

P: n/a
Ralf Rottmann (www.24100.net) <Be*****@live.comwrote:
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...

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #11

P: n/a
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).
"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP********************@msnews.microsoft.com.. .
Ralf Rottmann (www.24100.net) <Be*****@live.comwrote:
>Here is my current solution:

List<stringsearchTerms = new List<string>() { "Maria", "Pedro" };
List<Customerresults = 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 - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 25 '07 #12

P: n/a
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);


"Ralf Rottmann (www.24100.net)" <Be*****@live.comwrote in message
news:B4**********************************@microsof t.com...
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<stringsearchTerms = 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<stringsearchTerms = 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?
--

-------------------------------
http://www.24100.net
Nov 26 '07 #13

P: n/a
On Nov 26, 7:38 am, "Ralf Rottmann \(www.24100.net\)"
<BeSh...@live.comwrote:
Could you point me to the post you're refering to?
Nov 27, 8:27pm, message ID <MPG.
21*****************@msnews.microsoft.com>

It starts with:

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

Jon
Nov 26 '07 #14

P: n/a
Ralf Rottmann (www.24100.net) <Be*****@live.comwrote:
And here is the final "correct" and clean way of doing it in LINQ (as far as
I believe):
<snip>

Looks good to me - it's the kind of thing I'd been trying to do,
although I hadn't used the cast. You might want to consider using
OrElse instead of Or if you want to match || behaviour of C#.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
Nov 26 '07 #15

This discussion thread is closed

Replies have been disabled for this discussion.