Sort by search criteria C# LINQ

Question

I have a LINQ query which searches for a string (using a regex) in multiple fields. I want to sort the results based on in which field the text was found.

Currently I have this:

var results = from i in passwordData.Tables["PasswordValue"].AsEnumerable()
           where r.IsMatch(i.Field<String>("Key").Replace(" ","")) ||
           r.IsMatch(i.Field<String>("Username").Replace(" ","")) ||
           r.IsMatch(i.Field<String>("Other").Replace(" ",""))
           orderby i.Field<String>("Key"),
           i.Field<String>("Other"),
           i.Field<String>("Username")
           select i;

I want matches found in Key first, then matches found in Other, and then matches found in Username. If possible, matches which match both Key and Other should go before matches that match only Key.

The code I currently have sorts based on Key first, so if a match is found on Other but Key starts with an A, it will be sorted before a match found on Key where Key starts with a Z.

Thanks in advance, it isn't a hard question I think but I just can't figure out how to do this as I'm new to LINQ.

Answer 1

Your one soloution is to create 2 methods , one for the Key search and one for the Other search. Then based on which field was hit on the seach you run the order by. While this may be extra coding it is the only way that i see it being done with out creating your own expresion trees which are alot harder.

Answer 2

Here is a simple, but sub-optimally performant, way to do it:

static IEnumerable<DataRow> DoSearch(DataTable table, RegEx r, string fieldName) {
    return table.AsEnumerble()
                .Where(row => r.IsMatch(row.Field<string>(fieldName).Replace(" ", ""))
                .OrderBy(row => row.Field<string>(fieldName));

}

var table = passwordData.Tables["PasswordValue"];
var results = DoSearch(table, r, "Key")
    .Union(DoSearch(table, r, "Username")
    .Union(DoSearch(table, r, "Other");

The Union method will filter out duplicates in case a row matches more than one field.

Answer 3

Using the let keyword to capture intermediate values, you can easily sort by whether there was a match before you sort the matched values:

var results = from i in passwordData.Tables["PasswordValue"].AsEnumerable()
              let fields = new {
                  Key = i.Field<String>("Key"),
                  Username = i.Field<String>("Username"),
                  Other = i.Field<String>("Other") }
              let matches = new {
                  Key = r.IsMatch(fields.Key.Replace(" ","")),
                  Username = r.IsMatch(fields.Username.Replace(" ","")),
                  Other = r.IsMatch(fields.Other.Replace(" ","")) }
              where matches.Key || matches.Username || matches.Other
              orderby matches.Key descending, fields.Key,
              matches.Username descending, fields.Username,
              matches.Other descending, fields.Other
              select i;