Search Extensions - Child collection search support and over 3000 downloads

2 min read

SearchExtensions now supports searching of child collections for IQueryable

You can now perform child collection searches on IQueryable objects which will get translated and run on the data source server returning only the filtered records.

String searching

The functionality allows for the following to be performed on the data provider and not in memory

var shops = context.Shops.SearchChildren(s => s.Products)
                         .With(p => p.Name)
                         .Containing("ball");

The above returns shops that contain any product that has the term "ball" in it's Name field.

The above will send the following SQL when using LinqToEntities

SELECT [Extent1].[Id] AS [Id]
  -- Additional columns --        
FROM [dbo].[Shops] AS [Extent1]
WHERE EXISTS (
  SELECT 1 AS [C1]
  FROM [dbo].[Products] AS [Extent2]
  WHERE ([Extent1].[Id] = [Extent2].[Shop_Id])
    AND ([Extent2].[Name] LIKE N'%ball%')
)

Support is also in place for searching multiple properties and multiple values

var shops = context.Shops.SearchChildren(s => s.Products)
                         .With(p => p.Name, p => p.Desc)
                         .Containing("ball", "balls");

Additional string comparisons

All the usual string comparisons that you expect from SearchExtensions are also supported for searching child records:

  • .StartsWith() - property starts with any of the given terms
  • .EndsWith() - property ends with any of the given terms
  • .EqualTo() - property is equal to any of the given terms
  • .Containing() - property contains any of the given terms
  • .ContainingAll() - property contains all of the given terms

Support for non string filtering

Support has also been added to allow searching child collections for matches on other types. The following example returns shops who have products with a price between 10,000 and 100,000:

var shops = context.Shops.SearchChildren(s => s.Products)
                         .With(p => p.Price, p => p.RRP)
                         .Between(10000, 100000);

Both the examples so far will return a collection of Shops where any of it's products match the given criteria.

The equivalent of the above without utilising SearchExtensions is as follows:

var shops = context.Shops.Where(s => s.Products.Any(p =>
                              (p.Price > 10000 && p.Price < 100000)
                           || (p.RRP > 10000 && p.RRP < 100000)));

All the usual non string comparisons have also been implemented for IQueryable searching:

  • .GreaterThan()
  • .GreaterThanOrEqualTo()
  • .LessThan()
  • .LessThanOrEqualTo()
  • .Between()

Method Chaining

As is the norm with SearchExtensions, It is also possible to chain your search criteria together in order to create more complex filtering:

var result = context.Countries.SearchChildren(x => x.Cities)
                    .With(c => Name, c => c.LocalName)
                    .StartsWith("A", "E", "I", "O", "U")
                    .EndsWith("S", "T", "U", "V", "W");

To read more about SearchExtensions, please visit ninjanye.github.io/SearchExtensions/

Thanks again to @bzbetty for getting in touch via a github issues and creating this feature 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

To install SearchExtensions, you can either install it via the Nuget Package manager or by using the following in the package manager console

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