How to do a multiple OrderBy in LINQ across two tables.

This article will show example solutions to accomplish multiple OrderBy commands in one query in LINQ across multiple tables.

Table 1: Movies

Columns: MovieID, MovieName, and CategoryID (FK to Categories table)

Table 2: Categories

Columns: CategoryID, CategoryName

Goal: We want to select movies and order the records first by CategoryName and secondly by MovieName.

First, we can select movies and order them by CategoryName like this:

var movieRecords = dbContext.Movies.OrderBy(c => c.CategoryName);

Next, we introduce the ThenBy function and apply it to our query like this:

var movieRecords = dbContext.Movies.OrderBy(c => c.CategoryName).ThenBy(m => m.MovieName)

When we iterate through the resulting movieRecords, the items will be ordered by CategoryName and MovieName.

Another approach to accomplishing the multiple order by query without using Lambda expressions can look like this:

var movieRecords = from movieRecord in dbContext.Movies 
             orderby movieRecord.MovieCategory, movieRecord.MovieName
             select movieRecord;

Simply, separate the order by columns with a comma and write a from X orderby and select LINQ statement like the above example.

Querying Wikipedia in ElasticSearch with C# Nest client

This article assumes that you’ve already loaded the Wikipedia articles in to your local ElasticSearch as described in this previous article. Please follow the instructions in this article on how to load your ElasticSearch with the entire content of Wikipedia:

http://blog.novelessay.com/post/loading-wikipedia-in-to-elasticsearch

Start a Visual Studio C# console application project, and install the ElasticSearch Nest Nuget package. 

In your code, create a Nest ElasticClient instance that is configured for your ElasticSearch instance. We are using localhost:9200 and the index named “mywiki” as the location of our Wikipedia data. 

var node = new Uri("http://localhost:9200");
var settings = new ConnectionSettings(
    node,
    defaultIndex: "mywiki"
).SetTimeout(int.MaxValue);
ElasticClient esClient = new ElasticClient(settings);

The Wikipedia index schema has a particular field format. We’ll need a Page class like this for Nest to map fields in to:

public class Page
{
    public List<string> category { get; set; }
    public bool special { get; set; }
    public string title { get; set; }
    public bool stub { get; set; }
    public bool disambiguation { get; set; }
    public List<string> link { get; set; }
    public bool redirect { get; set; }
    public string text { get; set; }
}

Now, we can start querying our Wikipedia ElasticSearch index using our Nest client. Here’s a simple example that pulls down the first 10 Wikipedia articles:

var result = esClient.Search<Page>(s => s
    .From(0)
    .Size(10)
    .MatchAll()
    );

You can check the response for errors and loop through the Page hits like this:

if (result.IsValid)
{
    foreach (var page in result.Hits)
    {
        // page.Source.text contains the wikipedia article text

After this, you can loop through all Wikipedia documents by changing the arguments passed to From and Size in the ElasticSearch query call.

Here’s a query example that emulates a Google-like search via the use of a QueryString. Notice the use for Operator.And. I suggest you change it to Operator.Or and observe the difference effect on your results.

var result = esClient.Search<Page>(s => s
    .Take(10)
    .Query(q => q
        .QueryString(p => p.Query("cats dogs birds").DefaultOperator(Operator.And))
    )
);

If you’re ready to start getting fancy, you can write a function that builds a Nest SearchDescriptor based on your query criteria. Then use the SearchDescriptor in your query to ElasticSearch. I wanted to search Wikipedia without getting redirect link results, so I set some ignore options in the example below that exclude #redirect terms for my search descriptors.

public static SearchDescriptor<Page> GetDocumentSearchDescriptorFromSearchParameters(string queryString, bool queryAnd, string ignoreQuery)
{
    string ignoreA = "#redirect";
    string ignoreB = "redirect";

    var searchDescriptor = new SearchDescriptor<Page>()
            .Query(q =>
                q.QueryString(p => p.Query(queryString).DefaultOperator(queryAnd ? Operator.And : Operator.Or))
                && !q.Term(p => p.text, ignoreA)
                && !q.Term(p => p.text, ignoreB)
                && !q.QueryString(p => p.Query(ignoreQuery).DefaultOperator(queryAnd ? Operator.And : Operator.Or))
            );
    return searchDescriptor;
}

If this SearchDescriptor example is a little confusing, stay tuned for the ElasticSearch Wikipedia clustering future article that I intend to write. In the mean time, you should be set up to query your Wikipedia ElasticSearch index with the C# Nest client.