Search Extensions: ContainingAll string search feature

less than a minute read

ContainingAll() string search feature

Recently I have been working on a couple of new additions to SearchExtensions. One of these changes was a request from a user to have the ability to only return results where all of the search terms were hit against any number of properties.

I'm pleased to say this feature is now part of the NinjaNye.SearchExtensions nuget package.

<p class="nuget-badge"><code>PM> Install-Package NinjaNye.SearchExtensions</code></p>

How to use ContainingAll()

var result = context.Model.Search(x => x.Name, x => x.Desc)
                          .ContainingAll("test", "search");

This will return only records where all the search terms are matched in any of the defined properties, meaning that the following records would all be returned for the above.

{ Name= "test search", Desc = "desc"}
{ Name= "test", Desc = "search"}
{ Name= "ninja", Desc = "searchtest"}

This feature is also implemented as an IQueryable extension method so can perform the search on the data source so that you don't need to bring every potential record into memory before you apply the filter.

IQueryable implementation

The SQL that is generated when using the IQueryable extension method will be similar to the following:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Desc] AS [Desc]
FROM [dbo].[Test] AS [Extent1]
WHERE (([Extent1].[Name] LIKE N'%test%') OR ([Extent1].[Desc] LIKE N'%test%'))
  AND (([Extent1].[Name] LIKE N'%search%') OR ([Extent1].[Desc] LIKE N'%search%'))

Thanks to @JamesReate for raising this as an issue. Chances are I would not have realised this was a requirement had you not made the request.

If you have a new feature request, please get in touch by adding a comment below, contact me on twitter (@ninjanye) or you can raise an issue on the github project page

Comments
Mats says:January 6, 2015

This is brilliant, exactly what I have been looking for!

John says:January 8, 2015

Hi Mats,

Thanks for getting in touch. I'm glad to be of help. If you have any observations or suggestions, please get in touch as that is usually how I get the wind of the most useful features

Regards John

rob says:August 28, 2015

This extension is great. Is there any way of combining .ContainingAll() so that it only returns whole words within a string. e.g. "I like coffee" will return when I search "coffee" but not when I search "of". I need this across multiple database columns.

John says:August 31, 2015

Hi Rob,

Thanks for getting in touch. I can understand what you are after. I have created a new issue on the project's github page so will work on official support for this.

I think there is a way of using the existing functionality to acheive what you are after. It is not the most elegant solution but might get you further down the line until the feature is complete.

data.Search(x =&gt; x.ColumnA).Containing(&quot; coffee &quot;)  // Prefix and suffix with space

Obviously this doesn't cover you if the word is at the start or end of the string so unfortunately, currently you will have to perform separate StartsWith() and EndsWith() searches:

var contains = data.Search(x =&gt; x.ColumnA).Containing(&quot; coffee &quot;)
var startsWith = data.Search(x =&gt; x.ColumnA).StartsWith(&quot;coffee &quot;)
var endsWith = data.Search(x =&gt; x.ColumnA).EndsWith(&quot; coffee&quot;)

** Note the deliberate spaces on each of the searches

Thanks for raising this issue as it is not something I would have potentially thought of. I will be working on adding this feature to SearchExtensions so keep an eye on the issue in github

John says:September 7, 2015

Hi Rob,

I have just released version 1.6 of SearchExtensions which has the feature you desire. I've have also written up a blog post on the feature and how to use it. I hope it meets your needs.

Thanks again for getting in touch.