Sort by C # LINQ search criteria

Question

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

I currently 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 the matches to occur in Key first, then the matches found in Other, and then the matches found in Username. If possible, matches matching both Key and the other should go before matches matching only Key.

I currently have Key-based sortings, so if a match is found in Other, but Key starts with A, it will be sorted before the match found in Key, where Key starts with Z.

Thanks in advance, this is not a difficult question, I think, but I just can’t figure out how to do this, since I'm new to LINQ.

Answer 1

Using the let keyword to capture intermediate values, you can easily sort by match before sorting 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; 

Answer 2

Your only solution is to create 2 methods, one to search by keyword and one to search for the Other. Then, based on which field was hit, you complete the order. Although this may be additional coding, this is the only way I can see that this is done without creating my own expression trees, which are much more complicated.

Answer 3

Here is a simple but sub-optimally efficient way:

 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 if a row matches multiple fields.