How to search a varchar field, using LINQ, to build a list of recommendations

Question

I am trying to construct a LINQ query, with expression trees, to do the following:

I have a field in my ItemCode table called Labels, an example of the data contained in this field is "lamps lighting chandelier".

I want to allow the user to type in some text, i.e. "Red Lamp", and be able to search the Labels field, of the ItemCode, table where the text contains "Red" or "Lamp".

I am trying to recommend selections to the user, and this, while basic, is a good first step ... just need some help constructing the query.

I am using CSLA as my framework, here is an example of the code I currently have:

IQueryable<Data.ItemCode> query = ctx.DataContext.ItemCodes;

//
// ItemCodeId
//
if (criteria.Name != null)
     query = query.Where(row => row.ItemCodeId.Contains(criteria.ItemCodeId));
//
// Name
//
if (criteria.Name != null)
     query = query.Where(row => row.Name.Contains(criteria.Name));

var data = query.Select(row => ItemCodeInfo.FetchItemCodeInfo(row));

this.AddRange(data);

Thanks in advance!

Answer 1

EDIT: Updated to reflect your example code

Assuming your Criteria object has a field called Labels, which is the search string (eg 'Red Lamp') then:

var rows = query.ToList() // work around for local sequences error
           .Where(row => row.Labels.Split(' ')
                         .Intersect(Criteria.Labels.Split(' '))
                         .Count()>0);
this.AddRange(rows);

This will select rows where the labels field contains any of the words in the criteria search string. It assumes that words are separated by spaces in both the Labels field and the criteria string. It won't do partial word matching eg it won't find a record with a label of 'redlamp' if the search string provided is 'red lamp'

Answer 2

String searchString = "red LAMP";

String[] searchStringParts = searchString.
   ToUpperInvariant().
   Split(new Char[] {' '}, StringSplitOptions.RemoveEmptyEntries);

IEnumerable<ItemCode> resultSet = itemCodes.
   Where(itemCode => searchStringParts.
      Any(part => itemCode.Labels.ToUpperInvariant().Contains(part)));

This will work with LINQ to Object. May be not with LINQ to SQL because of the ToUpperInvariant() call. In this case you could leave out the normalization (causing bad search quality) or store the labels normalized. Or you can add a ToList() call, get all items and do the processing on the client machine.

Answer 3

It can't be done.

http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/d2791ad4-3897-4fc0-80e9-72ebc4822898

Answer 4

We have decided you go the stored procedure route for this advanced functionality. Thanks for everyone's input!