CODEWITHSUNDEEP
c#linqentity-framework-coreasp.net-core-2.1
Вадим Ерёмин
Вадим Ерёмин

Reputation: 3

Dynamic table name EF CORE 2.2

I want to make a universal method for working with tables. Studied links

Dynamically Instantiate Model object in Entity Framework DB first by passing type as parameter

Dynamically access table in EF Core 2.0

As an example, the ASP.NET CORE controller for one of the SQL tables is shown below. There are many tables. You have to implement such (DEL,ADD,CHANGE) methods for each table :

    [Authorize(Roles = "Administrator")]
    [HttpPost]
    public ActionResult DeleteToDB(string id)
    {
        webtm_mng_16Context db = new webtm_mng_16Context();

        var Obj_item1 = (from o1 in db.IT_bar 
                         where o1.id == int.Parse(id)
                         select o1).SingleOrDefault();
        if ((Obj_item1 != null))
        {
            db.IT_bar.Remove(Obj_item1);
            db.SaveChanges();
        }
        var Result = "ok";

        return Json(Result);

    }

I want to get a universal method for all such operations with the ability to change the name of the table dynamically. Ideally, set the table name as a string. I know that this can be done using SQL inserts, but is there really no simple method to implement this in EF CORE

Upvotes: 0

Views: 1421

Answers (1)

TomTom
TomTom

Reputation: 62157

Sorry, but you need to rework your model.

It is possible to do something generic as long as you have one table per type - you can go into the configuration and change the database table. OpenIddict allows that. You can overwrite the constructors of the DbContext and play whatever you want with the object model, and that includes changing table names.

What you can also do is a generic base class taking the classes you deal with as parameters. I have those - taking (a) the db entity type and (b) the api side dto type and then using some generic functions and Automapper to map between them.

But the moment you need to grab the table name dynamically you are in a world of pain. EF standard architecture assumes that an object type is mapped to a database entity. As such, an ID is unique within a table - the whole relational model depends on that. Id 44 has to be unique, for a specific object, not for an object and the table it was at this moment loaded from.

You also miss up significantly on acutally logic, i.e. for delete. I hate to tell you, but while you can implement security on other layers for reading, every single one of my write/update methods are handwritten. Now, it may seem that "Authorize" works - but no, it does not. Or - it does if your application is "Hello world" complex. I run sometimes pages of testing code whether an operation is allowed in a specific business context and this IS specific, whether the user has set an override switch (which may or may not be valid depending on who he is) do bypass certain business rules. All that is anyway specific.

Oh, what you can also do... because you seem to have a lot of tables: do NOT use one class, generate them. Scaffolding is not that complex. I hardly remember when I did generate the last EF core database classes - they nowadays all come out of Entity Developer (tool from Devart), while the db is handled with change scripts (I work db first - i actually want to USE The database and that means filtered indices, triggers, some sp's and views with specific SQL), so migrations do not really work at all.

But now, overwriting the table name dynamically - while keeping the same object in the background - will bite you quite fast. It likely only works for extremely simplistic things - you know, "hello world" example - and breaks apart the moment you actually have logic.

Upvotes: 1

Related Questions