Querying an SPFieldMultiLineText field for an exact line match with LINQ

In the UI provided, if a user looked for the term ‘Microsoft Office SharePoint Server’, I needed to return back the ‘SharePoint’ term (first item, first column in the table).
There were three options I could think of to accomplish this:

1. CAML query.I ruled this out fairly quickly. Unfortunately, I had to do an exact match on the term that the user entered. So, for instance, if the user entered the term ‘Windows SharePoint Services’, then the first term (SharePoint) would be returned. However, if the user only entered the term ‘Windows’, the ‘SharePoint’ item shouldn’t be returned because it was not an exact match. With CAML, there was really no way for me to specify an exact match per line. I couldn’t use <Eq> because that would necessitate the entire Synonyms field to match what the user entered. I also couldn’t use <Contains> as that would result in false positives. None of the other comparison operators seemed appropriate either.
2. For-each loop. Of course, i could have iterated through the items one by one and examine the Synonyms field. For each item in the list, I’d have to examine the Synonyms line-by-line to find the exact match. This was originally what I planned on doing, however, it felt inelegant and potentially inefficient, especially because this list is going to contain many items.
3. LINQ. I didn’t know exactly how I could use LINQ to tackle this but I always felt like I could. So this is the path I attempted. Obviously, it worked or I wouldn’t be writing this blog post :).

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

Below is the code I wrote to query the field for an exact line match:

 1: public string GetTerm(string userSearchTerm)

 2: {

 3:     using (SPSite site = new SPSite("http://mysite"))

 4:     {

 5:         using (SPWeb web = site.OpenWeb("myweb"))

 6:         {

 7:             SPListItemCollection items = web.Lists["mylist"].Items;

 8:

 9:             string[] delimiters = new string[] { "rn" };

 10:

 11:             var term = (from e in items.OfType<SPListItem>()

 12:                         where ((string)e["Term"]).Equals(userSearchTerm, StringComparison.InvariantCultureIgnoreCase)

 13:                         || ((string)e["Synonyms"]).ToLower().Split(delimiters, StringSplitOptions.RemoveEmptyEntries).Contains(userSearchTerm.ToLower())

 14:                         select e).FirstOrDefault();

 15:

 16:             if (term != null)

 17:                 return (string)term["Term"];

 18:             else

 19:                 return null;

 20:         }

 21:     }

 22: }

The code isn’t terribly difficult but a few lines probably warrant some explanation. The first thing to understand is how the values are stored in an SPFieldMultiLineText field. Each line is in the field is split using a carriage return/line-feed combination (rn). So if I wanted the value of the second item in the list, the value would be represented as “FAST ESPrnFAST Search for Internet SitesrnFAST Search for SharePointrnFSIS”. Line 9 sets up the pattern that I’m going to be looking for to treat each line as a separate item.
Lines 11-14 is the LINQ code. In line 11, I first need to convert the items object into a generic, IEnumerable<T> representation of the collection. Even though the SPListItemCollection class implements IEnumerable and LINQ is supposed to work with IEnumerable objects, LINQ didn’t seem to work directly with the SPListItemCollection. It’s almost like I needed to give LINQ a “hint” what type of items the collection held.
Line 12 checks for an exact match of the user’s query terms to the term stored in the SharePoint list itself. For example, if the user searches for “FAST Search”, then the second item in the list better be returned.
Line 13 is where I handle the Synonyms multiline field. The first thing I do is convert the entire string in the field to lowercase, then I split the value of that into an array using the ‘rn’ combination as the delimiter. I then just need to call the Contains() method to see if the term the user entered is in the array and return the SharePoint list item if it is or null if it isn’t (Line 14 – call to FirstOrDefault()).
That’s pretty much it. Not hard stuff but I think a pretty useful technique.