Prioritizing fields when matching multiple fields with linq

Question

I have a database with fields like firstname lastname street and searchfield. Anything that match the search field will be in my search subset here is the linq logic :

if (!String.IsNullOrEmpty(searchString))
{
  folders = folders.Where(p => p.SearchField.ToLower().Contains(searchString.ToLower()));
}

I can order it by name or firstname or whatever.

Now I would like to present the results so it prioritize the name field in relation to my search term.

For example if i look for Schmid i want to show first the people with the LastName that match Schmid then the firstname then the street ...etc

Any idea ?

I think you'll find what you're looking for here:http://stackoverflow.com/questions/3760001/linq-orderby-versus-thenby — Sam Trost, Oct 02 '12 at 13:48
Yes it is usefull but do not match exactly what i want to do. Let's say you have schmid(firstname) arnaud (lastname), john schmid, alex schmid then the first will be schmid arnaud, And i would like the first to be alex schmid then john schmid ..etc I want to show the schmid as firstname first then as lastname ...etc — Arno 2501, Oct 02 '12 at 13:50
Ah, I see what you mean now. For a string search, you're matching multiple fields, and you want some fields to be matched first. — Sam Trost, Oct 02 '12 at 13:51

Jan P. · Accepted Answer · 2012-10-02T14:55:35.263

3

I hope I understood it correctly

var res = 
    folders
        .Where(item => item.FirstName == name)
        .Union(folders.Where(item => item.LastName == name))
        /* Add more Union-Where statements */
        ;

edited Oct 02 '12 at 14:55

answered Oct 02 '12 at 14:41

Jan P.

3,261
19
26

Yes that's close thanks a lot it's really nice. But I also need to take the rest if you see what I mean. I could do it adding this : res = res.Union(folders.Except(res)) but it's ugly ... do you have better idea ? – Arno 2501 Oct 02 '12 at 14:59
just union `res` with `folders` ... union will distinct the collection – Jan P. Oct 02 '12 at 15:18

score 1 · Answer 2 · answered Oct 02 '12 at 14:52

I think the best approach is to get the matching objects first and then proceed in memory:

var lower = searchString.ToLower();
folders = folders
          .Where(p => p.SearchField.ToLower().Contains(lower))
          .ToArray();
folders = folders
            .OrderBy(f => !f.LastName.Contains(lower))
            .ThenBy(f => !f.FistName.Contains(lower))
            .ThenBy(f => !f. ...

If you do all the OrderBy's on the IQueryable the query will probably blow up, while the initial filter is the most important thing to use the database engine for.

Note that you cannot always show the items that match lower in LastName and then those with a match in FistName etc., because there may be items that have a match in both. I don't think you want to duplicate items, do you?

Thanks for your answer i don't understand why but this is damn slow when I try it — Arno 2501, Oct 02 '12 at 15:22
Probably because all lambda expressions run for each item in the list, so that's a lot of data crunching. Maybe a full db engine solution (without the `ToArray`) with a longer query is better after all. — Gert Arnold, Oct 02 '12 at 17:04

Prioritizing fields when matching multiple fields with linq

2 Answers2