0

I am creating a database that will keep track of my company's clients, and an app that will allow users inside the company to read/update/etc. the database. I'm using code-first EF Core to manage the database. The app is a C# .NET Blazor Server website, and the database is MySQL.

I initially decided to use a RESTful web API between the EF Core/database layer and the app layer, so it was roughly structured like this:

MyCo.WebApp -> MyCo.SDK -> (Server running MyCo.API) -> MyCo.Models -> (Server running MySQL DB)

Where:

  • MyCo.Models contains the data classes and the EF Core code (including the DbContext) that interacts with the database,
  • MyCo.API provides the RESTful web API,
  • MyCo.SDK provides an interface to call the API using an HttpClient (from the .NET framework) and parses the JSON back into the data classes from MyCo.Models (I'm referencing the data classes in that project so I didn't need to rewrite them), and
  • MyCo.WebApp is the user interface that displays the data and allows users to interact with it.

I initially thought having the API in the middle of everything was the right thing to do. We have other apps that will need to use this database, so having a single point of entry to the data would give us a way of controlling that. We also need to have an audit of all the changes that users make, and having the API layer gives us a single point where we can add a row to a Log table every time a user adds, updates or deletes an entity.

The problem came when I tried to retrieve the Company property of a Client. Each Client must belong to a single Company, but in the JSON that the API returned for a particular client, the Company property was always null. This is because EF Core uses lazy loading as an optimization. Since I wasn't explicitly requesting the Company property, EF Core had no reason to load it.

I could solve this by configuring EF Core to use eager loading, so all relations to an entity are loaded at the same time as the entity. But this comes at a performance cost, and there are times when I'd be happy to load a Contact entity without getting the associated Company with it.

This led me to realize that by introducing the Web API layer, I was actually losing a lot of the benefits of EF Core, like optimizing the DB calls using Linq. I could return the CompanyId inside the Contact and make another call to the API, but again I'm losing the benefit of being able to use EF Core to turn this into one DB call.

This answer to another question reasons that you shouldn't use the Repository pattern with Entity Framework because it itself is a Repository layer, which I think I agree with. The Web API I've built is essentially an additional Repository layer.

I'm now wondering if I need the API at all, since all apps that will use it in future will very likely be built in C#.NET, so they could use the EF Core DbContext directly. So instead I would have this structure:

MyCo.WebApp -> MyCo.Models -> (Server running MySQL DB)

Alternatively I could add in a Service layer which would handle the calls to DbContext, so the WebApp wouldn't need to know anything about how the database is structured.

MyCo.WebApp -> MyCo.Service -> MyCo.Models -> (Server running MySQL DB)

Or I could keep the API but change it to be non-RESTful, so it essentially acts as the Service layer - e.g. one endpoint to get a simple Contact data object, one endpoint to get a different Contact object which includes the Company data.

So my question is:

  1. Is there any value to having the RESTful Web API layer in my scenario?
  2. If not, should I use a Service layer, or is there some other common way of structuring applications of this type?
  • "an app that will allow users inside the company to read/update/etc. the database" - Are these users vetted and trustworthy? – whatsisname Apr 21 at 17:24
  • 1
    I agree with your assessment that putting EF behind a service layer seems to take a lot of the apparent benefit out of using EF to begin with. But if you do want it behind a service, then you just have to accept that the service will return DTO's transformed with the same included data structures for each method call, you'll lose the power of lazy loading if the client happens to want the associated entities/properties in some cases but not others. That will be have to be set up as different service method endpoints. – Graham Apr 21 at 17:38
  • @whatsisname Yes, auth is a whole other thing but I take your point and I'm definitely planning on restricting access to certain accounts. – Craig Brown Apr 21 at 18:46
  • To whoever downvoted, please do let me know why - I'd appreciate the opportunity to improve the question. – Craig Brown Apr 21 at 18:48
  • @CraigBrown: I ask not so much about authentication, but about the extent to which the users will attempt to gain unauthorized access. There are many enterprise softwares that talk directly to their databases, without any problems. – whatsisname Apr 21 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.