How to search good places to travel (MongoDb LINQ & .NET Core)

Let’s build a simple WebApi with .NET Core and MongoDb to query the details of different destinations around the globe. We’ll do the search with MongoDb LINQ, running different scenarios.

For a brief introduction on how to build and test a full .NET CORE WebApi with MongoDB please check my earlier article: Using MongoDB .NET Driver with .NET Core WebAPI.

This article continues with 2 other parts:

• Part 2 – Paging in MongoDB – How to actually avoid poor performance ?
• Part 3 – MongoDb and LINQ: How to aggregate and join collections

You could find the project on GitHub: github.com/fpetru/WebApiQueryMongoDb

Within this article I will use two datasets:

• Wikivoyage provides more details of the most traveler friendly museums, attractions, restaurants and hotels around the globe. The original dataset could be access from the next url.
• The second dataset comes from GeoNames, which is a geographical database covering all countries. For demo purposes, I have selected only the cities with a population over 5000 inhabitants.

Using these datasets it would be easier to run some sample queries, retrieving consistent amount of data.

Topics covered

• MongoDb – Installation and security setup
• MongoDB – use mongoimport tool
• Make a full ASP.NET WebApi project, connected async using MongoDB C# Driver v.2
• Run LINQ queries

To install

Here are all the things needed to be installed:

• Visual Studio Community 2017, including .NET Core option
• MongoDB and Robomongo

MongoDB configuration

Once you have installed MongoDB, you would need to configure the access, as well as where the data is located.

To do this, create a file locally, named mongod.cfg. This will include setting path to the data folder for MongoDB server, as well as to the MongoDB log file, initially without any authentication (last 2 lines being commented). Please update these local paths, with your local settings:

systemLog:
  destination: file
  path: "C:\\tools\\mongodb\\db\\log\\mongo.log"
  logAppend: true
storage:
  dbPath: "C:\\tools\\mongodb\\db\\data"

#Once the admin user is created, remove the comments, and let the authorization be enabled
#security:
#  authorization: enabled

Run in command prompt next line. This will start the MongoDB server, pointing to the configuration file already created (in case the server is installed in a custom folder, please update first the command)

"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --config C:\Dev\Data.Config\mongod.cfg

Once the server is started (and you could see the details in the log file), run mongo.exe in command prompt. The next step is to add the administrator user to the database. Run MongoDB with the full path (ex: “C:\Program Files\MongoDB\Server\3.4\bin\mongo.exe”) and copy paste the next code in the console:

use admin
db.createUser(
  {
	user: "admin",
	pwd: "abc123!",
	roles: [ { role: "root", db: "admin" } ]
  }
);
exit;

Stop the server, uncomment the last 2 lines from mongod.cfg file and then restart the MongoDb server.

MongoImport – Initialize the database with large datasets

We will start with Wikivoyage. The dataset was originally available here (link). To be easier to import it, I have slightly transformed it (changed to tab delimited file, and applied a minimum data cleaning). The file is available in Github (link).

The second dataset GeoNames is available in the same Github folder (link).

Running the script import.bat (found in the same folder as the datsets), will do the import of the data, creating also the a new database, called TravelDb and the associated indexes. Script is included here, but it would be better just to run the script file:

mongoimport --db TravelDb ^
            --collection WikiVoyage ^
            --type tsv ^
            --fieldFile enwikivoyage-fields.txt^
            --file enwikivoyage-20150901-listings.result.tsv^
            --columnsHaveTypes^
            --username admin ^
            --password abc123! ^
            --authenticationDatabase admin ^
            --numInsertionWorkers 4

mongoimport --db TravelDb ^
            --collection Cities ^
            --type tsv ^
            --fieldFile cities5000-fields.txt^
            --file cities5000.txt ^
            --columnsHaveTypes^
            --username admin ^
            --password abc123! ^
            --authenticationDatabase admin ^
            --numInsertionWorkers 4

Fields files specifie the field names as well as their associated types. Using the option columnsHaveTypes we make the import with the types we need (e.g. int, double, string etc.).

The result should look like this:

MongoDB – LINQ support

The .NET Core solution included here follows the same structure as in my earlier article – Using MongoDB .NET Driver with .NET Core WebAPI . There, I have already presented a step by step guide on how to create an WebApi solution from scratch, connecting to MongoDB and implementing all the basic actions of a REST API.

In comparison, here, the web controller will implement just one action (GET) – focusing mainly just on running different queries:

[NoCache]
[HttpGet]
public Task> Get()
{
    return GetTravelItemsInternal();
}

private async Task> GetTravelItemsInternal()
{
    return await _travelItemRepository.GetTravelItems();
}

In background, the query runs using LINQ syntax, and it returns first 500 records.

public async Task> GetTravelItems()
{
    try
    {
        return await _context.TravelItems.Take(500).ToListAsync();
    }
    catch (Exception ex)
    {
        // log or manage the exception
        throw ex;
    }
}

The query is rendered on the server, and we receive just the limited set of data. This is possible since we have IQueryable type interface, provided natively by MongoDB C# Driver.

...
using MongoDB.Driver.Linq;
...
public IMongoQueryable TravelItems
{
    get
    {
        return _database.GetCollection("WikiVoyage").AsQueryable();
    }
}

How to find things to do in a specific city

Let’s assume we want to find the interesting things to do in a city. We either show all the items in the city, ordered by the type of action, or just select a specific action (e.g. buy, do, eat, drink etc.).

public async Task> GetTravelItems(string cityName, string action)
{
    try
    {
        if (action != null)
                    return await _context.TravelItems
                        .Where(p => p.City == cityName && p.Action == action).ToListAsync();

        return await _context.TravelItems.Where(p => p.City == cityName)
                    .OrderBy(p => p.Action)
                    .ToListAsync();
    }
    catch (Exception ex)
    {
        // log or manage the exception
        throw ex;
    }
}

This method will be called by a GET function. Assuming that we want to search after interesting things to do in Paris (http://localhost:61612/api/travelquery/Paris?doAction=do) we get interesting results, and one of them is the next:

Running faster the queries

One way to improve the speed of the queries is to apply an index. Searching within the collection after City and Action would recommend to add a simple index with these two fields.

Executing the JavaScript file with mongo shell, will add an index on City, and then Action.

db = db.getSiblingDB('TravelDb');
db.WikiVoyage.createIndex( { City: 1, Action: 1 } );

The speed of retrieval will increase from an average of 0.150 ms to about 0.001 ms.

Group items

What if we would like to see only headlines ? What types of actions are available for a specific city, without getting into details ?

A sample query, grouping by City and Action fields would be:

await _context.TravelItems
            .GroupBy(grp => new { grp.City, grp.Action })
            .Select(g => new { g.Key.City, g.Key.Action }).ToListAsync();

To continue

I would create a second part of this article, adding pagination support as well as aggregation enhancements brought by newer MongoDB versions, taking into consideration also the second dataset. Perhaps you knew these, maybe you learned a few things. Would you like to see something more covered ?