Entity Framework Troubleshooting: Can’t have the same table names in different models

  • Article

If you are using Entity Framework in your development where there are more than 1 databases involved, you might have faced the issue if the same table name is present in different databases.

Download

Find a small project to demonstrate what is going which can be downloaded from below url.

http://1drv.ms/1RtgnNe

1. What is the problem?

If there are multiple databases being used with entity framework in a project, which have some table names which are common in both the databases, in such cases code breaks with error message like:

An unhandled exception of type ‘System.Data.Entity.Core.MetadataException’ occurred in EntityFramework.dll

Additional information: Schema specified is not valid. Errors:

The relationship ‘CustomerModel.FK_User_CustomerType’ was not loaded because the type ‘CustomerModel.CustomerType’ is not available.

The following information may be useful in resolving the previous error:

The required property ‘Users’ does not exist on the type ‘EFIssuewithSameTableNameinDiffDB.CustomerType’.

2. Why this is happening

Let’s add our first Model for Customer database. As you can see below for the table CustomerType there is an ICollection of type User.

  public partial class CustomerType

  {

  public CustomerType()

  {

 this.Users = new HashSet<User>();

  }

  public int CustomerTypeID { get; set; }

  public string CustomerTypeCode { get; set; }

  public string CustomerTypeDescription { get; set; }

 public virtual ICollection<User> Users { get; set; }

  }

Now  let’s add our second model for the Product database which also has a table CustomerType.

  public partial class CustomerType

  {

  public CustomerType()

  {

 this.Discounts = new HashSet<Discount>();

  }

  public int CustomerTypeID { get; set; }

  public string CustomerTypeCode { get; set; }

  public string CustomerTypeDescription { get; set; }

 public virtual ICollection<Discount> Discounts { get; set; }

  }

As, we can see when a new model is added to the project, in the process of creating new classes for Entities, if EF finds a class with the same name already present,

it overwrites the existing class with new class having attributes that are part of the current model being created.

As a result of which the already existing model now refers to this new entity.

Although the entity name is the same, the attributes within the class got changed, hence when the application runs it fails to find the associated properties and it fails at runtime.

The error message highlighted in green, stays the same, it tries to find the Users property which is no longer present.

3. How to fix it?

Fortunately there are multiple ways to fix it. Let’s explore the options:

Option 1:

What I felt as the cleanest way to do this is to put the models in there own folder within the project. That way the namespace of the entities being generated will be different and one model will not overwrite the entities of the other model.

Also adding a folder will make your project look a bit organised.

https://dotnetforum.files.wordpress.com/2016/03/differentnamespace.png?w=700

Option 2:

This option talks about changing the entityname to make the entities unique within the model.

Now the question would be how to make a unique name as there is only one entity class for both the models?

Answer is, although the class got overwritten but if you open the edmx file(diagram view) you can still see that all the columns are mapped correctly.

As we see below CustomerType table still has the Users under Navigation Properties.

https://dotnetforum.files.wordpress.com/2016/03/clip_image0044_thumb.jpg?w=244&h=181

Now we just need to right click on the Customer Type and say refactor, which will provide the option to rename. Just rename it to what fits for the project, save the file and a new entity would be created for your model.

https://dotnetforum.files.wordpress.com/2016/03/changeentityname.jpg?w=700

Option 3:

Another option to get away with this sitution is to change the database table name itself. That way when the model is added to system, it will not find any conflicting entities and things will work normal.

This approach is ok if we are creating database from scratch where we have the freedom to edit the entities name, but would not work (without potentially lot of re-work) if working with already existing database.