Skip to content

Best way to create model via T4 in case same table names but different schema? #1425

Closed
@jdistlr

Description

How to handle this kind of T4-modelling problem, when database tables have the same name, but different schemas? The result of the generation process are conflicting (partial) classes.

Environment details

linq2db version: 2.5.2
Database Server: SQL Server 2012
Framework version: .NET Framework 4.6.2

Activity

sdanyliv

sdanyliv commented on Nov 22, 2018

@sdanyliv
Member

@jdistlr, there are several ways to solve problem:

  1. You can filter non wanted schemas
GetSchemaOptions.ExcludedSchemas = new[] { "TestUser", "SYSSTAT" }; // Defines excluded schemas.
GetSchemaOptions.IncludedSchemas = new[] { "TestUser", "SYS" };     // Defines only included schemas.
  1. You can try to set this option
GenerateSchemaAsType = true
  1. You can modify names for classes after loading schema but before generation, as described here:
    https://github.com/linq2db/linq2db/tree/master/Source/LinqToDB.Templates#customizing-generation-process
MaceWindu

MaceWindu commented on Nov 22, 2018

@MaceWindu
Contributor

But still we need to fix type name clash in generated code

added this to the 2.6.0 milestone on Nov 22, 2018
MaceWindu

MaceWindu commented on Nov 23, 2018

@MaceWindu
Contributor

Also need to document GenerateSchemaAsType option (and check if we have other undocumented options)

jdistlr

jdistlr commented on Nov 24, 2018

@jdistlr
Author

Thank you so much! GenerateSchemaAsType helps sorting out the conflict.

self-assigned this
on Nov 25, 2018
dipique

dipique commented on Jul 14, 2021

@dipique
Contributor

For anyone here because they google searched table name collisions in linq2db T4 template generated code and want to find a solution that doesn't require using schemas as types, here are a couple T4 samples to play with (to be placed between your LoadMetadata call and GenerateModel call):

Rename specific table(s) to avoid the conflict(s)

// to manually (re)name a table, add it to this dictionary using its
// qualified name (schema.name) as the key and the desired type
// name as the value
var tablesToRename = new Dictionary<string, string> {
    { "MilitaryDirective.Order", "MD_Order"} // creating this table will create a collision with Sales.Order
};

foreach(var ttr in tablesToRename)
{
    Tables[ttr.Key].Name = ttr.Value; // change the type name to avoid collision
    Tables[ttr.Key].DataContextPropertyName = ToPlural(ttr.Value); // change the context property name to avoid collision
}

Prepend the schema to the type name to all tables with a table name collision

Tables
    .GroupBy(t => t.Value.Name)
    .Where(g => g.Count() > 1)
    .SelectMany(g => g.Select(tg => tg.Value)).ToList()
    .ForEach(tbl => {
        tbl.Name = $"{tbl.Schema}_{tbl.Name}";
	tbl.DataContextPropertyName = ToPlural(tbl.Name);
    });

The first solution involves manually renaming, but has the benefit that if you are adding a table that produces a collision, you can fix that issue without renaming your old table. The second solution automatically detects collisions, but could break existing code that depends on the type name from the old table.

Neither solution is 100% polished. The two issues I'm aware of are: 1) it assumes pluralization instead of checking what setting was specified, and 2) foreign key relationships names will not be updated (but the types will, e.g. your foreign key property will be public MD_Order Order instead of public MD_Order MD_Order); for me, this was preferable, but propagating the change to FK relationships is definitely possible.

Hopefully that helps someone with the same issue I had.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Assignees

Type

No type

Projects

No projects

Relationships

None yet

Development

No branches or pull requests

Issue actions

    Best way to create model via T4 in case same table names but different schema? · Issue #1425 · linq2db/linq2db