LINQ Question (Contains)

[BeSharp]

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?

 

[Steven Cheng[MSFT]]

Hi BeSharp,

As for the D LINQ selection scenario you mentioned, I think it sounds like
what you want to do is a SQL "LIKE" comparison. For {"...",
"..."}.contains, "contains" actually means that the input parameter exactly
match any of the i tem in collection, that is not substring mapping. For
substring mapping, you should use SQL "Like" style selection. Here is a web
thread discussing on using "LIKE" query in LINQ:

#using LIKE in DLinq
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=308989&SiteID=1

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.





--------------------

 

[Steven Cheng[MSFT]]

Hi BeSharp,

Does the info in my last reply helps some or have you got any further
progress?


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

From: (e-mail address removed) (Steven Cheng[MSFT])
Organization: Microsoft
Date: Mon, 26 Nov 2007 06:17:43 GMT
Subject: RE: LINQ Question (Contains)

Hi BeSharp,

As for the D LINQ selection scenario you mentioned, I think it sounds like
what you want to do is a SQL "LIKE" comparison. For {"...",
"..."}.contains, "contains" actually means that the input parameter exactly
match any of the i tem in collection, that is not substring mapping. For
substring mapping, you should use SQL "Like" style selection. Here is a web
thread discussing on using "LIKE" query in LINQ:

#using LIKE in DLinq
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=308989&SiteID=1

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#noti f
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.





--------------------

 

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

Steven,

Sorry for not responding... I blogged about my solution here:

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

Hi BeSharp,

Does the info in my last reply helps some or have you got any further
progress?


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------

From: (e-mail address removed) (Steven Cheng[MSFT])
Organization: Microsoft
Date: Mon, 26 Nov 2007 06:17:43 GMT
Subject: RE: LINQ Question (Contains)

Hi BeSharp,

As for the D LINQ selection scenario you mentioned, I think it sounds like
what you want to do is a SQL "LIKE" comparison. For {"...",
"..."}.contains, "contains" actually means that the input parameter exactly
match any of the i tem in collection, that is not substring mapping. For
substring mapping, you should use SQL "Like" style selection. Here is a web
thread discussing on using "LIKE" query in LINQ:

#using LIKE in DLinq
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=308989&SiteID=1

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#noti f
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no
rights.





--------------------

From: "BeSharp" <[email protected]>
Subject: LINQ Question (Contains)
Date: Sun, 25 Nov 2007 13:28:55 +0100

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?

 

[Steven Cheng[MSFT]]

Thanks for your followup and sharing the info with us.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

From: "Ralf Rottmann \(www.24100.net\)" <[email protected]>
References: <[email protected]>

Subject: Re: LINQ Question (Contains)
Date: Wed, 28 Nov 2007 21:29:35 +0100

Steven,

Sorry for not responding... I blogged about my solution here:

http://www.talentgrouplabs.com/blog/archive/2007/11/26/dynamic-linq-queries --dynamic-where-clause-part-2.aspx

Hi BeSharp,

Does the info in my last reply helps some or have you got any further
progress?


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------

From: (e-mail address removed) (Steven Cheng[MSFT])
Organization: Microsoft
Date: Mon, 26 Nov 2007 06:17:43 GMT
Subject: RE: LINQ Question (Contains)

Hi BeSharp,

As for the D LINQ selection scenario you mentioned, I think it sounds like
what you want to do is a SQL "LIKE" comparison. For {"...",
"..."}.contains, "contains" actually means that the input parameter exactly
match any of the i tem in collection, that is not substring mapping. For
substring mapping, you should use SQL "Like" style selection. Here is a web
thread discussing on using "LIKE" query in LINQ:

#using LIKE in DLinq
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=308989&SiteID=1

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#no

ti
f

ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no
rights.





--------------------
From: "BeSharp" <[email protected]>
Subject: LINQ Question (Contains)
Date: Sun, 25 Nov 2007 13:28:55 +0100

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

substringComparer)
select c;

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