3

We have an e-commerce site that uses Web Api and Entity Framework. I recently had created an ApiController for an entity called "BundleProduct". When I call the GET method on the controller, I get a System.Data.SqlClient.SqlException: "Invalid object name 'dbo.BundleProducts1'."

Using the VS debugger, this is the query that was being executed (there are only two columns in this table, with together form a primary key):

SELECT 
[Extent1].[Fk_BundleID] AS [Fk_BundleID], 
[Extent1].[Fk_ProductID] AS [Fk_ProductID]
FROM [dbo].[BundleProducts1] AS [Extent1]

There is no "BundleProducts1" table, it should be called "BundleProducts". I did a search in the code and cannot find any instances where the name "BundleProducts1" is used.

The BundleProducts table represents a many-to-many relationship between a "Bundles" table and "Products" table. This particular table has only two columns and both together are the primary key. I did look at the DbContext class and the only references it has to BundleProducts are:

public DbSet<BundleProduct> BundleProducts { get; set; }

modelBuilder.Entity<Bundle>()
.HasMany(e => e.Products)
.WithMany(e => e.Bundles)
.Map(m => m.ToTable("BundleProducts")
.MapLeftKey("Fk_BundleID")
.MapRightKey("Fk_ProductID"));

Why is EF appending a "1" to the table name and what can I do to change this?

DesertFoxAZ
  • 439
  • 1
  • 4
  • 14
  • 1
    Ive seen EF do this when you have two foreign keys that map to different records in the same table, but I havent seen it get the table name wrong like that. Can you post the entire DbContext? – victor Apr 06 '17 at 20:34
  • 2
    Entity Framework uses numbers to resolve name conflicts so there is a good chance you have a name confliction. I would: 1. Look for conflicting classes. 2. Make sure everything is up-to-date. – Mark Beleski Apr 06 '17 at 20:37
  • @victor - I cannot post the entire DBContext, there are hundreds of tables in the database. – DesertFoxAZ Apr 06 '17 at 20:56
  • @MarkBeleski - I did a search in the project and there are no conflicting classes. – DesertFoxAZ Apr 06 '17 at 20:59

1 Answers1

6

When you use the many-to-many mapping (HasMany - WithMany), EF will use a hidden association class named BundleProducts.

The problem is that you also added a visible class BundleProducts. EF tries to do what you instructed it to do: map both classes to a table and then it encounters a name conflict. The visible class is victimized and is renamed.

You either have to remove the visible class from your model, or transform the many-to-many mapping into two one-to-many mappings with BundleProducts in the middle: Bundle 1 - n BundleProduct n - Product.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • I need to use the BundleProducts class in my ApiController so option 1 doesn't appear to be feasible for me. How would I implement option 2? – DesertFoxAZ Apr 06 '17 at 21:19
  • [Here](http://stackoverflow.com/q/9959981/861716) is an example. `Enrollment` is your `BundleProduct`. – Gert Arnold Apr 06 '17 at 21:23
  • I am trying to use that example to make the necessary changes to my code. We used "code first from existing database" and was thinking maybe something was missing because I created models for all table in the database and the BundleProducts model is not there. I created a new project and selected the Bundles, Products, and BundleProducts tables and it only created classes for Bundles and Products. It did refer to BundleProducts in the DbContext like I had mentioned before but if I go and add BundleProducts again, it creates the class for me but doesn't solve the problem. – DesertFoxAZ Apr 06 '17 at 22:05
  • It just creates the many-to-many mapping (without a visible junction class), because `BundleProduct` only contains the two foreign keys and no additional data. It's common practice to use "code first from existing database" as a starting point and then modify the class model as desired (for instance: give it more meaningful navigation property names). – Gert Arnold Apr 06 '17 at 22:12
  • If I request a Bundle it includes a Products property which is a collection of Product objects. However, our Web Api doesn't expose these models, we use DTO's, which do not include properties that are instances of other classes (representing relationships). I suppose I can modify the DTO to include that but I don't want to modify the Products when saving a Bundle. My goal here is to retrieve, save and delete rows in the BundleProduct table and I'm not sure how to do that. This is a rewrite of an existing app that used stored procedures accessed by ADO.NET where we didn't have this problem. – DesertFoxAZ Apr 06 '17 at 22:31
  • I don't understand the trouble. I you add `BundleProduct` to the model, setup the 2 one-to-many mappings, and remove the many-to-many mapping you're OK. – Gert Arnold Apr 07 '17 at 08:39
  • Okay, I think I got it. I also had to add a virtual property ICollection to both the Bundle and Product classes. I ended up with this: // Commented out the modelBuilder code above modelBuilder.Entity().HasKey(e => new { e.Fk_BundleID, e.Fk_ProductID }); modelBuilder.Entity().HasMany(e => e.BundleProducts).WithRequired().HasForeignKey(e => e.Fk_BundleID); modelBuilder.Entity().HasMany(e => e.BundleProducts).WithRequired().HasForeignKey(e => e.Fk_ProductID); – DesertFoxAZ Apr 07 '17 at 19:03