ef 6 many to many relationship giving invalid column error

Question

I modeled my m:m tables following the advice here code first many to many. Below is my model (and this is reflected in the database):

public class Catalog {
...
public virtual ICollection <CatalogItem> CatalogItems {get;set;}
public virtual ICollection <CatalogSubscriber> CatalogSubscribers {get;set;}
}

public class Item {
...
public virtual ICollection <CatalogItem> CatalogItems {get;set;}
}

public class Subscriber {
...
public virtual ICollection <CatalogSubscriber> CatalogSubscribers {get;set;}
}

public class CatalogItem{
        [ForeignKey("Catalog")]
        public Guid Catalog_Id { get; set; }
        [ForeignKey("Item")]
        public Guid Item_Id { get; set; }
        public virtual Catalog Catalog { get; set; }
        public virtual Item Item { get; set; }
}

public class CatalogSubscriber{
//similar to catalogitem
}

I am getting the error "invalid column Subscriber_Id" when trying to get catalogs, and I can see in the select statement that for some reason it thinks that Subscriber_Id is a column in Catalog (it isn't). I tried this solution, but to no avail. Funny thing is that CatalogItems were working perfectly, till I added the m:m on CatalogSubscribers. It doesn't seem to have a problem with CatalogItems at all. What am I doing wrong here?

Also, from your first link, the [Key, Column(Order = 0)] and [Key, Column(Order = 1)] parts are important if you are not doing it the fluent way. — Steve Greene, Dec 07 '16 at 17:43

Robert McKee · Answer 1 · 2016-12-07T17:40:30.607

Normally you don't even need the CatalogItem or CatalogSubscriber classes. EF will infer them for you, so long as the only fields are the two foreign keys.

public class Catalog {
...
public virtual ICollection<Item> Items {get;set;}
public virtual ICollection<Subscriber> Subscribers {get;set;}
}

public class Item {
...
public virtual ICollection<Catalog> Catalogs {get;set;}
}

public class Subscriber {
...
public virtual ICollection<Catalog> Catalogs {get;set;}
}

After reading both the links in your question, both answers there are correct. Except you've tried to manually create a custom cross reference table, which you don't need if it only contains the foreign key ids. In that case, you don't create the class at all, and use the Fluent API in your second link to tell EF that you have a Many to Many relationship between Catalog and Item, and another one between Catalog and Subscriber. Like this (I think -- I normally go database first out of habit):

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Catalog>().HasMany(m => m.Items).WithMany();
    modelBuilder.Entity<Catalog>().HasMany(m => m.Subscribers).WithMany();
}

I tried this approach, but now it says invalid columns for both Subscriber_Id and Item_Id when querying Catalog. — Riz, Dec 07 '16 at 17:58
Using EF conventions, those should be SubscriberId and ItemId when used in a foreign key field, or just Id when used in the Subscriber/Item classes (Yes, you can override it to use Subscriber_Id and Item_Id, but why unless you have a business reason for doing so?) Can you show a bit more of your classes -- just the relevant parts? Like the id fields? — Robert McKee, Dec 07 '16 at 20:13

ef 6 many to many relationship giving invalid column error

1 Answers1