Dynamic tables in Entity Framework

Question

The table has to be created dynamically. The table name is dynamic (is sent to API) and columns are static. Every time api is called, a new table is been created with different name.

Is it possible to do that in Entity Framework? If so - how? DB is Posstgress. Thanks

ADO is not the accepted way. I need to do it with Entity Framework. I tried to write migration that will be activated just when API is called. But it seems that migration can run only when running first.

I suspect there's a lot of confusion here. `ADO is not the accepted way. I need to do it with Entity Framework.` You're already using ADO.NET. EF is an ORM, not a data access library. It generates SQL queries that get executed by ADO.NET commands using ADO.NET connections. NpgSQL is an ADO.NET provider. EF deals with *objects*, not tables. Its job, like any ORM, is to map query results to objects and give the impression of working with in-memory objects instead of in-memory tables. — Panagiotis Kanavos, Jan 19 '23 at 08:04
It sounds like what you really want is a SQL generator for `CREATE TABLE`, not an ORM. Migrations aren't EF's way of running `CREATE TABLE`. They're a way of modifying the database to match a changed *object* model. They're meant to run only when the application starts. When they run they *do* generate `CREATE TABLE` or `ALTER TABLE` statements. — Panagiotis Kanavos, Jan 19 '23 at 08:11

score 1 · Answer 1 · answered Jan 18 '23 at 22:23

If you have a bunch of tables with the same columns and you want to switch between them at runtime, you can use SQL Queries.

var blogs = context.Blogs
    .FromSql($"SELECT * FROM {QuoteName(tableName)}")
    .ToList();

where QuoteName prevents SQL Injection attacks. This one is for SQL Server:

private static string QuoteName(string identifier)
{
    var sb = new StringBuilder(identifier.Length + 3, 1024);
    sb.Append('[');
    foreach (var c in identifier)
    {
        if (c == ']')
            sb.Append(']');
        sb.Append(c);
    }
    sb.Append(']');
    return sb.ToString();
}

Dynamic tables in Entity Framework

1 Answers1