Bug #80156 HasDefaultSchema TableFragment From Clause SchemaName Missing
Submitted: 26 Jan 2016 15:43 Modified: 5 Feb 2016 13:21
Reporter: Kai Scheddin Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.9.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: DefaultSchema, HasDefaultSchema, incorrect, schema, SELECT, TableFragment, WriteSql

[26 Jan 2016 15:43] Kai Scheddin
Description:
If an Context-Model is set to an DefaultSchema via OnModelBuilder, the TableFragment.WriteSql ignores the SchemaName.

How to repeat:
Create an Context:
    
    public class Context: DbContext
    {
        public Context():base("DefaultConnection"){}

        public DbSet<Property1> Property1 { get; set; }

        public override string DefaultSchema { get; set;} = "Context1";

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.HasDefaultSchema(DefaultSchema);
        }
    }

Migration creates the Table: 

Context1.Property1

Create an Request:

 using (var db = new Model1.Context())
 {
    db.Database.Log = s => Diagnostics.Logger.WriteToLog(s);
    db.Property1.ToList();
 }

The db.Property1.ToList() is translated to:
SELECT
`Extent1`.`Id`, 
`Extent1`.`Text`, 
`Extent1`.`test`
FROM `Property1`

Should translated to:

SELECT
`Extent1`.`Id`, 
`Extent1`.`Text`, 
`Extent1`.`test`
FROM `Context1.Property1`

Suggested fix:
Origin at TableFragment.cs Line 49:

 public override void WriteSql(StringBuilder sql)
    {
      if (DefiningQuery != null)
        sql.AppendFormat("({0})", DefiningQuery);
      else
        sql.AppendFormat("{0}", QuoteIdentifier(Table));
      base.WriteSql(sql);
    }

Fix:
  public override void WriteSql(StringBuilder sql)
    {
            if (DefiningQuery != null)
                sql.AppendFormat("({0})", DefiningQuery);
            else
            {
#if DEBUG
                string From = QuoteIdentifier(String.Format("{0}.{1}", Schema, Table));
                sql.AppendFormat("{0}", QuoteIdentifier(Table));
#else
                sql.AppendFormat("{0}",QuoteIdentifier(String.Format("{0}.{1}", Schema, Table)));
#endif
            }
            base.WriteSql(sql);
    }
[26 Jan 2016 15:46] Kai Scheddin
Synopsis changed
[26 Jan 2016 17:47] Kai Scheddin
Corrected Fix:

 public override void WriteSql(StringBuilder sql)
    {
            if (DefiningQuery != null)
                sql.AppendFormat("({0})", DefiningQuery);
            else
            {
                string _schemaName = Schema;
                string _tableName = Table;

                if (_schemaName.ToLowerInvariant().Equals("dbo"))
                    _schemaName = "";

                if (!String.IsNullOrEmpty(_schemaName) && !String.IsNullOrWhiteSpace(_schemaName))
                    _tableName = String.Format("{0}.{1}", Schema, Table);
                
                sql.AppendFormat("{0}", QuoteIdentifier(_tableName));
            }
            base.WriteSql(sql);
#if DEBUG
            System.Diagnostics.Debug.WriteLine("TableFragment: " + sql.ToString());
#endif
        }
[5 Feb 2016 13:21] Chiranjeevi Battula
Hello  Kai Scheddin,

Thank you for the bug report.
This is most likely duplicate of Bug #73205, please see Bug #73205.

Thanks,
Chiranjeevi.