How to make a .NET API dynamically work with any table in the database without explicitly creating a separate class for each table?

How to make a .NET API dynamically work with any table in the database without explicitly creating a separate class for each table?

I tried to implement this using .NET reflection.

I am currently using:

.NET
Entity Framework
C#
PostgreSQL

and handling GIS data

Currently I am working on a .NET API in which I was initially handling only one table.
I had a Toilets.cs class for representing table schema of a table named toilets and had its dbcontext in dbcontext.cs which looked like this:-

Toilets.cs

using NetTopologySuite.Geometries;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace layers
{
[Table("toilets")]
public class toilets
{
[Key]
public int gid { get; set; }
public string? name { get; set; }
public double tessellate { get; set; }
public double extrude { get; set; }
public double visibility { get; set; }
[Column("ward no")]
public double wardno { get; set; }
public string? localityna { get; set; }
[Column("code no.")]
public string? codeno { get; set; }
[Column("geom", TypeName = "geometry (point)")]
public Geometry? Geom { get; set; }
}
}

dbcontext.cs

using Microsoft.EntityFrameworkCore;
using Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure;
namespace layers
{
public class dbcontext : DbContext
{
public dbcontext(DbContextOptions<dbcontext> options)
: base(options)
{
}
public DbSet<toilets> toilets { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<toilets>()
.Property(x => x.Geom)
.HasColumnType("geometry");
base.OnModelCreating(modelBuilder);
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (optionsBuilder.IsConfigured)
{
var npgsqlOptionsBuilder = new NpgsqlDbContextOptionsBuilder(optionsBuilder);
npgsqlOptionsBuilder.UseNetTopologySuite(); // Register NetTopologySuite with Npgsql
}
base.OnConfiguring(optionsBuilder);
}
}
}

But now I have to make changes to this API such that when a new table is added to the database (even with different number of columns but having geometry), then the API should dynamically map it.

I tried creating a base entity and tried to use this BaseEntity as a generic type for DbSet.
Also, tried using .NET Reflection for Dynamically adding entity configurations for each table at runtime.

My attempt kind of looks like this

DynamicDBcontext.cs

using Microsoft.EntityFrameworkCore;
using Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
namespace layers
{
public class DynamicDbContext : DbContext
{
public DynamicDbContext(DbContextOptions<DynamicDbContext> options)
: base(options)
{
}
public DbSet<BaseEntity> Entities { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// reflection  (dynamically add entity configurations)
// for each table in the database at runtime.
// map each table to the BaseEntity class.
var assembly = Assembly.GetExecutingAssembly();
var entityTypes = assembly.GetTypes().Where(t => t.IsClass && !t.IsAbstract && t.IsSubclassOf(typeof(BaseEntity)));
foreach (var entityType in entityTypes)
{
modelBuilder.Entity(entityType);
}
}
public async Task<IQueryable<BaseEntity>> GetEntities<T>(string tableName = null) where T : BaseEntity
{
if (string.IsNullOrEmpty(tableName))
{
// Retrieve all data from all tables (excluding the BaseEntity table itself)
return Entities.Where(e => e.GetType() != typeof(BaseEntity));
}
// Retrieve data from the specified table
var entityType = Assembly.GetExecutingAssembly().GetTypes().FirstOrDefault(t =>
t.IsClass && !t.IsAbstract && t.IsSubclassOf(typeof(BaseEntity)) && t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase));
if (entityType == null)
{
try
{
// Debugging: Print available types to check if "states" table is recognized
Console.WriteLine("Available Types:");
foreach (var type in Assembly.GetExecutingAssembly().GetTypes())
{
Console.WriteLine(type.Name);
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message.ToString());
}
}
// Use reflection to invoke the Set<TEntity> method with the entityType
var setMethod = typeof(DbContext).GetMethod(nameof(Set), Type.EmptyTypes).MakeGenericMethod(entityType);
var dbSet = setMethod.Invoke(this, null);
// Retrieve all data from the specified table
return await Task.FromResult(((IQueryable<BaseEntity>)dbSet).AsQueryable());
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (optionsBuilder.IsConfigured)
{
var npgsqlOptionsBuilder = new NpgsqlDbContextOptionsBuilder(optionsBuilder);
npgsqlOptionsBuilder.UseNetTopologySuite(); 
}
base.OnConfiguring(optionsBuilder);
}
}
}

BaseEntity.cs

using System.ComponentModel.DataAnnotations.Schema;
public abstract class BaseEntity
{
[NotMapped] // Exclude this property from the database schema
public string TableName { get; set; }
}

I know it will not work and an Invalid Table Name error is thrown.

Don't know how to implement this.
Help is much appreciated.

Dapper for Database Interaction

This sounds like a perfect opportunity for using the Dapper NuGet Library.

Here is a step-by-step guide from me:

1. Install Dapper with the Nuget Manager
2. Create a Repository for interaction with the tables

ExchangeRepository.cs

using Dapper;
using Npgsql;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Threading.Tasks;

public class ExchangeRepository
{
    // Variables
    const private string _connectionString = "Your PostgreSQL Connection string";

    public async Task<IEnumerable<dynamic>> GetAllAsync(string tableName)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        var query = $"SELECT * FROM {tableName}";
        var result = await connection.QueryAsync<dynamic>(query);
        return result;
    }

    public async Task<dynamic> GetByIdAsync(string tableName, int id)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        var query = $"SELECT * FROM {tableName} WHERE id = @Id";
        var result = await connection.QuerySingleOrDefaultAsync<dynamic>(query, new { Id = id });
        return result;
    }
    // You can add Update, Insert and Delete methods if you want to.
}

3. Use ExpandoObject to Update or Insert.

Example Use:

public async Task AddAsync(string tableName, ExpandoObject entity)
{
    using var connection = new NpgsqlConnection(_connectionString);

    var parameters = (IDictionary<string, object>)entity;
    var columnNames = string.Join(", ", parameters.Keys);
    var paramNames = string.Join(", ", parameters.Keys.Select(key => "@" + key));

    var query = $"INSERT INTO {tableName} ({columnNames}) VALUES ({paramNames})";

    await connection.ExecuteAsync(query, entity);
}

And finally how to use it:

var repository = new ExchangeRepository();
dynamic entity = new ExpandoObject();
entity.Name = "Some Name";
entity.Age = 30;
await repository.AddAsync("Person", entity);

I solved it using stored procedure in PostgreSQL and datatables in Entity Framework.