MongoDb and LINQ: How to aggregate and join collections

Data aggregations are very helpful whenever you need to create metrics or get more insights from the data. Furthermore, joining multiple MongoDb collections may provide more meaningful results. This article will be a light intro on how to do run these on MongoDb using .NET Driver and LINQ.

Notes before starting

This article is the 3rd article, continuing Part 1: How to search good places to travel (MongoDb LINQ & .NET Core), and Part 2: Paging in MongoDB – How to actually avoid poor performance ?. All share the same GitHub project, each of them having specific code methods. Please follow the steps presented in Part 1 on how to install and configure MongoDb, as well as, the section about the initial data upload.

To install

Here are all the things needed to be installed:

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

Run project

In brief, once the MongoDb installation is complete, run the next steps:

• Clone or download the project (https://github.com/fpetru/WebApiQueryMongoDb)
• Run the import.cmd from Data\Import folder
• Open solution in Visual Studio, compile and run

GroupBy in MongoDb

MongoDb has for a long time an aggregation framework and with the .NET Driver, and its features fit nice into standard LINQ operators (such as: $project => Select(), $limit => Take(), $match => Where() etc.). LINQ is ideally suited to building up a pipeline of operations and to submit to the server as a single command.

In our example, grouping by City, and finding all available travel items would look like this:

public async Task<IEnumerable<object>> GetTravelDestinations(string cityName)
{
    var groupTravelItemsByCity = _context.TravelItems.AsQueryable()
                .Where(city => string.IsNullOrEmpty(cityName) 
						|| city.City.Contains(cityName))
                .GroupBy(s => new { s.City })
                .Select(n => new
                {
                    value = n.Key.City,
                    data = n.Count()
                });

    return await groupTravelItemsByCity.Take(100).ToListAsync();
}

The results are made available to external applications using the Get function from controller:

// GET api/Display/GroupBy?city=CityName
[NoCache]
[HttpGet("{type}")]
public async Task<IActionResult> Get(string type, [FromQuery]string city)
{
	if (!string.IsNullOrEmpty(city) && city.Length > 1) 
		return Ok(await _displayRepository.GetTravelDestinations(city));

	return NotFound();
}

I have used IActionResult interface to be able to return 404 in case the request does not follow the requirements: city needs to be provided, with a minimum lenght of 2 characters.

More about aggregation in MongoDb

All standard LINQ to SQL aggregate operators are supported: Average, Count, Max, Min, and Sum. We could also group by using more attributes. Here is an example, grouping first after City and then after each associated Action, and also using the aggregate functions (like Count, Max and Min):

public async Task<IEnumerable<object>> GetTravelItemStat()
{
	var groupTravelItemsByCityAndAction = _context.TravelItems.AsQueryable()
				.Where(s => s.City == "Paris" || s.City == "Berlin")
				.GroupBy(s => new { s.City, s.Action })
				.Select(n => new
				{
					Location = n.Key,
					Count = n.Count(),
					MaxPrice = n.Max(p => p.Price),
					MinPrice = n.Min(p => p.Price)
				});

	return await groupTravelItemsByCityAndAction.Take(100).ToListAsync();
}

Join support from MongoDb

Here is an example of running a join between 2 collections, using the LINQ as a query expression. It is a LEFT join query, starting with the first (left-most) collection (TravelItems) and then matching second (right-most) collection (CityExtended).

This means that it filters resultant items (CityExtended). The overall result could be projected in an anonymous type (our example below), or in a new entity:

public async Task<IEnumerable<object>> GetTravelItemsOfCityAsync(string cityName)
{
	var query = from travelItem in _context.TravelItems.AsQueryable()
				join city in _context.CityExtended.AsQueryable()
				   on travelItem.City equals city.Name
				into CityExtendedMatchingItems
				where (travelItem.City == cityName)
				select new
				{
					Action = travelItem.Action,
					Name = travelItem.Name,
					FirstCityMatched = CityExtendedMatchingItems.First(),
				};

	return await query.Take(10).ToListAsync();
}

Access the WebApi using Javascript

Accessing the webapi from a simple static HTML with javascript, could look like this:

In order to make available a html file within the project, we would need first to enable the access to the static files (e.g. html, css, images). These are typically located in the web root (/wwwroot) folder. For development, we could set this as project’s web root – see method (UseContentRoot):

public static IWebHost BuildWebHost(string[] args) =>
	WebHost.CreateDefaultBuilder(args)
		.UseContentRoot(Directory.GetCurrentDirectory())
		.UseStartup<Startup>()
		.Build();

In order for static files to be served, we also need to configure the Middleware to add static files to the pipeline.

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
    // ...
    app.UseStaticFiles();
    // ... 
}

Once the static files are enabled, we will use jQuery library to access the WebApi, and also to display the results (with the autocomplete widget). This code was originally available from: https://designshack.net/articles/javascript/create-a-simple-autocomplete-with-html5-jquery).

Here is the full javascript code for both autocomplete function, and WebApi server calling.

<script type="text/javascript">
    $(document).ready(function () {
        $('#autocomplete').autocomplete({
            minLength: 2,
            source: function (request, response) {
                var webApiUrl = './api/display/GroupBy' + '?city=' + request.term;
                $.getJSON(webApiUrl, request, function (data, status, xhr) {
                    response(data);
                });
            },
        });
    });
</script>

You might be interested also

• Part 1 – Run LINQ queries with MongoDB – How to search good places to travel (MongoDb LINQ & .NET Core)
• Part 2 – Paging in MongoDB – How to actually avoid poor performance ?
• Using MongoDB .NET Driver with .NET Core WebAPI
• Using MongoDB .NET Driver with ASP.NET Core MVC