Mongo & SQL in C#

Photo by Volkan Kaçmaz on Unsplash

Supporting repository contains simple project with worker service writing some data to both MongoDB and SQL server and exposing simplistic Swagger API.

Simple swagger getter

There is an /stats endpoint but more than comparing results it was added to demonstrate Decorator pattern using Scrutor.

What I wanted to achieve is to check how “natural” is to implement basic functionalities like :

• Insert data
• Get data based on predicates
• Abstract out underlying DB from code.

Insert data

IDataAccess should implement:

Task<bool> TryInsert<T>(T item) 
Task<IEnumerable<T>> TryGet<T>(T filter, DateTime from, DateTime to)

*there is also constrain that T should be data time but left out for visibility

MongoDB Driver version of insert operation

Collection finder in Mongo

Enitity Framework SQL access

Both Entity Framework core and Mongo Driver allow simple and clean way to insert data. While EF core requires Database context to be prepared, Mongo is more dynamic and tries to do cast database objects to classes at runtime.

Get Data

EF core way of simple query

Mongo DB driver filter builder

Date specific filter for Mongo

Dynamic filter builder for all set item

Getting data in Entity Framework is easiest using LINQ which is well documented and what is very important optimized. There is a lot of learning involved how to build LINQ to enable on database execution. Additionally help all SQL queries could be logged on runtime.

When it comes to Mongo it feels like working with json file which is expected.

Filters can be build dynamically.

FilterDefinition<T> BuildFilter(T definition)

THis method allows client to create template object where only required parameters are set and rest are kept as null.

Reflection based scanning returns set of filters which are executed on database.

Decorator

Decorator design pattern is great if you forgot to do something in first attempt but your code is already working.

In this project I started without any utility to measure SQL vs Mongo timing. Implementation of IDataAccess was completed and there was a moment

“wait how to compare them?”

Scrutor is one of packages which helps to decorate any interface:

Time Decorator construtor

What happens is that TimeDecorator is actually implementing IDataAccess but as one of its dependencies it requires implementation of service it decorates:

How to register it in Dependency Injection

One of implemented metods

Every implemented method of TimerDecorator works in simple workflow:

• Prepare some prerequisites — timer in this case
• Run decorated object method and capture its result
• Do some post execution logic — save in cache execution time
• Return result to client.

Implementation details

Dependency resolution workflow

Summary and verdict

Obviously winner is whatever fits your solution better. I decided not to publish results of any statistics summary while it depends on usage.

But what I can say for sure is:

• SQL and EF core fits well if you need extend static typed system up to data access layer, and you are willing to spend time to optimize your queries
• Mongo is more dynamic and allows more freedom. Boilerplate code is reduced, but you should ensure that runtime errors are foreseen and managed well.