Reputation: 3610
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.
Upvotes: 2
Views: 117
Reputation: 2535
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);
Upvotes: 0
Reputation: 70528
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?
Upvotes: 4
Reputation: 10138
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.
Upvotes: 1
Reputation: 532765
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);
Upvotes: 1
Reputation: 60493
I would try
orderby (importantCategories.Contains(ct.CatetoryId)
? 0
: 1),
c.Name
Upvotes: 1