Optimized model no longer using correct schemas in EF 8.0.0

[chriscameron-vertexinc]

EF Core version: 8.0.0
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 8.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.8.0

Hello!

I've been using Optimized Models in EF7 with great success.

My database model shards tenants at the schema level, so I'll have common tables in the dbo schema, and tenant tables in the tenant_xyz schema. My optimized model partial does the following to make this work:

public partial class DatabaseContextModel
{
    private readonly List<RuntimeEntityType> m_EntityTypes = new();

    private string DefaultSchema { get; init; } = "dbo";

    public static IModel GetInstance(string schema)
    {
        model = new DatabaseContextModel
        {
            DefaultSchema = schema
        };
        model.Initialize();
        model.Customize();

        return model;
    }

    public override RuntimeEntityType AddEntityType(string name, Type type, RuntimeEntityType? baseType = null,
                                                    bool sharedClrType = false,
                                                    string? discriminatorProperty = null,
                                                    ChangeTrackingStrategy changeTrackingStrategy =
                                                        ChangeTrackingStrategy.Snapshot,
                                                    PropertyInfo? indexerPropertyInfo = null,
                                                    bool propertyBag = false,
                                                    object? discriminatorValue = null)
    {
        var entityType =
            base.AddEntityType(name, type, baseType, sharedClrType, discriminatorProperty,
                               changeTrackingStrategy, indexerPropertyInfo, propertyBag, discriminatorValue);

        m_EntityTypes.Add(entityType);

        return entityType;
    }

    partial void Customize()
    {
        if (DefaultSchema == "dbo")
            return;

        RemoveAnnotation("Relational:DefaultSchema");
        AddAnnotation("Relational:DefaultSchema", DefaultSchema);

        foreach (var entityType in m_EntityTypes)
            Customize(entityType);
    }

    private void Customize(RuntimeEntityType entityType)
    {
        // Can't specify schema for shared CLR types
        if (((IReadOnlyTypeBase)entityType).HasSharedClrType)
            return;

        var tableName = entityType.FindAnnotation("Relational:TableName")?.Value as string;
        if (string.IsNullOrEmpty(tableName))
            return;

        var isTenant = SchemaUtils.IsTenantSchema(tableName);
        if (!isTenant)
            return;

        entityType.RemoveAnnotation("Relational:Schema");
        entityType.AddAnnotation("Relational:Schema", DefaultSchema);
    }

Since upgrading to EF 8 it looks like my DbContext no longer uses the appropriate schema for tenant tables, and is instead looking in dbo for everything.

I've debugged the resulting model and it looks like all of the annotations are correct.

When I remove the DbContextOptionsBuilder UseModel call my context works just fine, albeit slower.

How can I ensure that optimized models in EF 8 are using the appropriate schemas per table?

[ajcvickers]

/cc @AndriySvyryd

[AndriySvyryd]

EF 8 now also stores the relational model that contains all of the database mapping information in the compiled model. You can fallback to computing just the relational model as it was for EF 7 by removing the AddRuntimeAnnotation("Relational:RelationalModel", CreateRelationalModel()); line or by adding RemoveRuntimeAnnotation("Relational:RelationalModel");

Also, you don't need to override AddEntityType by using ((IModel)this).GetEntityTypes():

public partial class DatabaseContextModel
{
    private string DefaultSchema { get; init; } = "dbo";


    partial void Customize()
    {
        if (DefaultSchema == "dbo")
            return;

        RemoveAnnotation("Relational:DefaultSchema");
        AddAnnotation("Relational:DefaultSchema", DefaultSchema);
        RemoveRuntimeAnnotation("Relational:RelationalModel");

        foreach (RuntimeEntityType entityType in ((IModel)this).GetEntityTypes())
            Customize(entityType);
    }

    private void Customize(RuntimeEntityType entityType)
    {
        // Can't specify schema for shared CLR types
        if (((IReadOnlyTypeBase)entityType).HasSharedClrType)
            return;

        var tableName = entityType.FindAnnotation("Relational:TableName")?.Value as string;
        if (string.IsNullOrEmpty(tableName))
            return;

        var isTenant = SchemaUtils.IsTenantSchema(tableName);
        if (!isTenant)
            return;

        entityType.RemoveAnnotation("Relational:Schema");
        entityType.AddAnnotation("Relational:Schema", DefaultSchema);
    }
}

[chriscameron-vertexinc]

Thank you so much for your support @AndriySvyryd !

It's a shame that the relational model optimization won't work for our multi-schema use case. Is that a hard limitation, or something that may eventually be supported? My database has hundreds of relational constraints and would likely benefit from this.

I've removed the AddRuntimeAnnotation("Relational:RelationalModel", CreateRelationalModel()); (added some scripting to post-process the generated code) and it seems to have fixed my project.

When I attempted to remove the runtime annotation in Customize() I ended up with a separate error related to duplicate runtime annotations:

System.AggregateException: One or more errors occurred. (The annotation 'Relational:DefaultMappings' cannot be added because an annotation with the same name already exists on the object EntityType: PrintBatchReturn (Dictionary<string, object>) CLR Type: Dictionary<string, object>) ---> System.InvalidOperationException: The annotation 'Relational:DefaultMappings' cannot be added because an annotation with the same name already exists on the object EntityType: PrintBatchReturn (Dictionary<string, object>) CLR Type: Dictionary<string, object>

  Stack Trace: 
AnnotatableBase.AddRuntimeAnnotation(String name, Annotation annotation)
RelationalModel.AddDefaultMappings(RelationalModel databaseModel, IEntityType entityType, IRelationalTypeMappingSource relationalTypeMappingSource)
RelationalModel.Create(IModel model, IRelationalAnnotationProvider relationalAnnotationProvider, IRelationalTypeMappingSource relationalTypeMappingSource, Boolean designTime)
RelationalModel.Add(IModel model, IRelationalAnnotationProvider relationalAnnotationProvider, IRelationalTypeMappingSource relationalTypeMappingSource, Boolean designTime)
RelationalModelRuntimeInitializer.InitializeModel(IModel model, Boolean designTime, Boolean prevalidation)
ModelRuntimeInitializer.Initialize(IModel model, Boolean designTime, IDiagnosticsLogger`1 validationLogger)
DbContextServices.CreateModel(Boolean designTime)
DbContextServices.get_Model()

GetEntityTypes() works great, but I did need to cast the entities back to RuntimeEntityType:

    public partial class DatabaseContextModel
    {
        private string DefaultSchema { get; init; } = "dbo";

        partial void Customize()
        {
            if (DefaultSchema == SchemaUtils.VERTEX_SCHEMA)
                return;

            RemoveAnnotation("Relational:DefaultSchema");
            AddAnnotation("Relational:DefaultSchema", DefaultSchema);

            foreach (var entityType in ((IModel)this).GetEntityTypes().Cast<RuntimeEntityType>())
                Customize(entityType);
        }

        private void Customize(RuntimeEntityType entityType)
        {
            // Can't specify schema for shared CLR types
            if (((IReadOnlyTypeBase)entityType).HasSharedClrType)
                return;

            var tableName = entityType.FindAnnotation("Relational:TableName")?.Value as string;
            if (string.IsNullOrEmpty(tableName))
                return;

            var isTenant = SchemaUtils.IsTenantSchema(tableName);
            if (!isTenant)
                return;

            entityType.RemoveAnnotation("Relational:Schema");
            entityType.AddAnnotation("Relational:Schema", DefaultSchema);
        }
    }

[AndriySvyryd]

Is that a hard limitation, or something that may eventually be supported?

The current implementation of the relational model is read-only, however #20284 would allow it to be mutated.

My database has hundreds of relational constraints and would likely benefit from this.

If startup time is critical for your application and you don't change the model often you could write a script that modifies the code that we generate for CreateRelationalModel and replaces the tenant usages of the schema with DatabaseContextModel.DefaultSchema.

When I attempted to remove the runtime annotation in Customize() I ended up with a separate error related to duplicate runtime annotations:

We can fix this for 9.0