CODEWITHSUNDEEP
asp.net-coreentity-framework-coreschemadata-annotationspomelo-entityframeworkcore-mysql
Ivan Rakitin
Ivan Rakitin

Reputation: 79

Update to .NET 5 DataAnnotations.Schema don't work anymore. LINQ join multiple dbs / querys failing

In .NET Core 3.0 I could join multiple dbs / schemas. Here is the class Order in dbEarth:

namespace dgNet.Core.Models.Earth
{
    [Table("tbl_Order", Schema ="Earth")]
    public class Order : EntityBaseWithTypedId<int>
    {
        [Key]
        [Column("BestID")]
        public override int Id { get; set; }

Here is class SerialNumber in dbMars

namespace dgNet.Core.Models.Mars
{

    [Table("tbl_serialnumber", Schema = "Mars")]
    public class SerialNumber : EntityBaseWithTypedId<int>
    {
        [Column("serialnumber")]
        public int Serialnumber { get; set; }

        [Column("jobId")]
        public int JobId { get; set; }
        [ForeignKey("JobId")]
        public Order Order { get; set; }

Data Annotations is equivalent to the code here :

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>().ToTable("tbl_Order", "Earth");
}

So if builded a LINQ Query and included Order(dbEarth) in SerialNumber(dbMars) it worked well.

query => query.Include(serialNumber => serialNumber.Order).FirstOrDefault();

After the update to Core 5.0 SQL queries are created incorrectly.

SQL joins Orders on the same db / schema like SerialNumbers.

Using newest NuGet packages .AspCore (5.0.10) DB = MySQL Using Pomelo.EntityFrameworkCore.MySql (5.0.2)

Upvotes: 1

Views: 690

Answers (1)

lauxjpn
lauxjpn

Reputation: 5254

MySQL does not support the EF Core concept of schemas.

The EF Core concept of schemas is the same one that SQL Server uses, in which schemas are basically just categories (organization units) that you can use to group multiple tables logically together within the same database.

What MySQL calls schemas are actually databases, and a single DbContext does not support multiple databases in EF Core.

Therefore, we officially removed the very brittle multi-database support in Pomelo 3.2.0.

The official way to deal with this is shown in Implement alternatives to the current behavior to always throw, if a schema has been set for an object #982:

There are currently 3 options to choose from:

// Throw an exception, if a schema is being used. This is the default.
options.UseMySql(myConnectionString, b => b.SchemaBehavior(MySqlSchemaBehavior.Throw))

// Silently ignore any schema definitions.
options.UseMySql(myConnectionString, b => b.SchemaBehavior(MySqlSchemaBehavior.Ignore))

// Use the specified translator delegate to translate from an input schema and object name to
// an output object name whenever a schema is being used.
options.UseMySql(myConnectionString, b => b.SchemaBehavior(MySqlSchemaBehavior.Translate,
    (schema, entity) => $"{schema ?? "dbo"}_{entity}"))

There is also a way to explicitly enable the old behavior, as illustrated in method ModelBuilder.HasDefaultSchema is not working (No database selected) #22971 (comment) for Pomelo 3.2.x:

[...]

In essence, there are two steps:

  1. You need to derive from MySqlSqlGenerationHelper and override GetSchemaName:
public class CustomMySqlSqlGenerationHelper : MySqlSqlGenerationHelper
{
    public CustomMySqlSqlGenerationHelper(
        RelationalSqlGenerationHelperDependencies dependencies,
        IMySqlOptions options)
        : base(dependencies, options)
    {
    }

    protected override string GetSchemaName(string name, string schema)
        => schema; // <-- this is the first part that is needed to map schemas to databases 
}
  1. You need to provide a schema name translator:
optionsBuilder
    .UseInternalServiceProvider(serviceProvider) // use our ServiceProvider
    .UseMySql(
        "server=127.0.0.1;port=3308;user=root;password=;database=EFCoreIssue22971_01_IceCreamParlor",
        b => b.ServerVersion("8.0.21-mysql")
            .SchemaBehavior(
                MySqlSchemaBehavior.Translate,
                (schemaName, objectName) => objectName) // <-- this is the second part that is needed to map
                                                        //     schemas to databases
            .CharSetBehavior(CharSetBehavior.NeverAppend))
    .EnableSensitiveDataLogging()
    .EnableDetailedErrors();

Upvotes: 1

Related Questions