Entity Framework issues - appends a "1" to my table name?

Question

I have the following model-first (is that what it's called?) diagram that I have made. I use T4 to generate the classes.

Now, I have a problem that causes Entity Framework to somehow append a "1" to the table name of the DatabaseSupporter entity. The database has been generated from this very model, and nothing has been modified.

I am trying to execute the following line:

_entities.DatabaseSupporters.SingleOrDefault(s => s.Id == myId);

The error I receive when executing that line (along with its inner exception below) is:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in mscorlib.dll but was not handled in user code.

Invalid object name 'dbo.DatabaseSupporter1'.

I tried fixing the problem with the following Fluent API code (notice the second line in the function that names the table explicitly to "DatabaseSupporter"), but with no luck.

protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
{

    modelBuilder
        .Entity<DatabaseSupporter>()
        .HasOptional(f => f.DatabaseChatSession)
        .WithOptionalPrincipal(s => s.DatabaseSupporter);

    modelBuilder
        .Entity<DatabaseSupporter>()
        .Map(m =>
        {
            m.Property(s => s.Id)
                .HasColumnName("Id");
            m.ToTable("DatabaseSupporter");
        });

    modelBuilder
        .Entity<DatabaseSupporter>()
        .HasMany(s => s.DatabaseGroups)
        .WithMany(g => g.DatabaseSupporters)
        .Map(m =>
        {
            m.ToTable("DatabaseSupporterDatabaseGroup");
            m.MapLeftKey("DatabaseGroups_Id");
            m.MapRightKey("DatabaseSupporters_Id");
        });

    modelBuilder
        .Entity<DatabaseGroup>()
        .HasRequired(g => g.DatabaseChatProgram)
        .WithMany(c => c.DatabaseGroups);

    modelBuilder
        .Entity<DatabaseGroup>()
        .HasRequired(g => g.DatabaseOwner)
        .WithMany(o => o.DatabaseGroups);

    modelBuilder
        .Entity<DatabaseOwner>()
        .HasMany(o => o.DatabaseChatSessions)
        .WithRequired(o => o.DatabaseOwner);

    base.OnModelCreating(modelBuilder);
}

It should be mentioned that the Id property for every entity actually is a Guid.

I am using Entity Framework 6.0.2.

Any ideas?

Edit 1 Here's the generated DatabaseSupporter.cs file containing my DatabaseSupporter entity as requested in the comments.

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace Coengage.Data.Entities
{
    using System;
    using System.Collections.Generic;

    public partial class DatabaseSupporter
    {
        public DatabaseSupporter()
        {
            this.DatabaseGroups = new HashSet<DatabaseGroup>();
        }

        public bool IsActive { get; set; }
        public string Username { get; set; }
        public System.Guid Id { get; set; }

        public virtual DatabaseChatSession DatabaseChatSession { get; set; }
        public virtual ICollection<DatabaseGroup> DatabaseGroups { get; set; }
    }
}

Edit 2 The errors started occuring after I added the many-to-many link between DatabaseSupporter and DatabaseGroup. Before that link, the Fluent code wasn't needed either.

Ironworks

I Haven't used T4 to generate any classes before because I create all mine and then let Entity Framework create the database (code-first). Could you post your POCO class or the method behind the T4 generation? Attempting to alter the table name using fluent API will not work as its the class which is wrong. – Crupper

I am using the default T4 template. Entity Framework generates the classes for me, but it is using T4. The point is though that the T4 templates have not been modified. – Ironworks

Does it create a POCO class you can view/post? – Crupper

It creates .cs files yes, as seen here: i.imgur.com/J3QQQ7C.png. Are those the ones you want to see? If so, which one? – Ironworks

there should be a DatabaseSupporter.cs file somewhere? The contents of that is the important bit. If not, search your code for DatabaseSupporter, that should find the solution to your issue. – Crupper

I added the file as requested. Thanks so far for wanting to help. – Ironworks

That file looks perfectly normal. I'm not quite sure why It's a partial class though... I'm running out of ideas. Invalid object name 'dbo.DatabaseSupporter1' suggests it is looking for a real database or at least the web.config or app.config are looking for it. I would do Ctrl+F and see if "dbo.DatabaseSupporter1" is in the code. If not, check SQL Express doesn't have an existing database assuming your using SQL Server. Other than that I don't think I can help mate. Good luck. – Crupper

I searched the entire solution for DatabaseSupporter1 - no results found. Entity Framework makes the class partial so that it is possible to extend if needed in separate files. However, I can assure you that I haven't made such extension. – Ironworks

I just made a new edit, explaining when the issues started occuring. Could that help you somehow? – Ironworks

What do you see in the model browser under store model? – Permission

Did you regenerate the database after adding the many-to-many relationship? To handle many-to-many relationship, I believe Entity will create a relationship table if one is not explicitly created in the model. That could be the table referenced with the 1 appended to it's name. – Catabolite

Yes, I regenerated the database after I added the many-to-many relationship. I fail to see why that may have caused a problem? – Ironworks

If you remove the many-to-many is that fixes your problem? What is the exact code what is throwing the exception? Do you get exception from the same place every time? Can you post somewhere the full stacktrace of the exception? Have you tried recreate your model in an empty project to repro this behaviour? Have you manually opened (e.g in notepad) your Model.edmx and searched for DatabaseSupporter1 ? – Ankus

If I remove the many-to-many that doesn't fix my problem. However, reverting to before I added the many-to-many fixes it. The exact code that throws the exception is already shown. If I remove my fluent mappings entirely, it's not the same exception being thrown (it throws something about a group and a supporter, and a principal). I have not tried recreating the model in an empty project - that takes a lot of time. I already tried searching the EDMX in Notepad for references - none were found. – Ironworks

Have you checked your database? Can you verify that only 1 DatabaseSupporters table is being created? Perhaps try adding a new property to your DatabaseSupporter entity and let me know whether the corresponding field actually gets created in database. – Rebroadcast

Also, could you perhaps copy the stacktrace for the error? – Rebroadcast

Answer 1

This mapping is incorrect:

modelBuilder
    .Entity<DatabaseSupporter>()
    .Map(m =>
    {
        m.Property(s => s.Id)
            .HasColumnName("Id");
        m.ToTable("DatabaseSupporter");
    });

It is kind of 50 percent of a mapping for Entity Splitting - a mapping that stores properties of a single entity in two (or even more) separate tables that are linked by one-to-one relationships in the database. Because the mapping is not complete you even don't get a correct mapping for Entity Splitting. Especially EF seems to assume that the second table that contains the other properties (that are not explicitly configured in the mapping fragment) should have the name DatabaseSupporter1. I could reproduce that with EF 6 (which by the way has added a Property method to configure single properties in a mapping fragment. In earlier versions that method didn't exist (only the Properties method).) Also the one-to-one constraints are not created correctly in the database. In my opinion EF should throw an exception about an incorrect mapping here rather than silently mapping the model to nonsense without exception.

Anyway, you probably don't want to split your entity properties over multiple tables but map it to a single table. You must then replace the code block above by:

modelBuilder.Entity<DatabaseSupporter>()
    .Property(s => s.Id)
    .HasColumnName("Id");

modelBuilder.Entity<DatabaseSupporter>()
    .ToTable("DatabaseSupporter");

The first mapping seems redundant because the property Id will be mapped by default to a column with the same name. The second mapping is possibly also redundant (depending on if table name pluralization is turned on or not). You can try it without this mapping. In any case you shouldn't get an exception anymore that complains about a missing dbo.DatabaseSupporter1.

Yogi

Answer 2

I don't have my dev environment here in front of me, but my immediate thoughts are:

FIRST

Your fluent looks ok - but is the plural s in your ID column correct? And no plural (s) on the table names? This would be the opposite of convention.

SECOND

EF will automatically append a number to address a name collision. See similar question here: Why does EntityFramework append a 1 by default in edmx after the database entities?

Any chance you have something hanging around - a code file removed from your solution but still in your build path? Have you tried searching your source folder using windows explorer rather than the visual studio?

Allethrin

I found nothing that indicates that there's a duplicate anywhere. There are no copies of EDMX files or anything else, and searching the file in Windows Explorer just returns nothing. – Ironworks

@MathiasLykkegaardLorenzen You have no partial classes for DatabaseSupporter that aren't marked as partial? – Orcutt

Nope. If I change the class into non-partial, it still compiles. – Ironworks

Answer 3

I have replicated your model exactly as you have listed it and I cannot currently reproduce your issue in the DDL that the EDMX surface emits when Generating Database from Model.

Could you please provide detailed information on exactly how you are going about adding your many-to-many relationship between DatabaseGroup and DatabaseSupporter? You say that you're trying to add the relationship on the edmx surface and NOT through code and it craps on your table name?

I added this thing Many-to-many from DatabaseGroup to DatabaseSupporter I added this thing Many-to-many from DatabaseSupporter to DatabaseGroup

Can you please provide the following:

Rollback to your codebase prior to adding the many-to-many relationship. Ensure that your EF Fluent API code is not currently in your project.

• Generate the DDL from this surface and confirm that it is not being generated with the name DatabaseSupporters1 (Post the tablename that it chooses at this stage. DatabaseSupporter or DatabaseSupporters)

Now, right click DatabaseGroup| Add New| Association

• Choose DatabaseGroup for the left and DatabaseSupporter for the right. Confirm that the name of the association that the designer chooses is DatabaseGroupDatabaseSupporter [Do not create]
• Choose DatabaseSupporter for the left and DatabaseGroup for the right. Confirm that the name of the association that the designer chooses is DatabaseSupporterDatabaseGroup [Create]

From the edmx surface, right click the many-to-many association just created and click "Show in Model Browser"

• Edit your post to include the settings that display.

Also, right click the surface and click "Generate Database from Model."

• Edit your post to include the DDL that gets generated. The table should be named [DatabaseSupporters]

(My first inclination is that it's going to have something to do with your navigation properties, but not entirely sure. I actually had Entity Framework do the same thing to me in a toy project I was working on but I recall it being trivial to correct and I don't recall what the root cause was; I seem to recall it being something about the nav properties)

[Edit] Wait.....

If I remove the many-to-many that doesn't fix my problem. However, reverting to before I added the many-to-many fixes it. The exact code that throws the exception is already shown. If I remove my fluent mappings entirely, it's not the same exception being thrown (it throws something about a group and a supporter, and a principal). I have not tried recreating the model in an empty project - that takes a lot of time. I already tried searching the EDMX in Notepad for references - none were found.

(note my added emphasis) So the DatabaseSupporter1 error showed up after you tried your fluent api patch? Get rid of the patch, add the many-to-many and give us the real error then.

...also, it took me 5 minutes to build this diagram. I wouldn't qualify that as "a lot of time."

Sezen

Yeah I think the fluent workaround is unnecessary and better to focus on making EF play ball normally. Recreating from scratch and analyzing the SQL is a better approach. – Airwaves

Answer 4

   modelBuilder
    .Entity<DatabaseSupporter>()
    .HasMany(s => s.DatabaseGroups)
    .WithMany(g => g.DatabaseSupporters)
    .Map(m =>
    {
        m.ToTable("DatabaseSupporterDatabaseGroup");
        m.MapLeftKey("DatabaseGroups_Id");
        m.MapRightKey("DatabaseSupporters_Id");
    });

Left and Right are inversed on Many to Many. Try this :

   modelBuilder
    .Entity<DatabaseSupporter>()
    .HasMany(s => s.DatabaseGroups)
    .WithMany(g => g.DatabaseSupporters)
    .Map(m =>
    {
        m.ToTable("DatabaseSupporterDatabaseGroup");
        m.MapLeftKey("DatabaseSupporters_Id");
        m.MapRightKey("DatabaseGroups_Id");
    });

Monteith

Answer 5

I think the DatabaseSupporter class created two time

one name is : DatabaseSupporter

another one is : DatabaseSupporter1

The modified changes are stored in DatabaseSupporter1 and mapping to here.

You need to copy the DatabaseSupporter1 class code and past the code to DatabaseSupporter class . then delete this DatabaseSupporter1 class.

Vitellin

Answer 6

I had this issue from renaming tables in the diagram, specifically changing just the capitalization.

If you rename a table by clicking on the header in the diagram, I think it checks the entity set name before trying to change it, sees it exists (even though it's the same entity set), and appends a 1.

However, if you right-click and open the Properties pane and first rename the Entity Set Name, then change the Name second, it won't add the number.

Turanian

Answer 7

In my case i have two tables in the same database with the same name (2 different schemas(see image)

Hustle