c# Lambda and grouping

trying to get my head around using Lambda expressions to fetch data from my database.

Say I have a table that looks a bit like this (notice the spaces and casing):

name, count:

iPhone 4, 15

iphone 4, 2

iPhone4, 8

If I try to find it开发者_Go百科ems by name (using StartsWith()), I only want to fetch the result with the highest count, independent of casing and spaces. So searches for "iphone4" "i p h o n e 4", "iPhone4" sholud all return the "iPhone 4"-record

If you have a MS Sql Server 2005+ the following would work for your stated example:

var inputString = "iPhone 4";
var token = inputString.ToLower().Replace(" ", "");

var tokenizedQuery = DataContext.Devices.Select(d => new { Device = d, Token = d.Name.ToLower().Replace(" ", "") });
var filteredQuery = tokenizedQuery.Where(d => d.Token == token);
var resultsQuery = filteredQuery.Select(d => d.Device).OrderByDescending(d => d.Count);
var result = resultsQuery.FirstOrDefault();

Here is what is going on:

1. You are creating a tokenized version of your input string by lower-casing it and then removing spaces.
2. Then you are creating a pseudo-column on your table to create a similar token column
3. Filter your results based on this token
4. Finally, select only the record with the highest count

However it is very important that you realize the ToLower() and Replace() methods are being translated to T-SQL commands that run on the sql server and not in your app. This means should you need more sophisticated tokenizing routines, or you are not using MS SQL this may not work!

As others have noted, you may want to clean up your design somewhat. You are essentially storing a key or search keyword that can have many permutations. Doing the tokenizing in a query is not portable or performant, so you should ideally store the tokenized version of this string in its own column. Alternatively, look into Full Text Indexes, as they may also address your problem (again, if using MSSQL).

Let's assume that you have a Collapse string extension, which is not hard to write. One thing you'll note is that there won't be a mapping from this to SQL so the final filtering will have to be done in LINQ to Objects. You might be able to make the DB query more efficient by doing partial filtering (i.e., on iphone), then complete the filtering in memory.

 db.Table.ToList().Where( t => t.Name.Collapse().StartsWith( searchString.Collapse() )
                  .OrderByDescending( t => t.Count )
                  .Take( 1 );

Where Collapse is

 public static class StringExtensions
 {
     public static string Collapse( this string source )
     {
          if (string.IsNullOrWhiteSpace( source ))
          {
              return string.Empty;
          }
          var builder = new StringBuilder();
          foreach (char c in source)
          {
              if (!char.IsWhiteSpace( c ))
              {
                  builder.Append( c );
              }
          }
          return builder.ToString();
     }
}

Note: you'd be better off sanitizing your database if possible AND you really want these to map to the same thing.