How to search for varchar field using LINQ to compose a list of recommendations
I am trying to build 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 "fluorescent chandelier".
I want to allow the user to enter text such as "Red Lamp" and be able to look for the "Labels" field in the ItemCode table where the text contains "Red" or "Lamp".
I am trying to recommend the user a choice and this while basic is a good first step ... just need help building the query.
I am using CSLA as my framework, here is the example 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!
source to share
EDIT: Updated to reflect your example code
Assuming your Criteria object has a field called Labels which is a search string (for example, "Red Lamp"), do the following:
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 lines in which the labels field contains any of the words in the criteria search bar. Words are assumed to be separated by spaces in both the Labels field and the criteria string. It will not do partial word matching, for example it will not find an entry tagged "redlamp" if the search string is represented by a "red lamp"
source to share
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. Maybe not with LINQ to SQL because of the call ToUpperInvariant()
. In this case, you can leave the normalization (causing poor search quality) or keep the normalized labels. Or you can add a call ToList()
, get all the items, and do the processing on the client machine.
source to share