MySQL Bugs: #62185: Table name ignored with Entity Framework Code First

Bug #62185	Table name ignored with Entity Framework Code First
Submitted:	16 Aug 2011 23:06	Modified:	6 Nov 2011 22:17
Reporter:	Alexandr Ponomarenko	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.4.3	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Code First, Contribution, EF, fluent API

Description:
GetAssociationCreateScript ignore Table metadata.

How to repeat:
modelBuilder.Entity<Post>()
   .HasMany(p => p.Tags)
   .WithMany(t => t.Posts)
   .Map(mc =>
   {
       mc.ToTable("PostJoinTag");
       mc.MapLeftKey("PostId");
       mc.MapRightKey("TagId");
   });

It will generate sql for keys with PostTag name, but not with PostJoinTag.

Suggested fix:
protected override string DbCreateDatabaseScript(string providerManifestToken,
            StoreItemCollection storeItemCollection)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("-- MySql script");
            sql.AppendLine("-- Created on " + DateTime.Now);

            foreach (EntityContainer container in storeItemCollection.GetItems<EntityContainer>())
            {
                // now output the tables
                foreach (EntitySet es in container.BaseEntitySets.OfType<EntitySet>())
                {
                    sql.Append(GetTableCreateScript(es));
                }

                // now output the foreign keys
                foreach (AssociationSet a in container.BaseEntitySets.OfType<AssociationSet>())
                {
                    sql.Append(GetAssociationCreateScript(a));
                }
            }

            return sql.ToString();
        }

        private string GetAssociationCreateScript(AssociationSet associationSet)
        {
            AssociationType a = associationSet.ElementType;

            StringBuilder sql = new StringBuilder();
            StringBuilder keySql = new StringBuilder();

            if (a.IsForeignKey)
            {
                EntityType childType = (EntityType)a.ReferentialConstraints[0].ToProperties[0].DeclaringType;
                EntityType parentType = (EntityType)a.ReferentialConstraints[0].FromProperties[0].DeclaringType;

                string childName = childType.Name;
                string parentName = parentType.Name;
                try
                {
                    childName = (string)(associationSet.AssociationSetEnds[childType.Name].EntitySet.MetadataProperties["Table"].Value == null ?
                       childName : associationSet.AssociationSetEnds[childType.Name].EntitySet.MetadataProperties["Table"].Value);
                }
                catch { }

                try
                {
                    parentName = (string)(associationSet.AssociationSetEnds[parentType.Name].EntitySet.MetadataProperties["Table"].Value == null ?
                       parentName : associationSet.AssociationSetEnds[parentType.Name].EntitySet.MetadataProperties["Table"].Value);
                }
                catch { }

                sql.AppendLine(String.Format(
                    "ALTER TABLE `{0}` ADD CONSTRAINT {1}", childName, a.Name));
                sql.Append("\t FOREIGN KEY (");
                string delimiter = "";
                foreach (EdmProperty p in a.ReferentialConstraints[0].ToProperties)
                {
                    EdmMember member;
                    if (!childType.KeyMembers.TryGetValue(p.Name, false, out member))
                        keySql.AppendLine(String.Format(
                            "ALTER TABLE `{0}` ADD KEY (`{1}`);", childName, p.Name));
                    sql.AppendFormat("{0}{1}", delimiter, p.Name);
                    delimiter = ", ";
                }
                sql.AppendLine(")");
                delimiter = "";
                sql.Append(String.Format("\tREFERENCES {0} (", parentName));
                foreach (EdmProperty p in a.ReferentialConstraints[0].FromProperties)
                {
                    EdmMember member;
                    if (!parentType.KeyMembers.TryGetValue(p.Name, false, out member))
                        keySql.AppendLine(String.Format(
                            "ALTER TABLE `{0}` ADD KEY (`{1}`);", parentName, p.Name));
                    sql.AppendFormat("{0}{1}", delimiter, p.Name);
                    delimiter = ", ";
                }
                sql.AppendLine(");");
                sql.AppendLine();
            }

            keySql.Append(sql.ToString());
            return keySql.ToString();
        }

File with fix

Attachment: ProviderServices.cs (text/plain), 18.59 KiB.

and one more fix in GetTableCreateScript:

private string GetTableCreateScript(EntitySet entitySet)
        {
            EntityType e = entitySet.ElementType;

            StringBuilder sql = new StringBuilder("CREATE TABLE ");
            string tableName = (string)entitySet.MetadataProperties["Table"].Value == null ?
                e.Name : (string)entitySet.MetadataProperties["Table"].Value;
            sql.AppendFormat("`{0}`(", tableName);
            string delimiter = "";
            bool hasPK = false;
            foreach (EdmProperty c in e.Properties)
            {
                Facet facet;
                hasPK = hasPK ||
                    (c.TypeUsage.Facets.TryGetValue("StoreGeneratedPattern", false, out facet) &&
                    facet.Value.Equals(StoreGeneratedPattern.Identity));
                sql.AppendFormat("{0}{1}\t`{2}` {3}{4}", delimiter, Environment.NewLine, c.Name,
                    GetColumnType(c.TypeUsage), GetFacetString(c));
                delimiter = ", ";
            }
            sql.AppendLine(");");
            sql.AppendLine();
            if (!hasPK && e.KeyMembers.Count > 0)
            {
                sql.Append(String.Format(
                    "ALTER TABLE `{0}` ADD PRIMARY KEY (", tableName));
                delimiter = "";
                foreach (EdmMember m in e.KeyMembers)
                {
                    sql.AppendFormat("{0}{1}", delimiter, m.Name);
                    delimiter = ", ";
                }
                sql.AppendLine(");");
                sql.AppendLine();
            }
            return sql.ToString();
        }

Updated ProviderServices.cs

Attachment: ProviderServices.cs (text/plain), 18.61 KiB.

Thanks for your report Alexandr,

This fix was already made in http://bugs.mysql.com/bug.php?id=61230, as such I couldn't reproduce it in v.6.4.4 (ie. it is already naming the table as specified in OnModelCreating override).

Can you confirm this is fixed in 6.4.4?

Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".