Optimize Linq code

Tables:

Item

Id

Keyword

Id

Keyword

ItemKeyword

ItemId

KeywordId

SequenceNumber

Keyword keyword = Keyword.FirstOrDefault(a => a.Keyword
    .Equals(input, StringComparison.InvariantCultureIgnoreCase));
IEnumerable<Item> items = keyword.ItemKeyword.OrderBy(a => a.SequenceNumber)
    .SelectMany(a => a.Item);

for getting related keywords:

IEnumerable<Keyword> relatedKeywords = items.ItemKeyword
    .SelectMany(a => a.Keyword)
    .Except(keyword);
IEnumerable<Keyword> orderedRelatedKeywords = relatedKeywords
    .OrderByDescending(a => relatedKe开发者_运维知识库ywords
        .Where(b => b
            .Keyword.Equals(a.Keyword, StringComparison.InvariantCultureIgnoreCase))
            .Count())
    .Distinct();

I don't have a development computer with me right now, but I hope you get my idea. My real problem here is arranging in descending order the relatedKeywords by the times it has been used. Are there any ways we could do this? Thanks.

Hrm, you say LinqToEntities, which implies these queries will run in the database... If the queries are run in the database, aren't the string comparisons case insensitive anyway?

Here's a query form that will group, and then order by group's count.

IQueryable<string> orderedKeywords =
  from k in Keywords
  group k.Keyword by k.Keyword into g
  order by g.Count() descending
  select g.Key;

  // lambda syntax of above
IQueryable<string> orderedKeywords = Keywords
  .GroupBy(k => k.Keyword)
  .OrderByDescending(g => g.Count())
  .Select(g => g.Key)