Partial Search in Linq for space separate values

Question

I am having a Field say Company name and the want to partially search for the data entered by user that might be space separated. Say the user enters 'ABC India PVT Ltd' the query must search for all the records that contain any one or more of the entered words. like in sql it would be

Select * 
from Company 
where CompanyName like '%ABC%' or 
      CompanyName like '%India%' or 
      CompanyName like '%PVT%' or 
      CompanyName like '%Ltd%'

I am trying some to do something like this

string search = "ABC India PVT Ltd"
String[] searchArray = search.Split(' ');
IEnumerable<Account> accountInfo = acctInfo.Get(Filter: a
=>searchArray.AsQueryable().Contains(a.CompanyName));

but this gives me exactly opposite of what i am trying to achieve. Is there a way i can achieve this.

The basic idea is to wildcard search, companyname field for any of the searchArray values.

a.CompanyName.Contains('Any value from searchArray')

score 2 · Accepted Answer · answered Jun 11 '14 at 08:30

This does the trick. Use Any() to filter on records that contain one of the keywords:

string searchTerm = "ABC India PVT Ltd";
string[] keywords = searchTerm.Split(' ');

List<Records> records = new List<Records>();
records.Add(new Records() { CompanyName = "Foo name" });
records.Add(new Records() { CompanyName = "ABC name" });
records.Add(new Records() { CompanyName = "Foo India" });
records.Add(new Records() { CompanyName = "PVT name" });
records.Add(new Records() { CompanyName = "Foo name" });
records.Add(new Records() { CompanyName = "Foobar" });
records.Add(new Records() { CompanyName = "Stackoverflow" });

var results = records.Where(x => keywords.Any(keyword => x.CompanyName
                                         .Contains(keyword))).ToList();

Dummy class:

public class Records
{
    public Records() { }
    public string CompanyName { get; set; }
}

Will give you a List<Record> of records whose CompanyName contains any of the keywords. E.g

"ABC name"
"Foo India"
"PVT name"

score 1 · Answer 2 · edited May 23 '17 at 11:49

A solution with Any as proposed by DGibbs is OK for small numbers of elements (which will probably be the case for you, so I upvoted) but EF is not capable of building an efficient query from this construct. This is what the query looks like with only 4 elements:

SELECT 
-- some fields
FROM [dbo].[Company] AS [Extent1]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM  (SELECT 
        [UnionAll2].[C1] AS [C1]
        FROM  (SELECT 
            [UnionAll1].[C1] AS [C1]
            FROM  (SELECT 
                N'a' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
            UNION ALL
                SELECT 
                N'b' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
        UNION ALL
            SELECT 
            N'c' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
    UNION ALL
        SELECT 
        N'd' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
    WHERE ( CAST(CHARINDEX([UnionAll3].[C1], [Extent1].[Name]) AS int)) > 0
)

This is not scalable. Beyond a certain number of elements (only dozens) the query will throw a SQL exception that the maximum nesting level was exceeded.

To prevent this, you should build a query with OR clauses. This is where PredicateBuilder will help:

var predicate = PredicateBuilder.False<Company>();

foreach (var keyword in searchArray)
{
    predicate = predicate.Or(c => c.Name.Contains(keyword));
}

var query = Companies.Where(predicate.Expand());

There is, by the way, a source-code alternative to LINQKit's PredicateBuilder, that does the same, but without Expand.

Partial Search in Linq for space separate values

2 Answers2