Database Schema name used within EntityName to differentiate naming conflicts

Posts   
 
    
raxuser100
User
Posts: 6
Joined: 17-Dec-2019
# Posted on: 17-Dec-2019 12:52:46   

How is it possible to use the Database schema name within a generated C# Entity Name to prevent duplicate database table name conflicts. Also to reference tables in different Db Schemas:

Example in MS SQL, we have two tables named tUser but they are in different schemas: Also there is a [Customer].[tCustomerType] that is in the Schema [Customer]. Both [tUser] tables reference this as a FK

[Finance].[tUser]
[Marketing].[tUser]
[Customer].[tCustomerType]

-- With FK relations across Db Schemas

[Finance].[tUser] m-->1 [Customer].[tCustomerType]
[Marketing].[tUser] m-->1 [Customer].[tCustomerType]


In the generated C# code for Dtos I need the following output to add a Db Schema Name prefix to classes. Also the Dtos can reference tables in any schema:

public class Finance_User
{
   int Id {get;set;}

   // 1 to many - FK to [Customer].[tCustomerType] table
   Customer_CustomerType CustomerTypePrimary {get;set;}

}

public class Marketing_User
{
   int Id {get;set;}

   // 1 to many - FK to [Customer].[tCustomerType] table
   Customer_CustomerType CustomerTypePrimary {get;set;}

}

The suggested solution was for all tables in a Schema to be added into an EntityGroup, then to output these as separate projects. Unfortunately the Dtos in different projects cannot reference each other. This is stated in the documentation

https://www.llblgen.com/Documentation/5.6/Designer/Functionality%20Reference/ElementGrouping.htm

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 18-Dec-2019 05:22:44   

You could use Entity Model element grouping. You have to set the Group Usage project setting to AsSeparateProjects then add all the tables in schema A to model group Dev, and all tables in schema B to model group Prod. LLBLGen Pro will generate code for each group separately, instead of once for the whole project.

raxuser100
User
Posts: 6
Joined: 17-Dec-2019
# Posted on: 10-Jan-2020 14:58:08   

Thanks for the suggestion. Unfortunately for this case, it does not work fully.

In some FK's the tables span across multiple DB Schemas.

Separate Projects per Entity Group does not allow FK's to span across multiple Projects.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 13-Jan-2020 06:43:38   

My recomendation would be: When you add those duplicate names on the Reverse Engineer wizard, assign different names to those entities, like: FinanceUser, MarketingUser. That way your names are clean from model to code.

If there are a lot of those duplicates, you could write a plugin for that.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 13-Jan-2020 09:22:06   

For v5.7 we have planned to solve this using a pattern for reverse engineering (e.g. {schemaname}_{tablename}, but that doesn't help you at the moment, so for now you have to fall back to e.g. manually rename them once (the name isn't reset the next time)

Frans Bouma | Lead developer LLBLGen Pro
raxuser100
User
Posts: 6
Joined: 17-Dec-2019
# Posted on: 13-Jan-2020 11:21:27   

Thanks David and Frans.

I will most likely just rename the tables manually which should be retained after future syncs with the Db.

Would you happen to have a sample C# plugin that I could use to automate and add some custom logic please?

Look forward to v5.7!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 13-Jan-2020 11:54:26   

raxuser100 wrote:

Thanks David and Frans.

I will most likely just rename the tables manually which should be retained after future syncs with the Db.

Would you happen to have a sample C# plugin that I could use to automate and add some custom logic please?

Look forward to v5.7!

If it's a one-time thing, you can use the element search in the designer. It allows you to run any C# against the entity model.

Open element search, select 'Entity' as element type and paste the following (I use SQL Server here, the driver id of your db is in the first code pane, so you have to adjust it for that)


var cmd = new UndoablePeriodCommand("Renaming elements");
CommandQueueManagerSingleton.GetInstance().BeginUndoablePeriod(cmd);
foreach(EntityDefinition e in p.EntityModel.Vertices)
{
    var mapping = p.GetGroupableModelElementMapping(e, SqlServerSqlClientDriverID, false);
    if(mapping==null || mapping.MappedTarget==null)
    {
        continue;
    }
    e.Name = string.Format("{0}{1}", mapping.MappedTarget.ContainingSchema.SchemaOwner, e.Name);
}
CommandQueueManagerSingleton.GetInstance().EndUndoablePeriod(cmd);
return p.EntityModel.Vertices;

This will prefix all names in the entity model with the schema name. You can limit the elements you want with filtering p.EntityModel.Vertices (which are all entities in the model) based on a subset, e.g. a list of names you hardcode, e.g. to avoid having all names prefixed and only the ones you need which are duplicates (like your User example). It also has undo / redo awareness, so you can undo the action in one go. It might appear a bit slow, that's caused by the fact that you're renaming elements one by one and the UI will get notified by that and will re-render the element in the project explorer...

Renaming the tables is best done in the database itself, as some databases require to drop/recreate renamed tables so the provided tooling for the database can help there. Then you can simply sync your project in the designer with the database and the designer should pick up renamed elements (it will do heuristics for renamed elements).

If you want to write a plugin, you can look at the sourcecode archive on the website which comes with the sourcecode of a couple of plugins, and the SDK: https://www.llblgen.com/Documentation/5.6/SDK/gui_implementingplugin.htm

Frans Bouma | Lead developer LLBLGen Pro
raxuser100
User
Posts: 6
Joined: 17-Dec-2019
# Posted on: 13-Jan-2020 13:55:48   

Thanks so much for the wonderful support, I will create the Plugin and automate this out!