Question 223968 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – 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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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


  2. 0

    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.
}
    1. 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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search