Optimize Linq code

Question

Tables:

Item
Id

Keyword
Id
Keyword

ItemKeyword
ItemId
KeywordId
SequenceNumber

---

for searching items via keyword:

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 => relatedKeywords
        .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.

Answer 1

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)