Ordering my LINQ

Question

I have this list of companies (joined to categories), in which i search by free text input and normally order the results by name / alphabetically.

But, when the returned companies are linked to a certain category (the id's of these more important categories are in a List), i want to primarily order the results with the companies from those categories on top and secondary ordering should be alphabetically.

How can i add this ordering?

    List<int> importantCategories = new List<int>() { 14, 99, 4428 };

    var companies = (from c in context.Companies
                     join ct in context.Categories on c.CategoryId equals ct.CategoryId
                     where ct.Active == true 
                     && c.Name.Contains(freeTextFilter)
                     orderby c.Name
                       --- if company is from category 14, 99, 4428 
                           then the ordering should place those above the rest
                     select c);

I took out a couple of lines from the query, but added one of those after the questions why i was not using the joined Categories table.

Answer 1

This is similar to Hogan's answer only using extension methods and Lambda expressions rather than query syntax (although arguably this might be a case where query syntax is easier on the eyes).

var companies = context.Companies
    .Join(context.Categories, c => c.CategoryId, ct => ct.CategoryId, (c, ct) => c)
    .Where(c => c.Name.Contains(freeTextFilter))
    .OrderBy(c => importantCategories.Contains(c.CategoryId) ? 0 : 1)
    .ThenBy(c => c.Name);

Assuming you may not really need the Join...

var companies = context.Companies
    .Where(c => c.Name.Contains(freeTextFilter))
    .OrderBy(c => importantCategories.Contains(c.CategoryId) ? 0 : 1)
    .ThenBy(c => c.Name);

Answer 2

Maybe like this?

List<int> importantCategories = new List<int>() { 14, 99, 4428 };

var companies = (from c in context.Companies
                 join ct in context.Categories on c.CategoryId equals ct.CategoryId
                 where c.Name.Contains(freeTextFilter)
                 orderby importCategories.Contains(ct.CategoryID) ? 0 : 1, c.Name
                 select c);

or if you want the category to cause the order too then you might try this:

orderby importCategories.Contains(ct.CategoryID) ? ct.CategoryID : 4429, c.Name

4429 is just max value of the list + 1 which could be calculated dynamically.

As suggested this will work too, but not if you want to order by categoryID:

orderby !importCategories.Contains(ct.CategoryID) , c.Name

Is there a reason why ct is never used?

Answer 3

You can use ThenBy: here is an example

 List<string> strings = new List<string> { "f", "a", "b", "c", "d", "e" };

        var result = strings.OrderByDescending(x => x == "e")
                            .ThenByDescending(x => x == "c")
                            .ThenBy(x=>x);

This gives "e", "c", "a", "b", "d", "f"

I think you could apply that to your problem to get the correct ordering.

Answer 4

First, I don't think you need to do the join since you're not actually using any data from the category. You're only using the category id for filtering and the Company contains that data already. Second, you should use OrderBy with a custom key selector and a ThenBy so that you can get alphabetical within category. This should due it using the fluent syntax.

It first orders by category id, but only if they are in the specified categories, other entries are considered the same (int.MaxValue). Then it orders by Name within the category selections. If you don't care about the order of the categories for the first section you can use c => categories.Contains(c.CategoryId) ? 0 : 1 as the key selector.

var categories = new int[] { 14, 99, 4428 };

var companies = context.Companies
                       .Where(c => c.Name.Contains(freeTextFilter))
                       .OrderBy(c = categories.Contains(c.CategoryId) 
                                        ? c.CategoryId 
                                        : int.MaxValue)
                       .ThenBy(c => c.Name);

Answer 5

I would try

orderby (importantCategories.Contains(ct.CatetoryId) 
                            ? 0
                            : 1),
         c.Name