writing a flexibel backend using .net core and entity framework on a existing database

// draft - repo in github comming soon

I am working on a bigger project for a while now. The main reason for its complexity in my opinion, is the size of the database. With around 1100 tables and around 1000 stored procedures its my biggest database so far.

This is challenging in some ways. First one is the understanding of what every table does and how they are connected. The second one is to find all the crazy special cases in this database, like tables having spaces in its table name. But the most time expensive challenge was to find a way to build a working, flexible entity model.

Thanks to entity framework core and its cli tools I was able to generate a complete model for the database. But the generator and the model are not perfect. For example, tables with multiple keys where not generated.

So I generated the model and a matching DbContext. The generator puts all the mapping in one huge file. To work with the mapping as I like to, I wrote a simple PowerShell Script to generate separate files with the mappings for one entity in it. Now I got something like this

    |-- DefaultMapping
    |  |-- Entity-1-Mapping.cs
    |  |-- Entity-2-Mapping.cs
    |
    |-- Entities
    |  |-- Entity-1.cs
    |  |-- Entity-2.cs
    |
    |-- MyDbContext.cs

All my entities are located in a separate project. As I am using one project per API service, I included this project into my services. I modified the mappings, so that there are no relations between them as I want to decide which relations I want to use for every service. This enables me to use only a subset of my model in a service. To build the relationship between the entities I am implementing a inheritance from those generated models in my service.

|-- api
|  |-- service-1
|  |  |-- controllers
|  |  |-- mappings
|  |  |  |-- Entity-1-Mapping.cs
|  |  |  |-- Entity-2-Mapping.cs
|  |  |-- DBContext.cs
|  |
|  |-- api.entities
|  |  |-- ...

In my service-mapping I called the base function from the generated mapping.

namespace api.service1.mappings
{
    using Microsoft.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore.Metadata.Builders;
    using api.entities;

    public class Entity1Mapping : api.entities.DefaultMapping.Entity1Mapping, IEntityTypeConfiguration<Entity1>
    {
        public new void Configure(EntityTypeBuilder<Entity1> entity)
        {
            base.Configure(entity);

           entity.HasOne(d => d.Entity2)
                   .WithMany(p => p.Entity1Collection)
                   .HasForeignKey(d => d.Entity2Id)
                   .OnDelete(DeleteBehavior.ClientSetNull)
                   .HasConstraintName("FK_Entity2_Entity1");
        }
    }
}