2

I am trying to write my first web api with .net core. I am using VS2017 and core 1.1. I've got everything working except for one of my objects (I've tried it with that last line commented and uncommented...it makes no difference):

public class Tag
{
    public int ID { get; set; }
    public string Name { get; set; }
    public bool ShowInFilter { get; set; }
    public ICollection<SubscriberTag> SubscriberTags { get; set; }
}

My repository code looks like this:

private SubscriptionContext db;
public TagRepository(SubscriptionContext context) { db = context; }
public Tag Find(int key) => db.Tags.SingleOrDefault(a => a.ID == key);

That is being called from my TagController:

private iTagRepository TagItems { get; set; }
public TagController(iTagRepository tagItems) {TagItems = tagItems; }

[HttpGet("{id}", Name = "GetTag")]
public IActionResult Get(int id) { return new ObjectResult( TagItems.Find(id) ); }

The problem is when I run it, the query that is executed is:

exec sp_executesql 
N'SELECT TOP(2) [a].[ID], [a].[Name], 
    [a].[ShowInFilter], [a].[SubscriberID]
FROM [Tags] AS [a]
WHERE [a].[ID] = @__key_0',N'@__key_0 int',@__key_0=1

which throw and error because Tags doesn't contain a column called SubscriberID. I've searched all my code and SubscriberID only shows up two places (in other classes which are not being used here). I have no partial classes in my entire project (saw that was an issue on a related question.)

Why is EF adding this column to its query and how do I fix it?

As requested here is the class that contains subscriberID:

public class SubscriberTag
{
    public long ID { get; set; }
    public long subscriberID { get; set; }
    public int tagID { get; set; }

    public Subscriber Subscriber { get; set; }
    public Tag Tag { get; set; }
}

Subscriber class (lots of irrelevant properties removed):

public class Subscriber
{
    public Subscriber()
    {
        //a few value initalizers/defaults
    }

    public long ID { get; set; }
    [StringLength(200)]
    public string FirstName { get; set; }
    //.......

    public ICollection<Subscribers.Models.Subscription> Subscriptions { get; set; }
    public ICollection<Subscribers.Models.Tag> Tags { get; set; }
}
5
  • I am not sure but I would look at the migrations that created the database. Looks like EF could not determine which class was the principal in the Tag-Subscription relationship and added the SubscriberID as a foreign key to Tag for you. Can we see the SubscriberID is the primary key of? Commented Mar 30, 2017 at 18:19
  • Do you have another entity class that inherits Tag? In other words, what happens if you change the definition to public sealed class Tag? Commented Mar 30, 2017 at 18:19
  • @IvanStoev No... adding sealed to the definition has no effect. Commented Mar 30, 2017 at 18:34
  • @DanielBoyer Can we see the Subscriber class as well? If Subscriber has a property: public List<Tag> Tags {get;set;} that would explain EF's foreign key expectation... but not why you are not getting a context mismatch error. Commented Mar 30, 2017 at 18:47
  • added Subscriber class Commented Mar 30, 2017 at 19:04

1 Answer 1

1

Because of the Property Tags on Subscriber:

public ICollection<Subscribers.Models.Tag> Tags { get; set; }

Entity Framework expects the Tag object to have a foreign-key to Subscriber so it builds a query with it. It looks like to configure the many-to-many relationship needs to change the property to:

public ICollection<Subscribers.Models.SubscriberTag> SubscriberTag{ get; set; }

Configuring a Many-to-Many Relationship.

Thanks for the insight Ivan

Sign up to request clarification or add additional context in comments.

5 Comments

You are right for the reason. But the fix should be just changing the public ICollection<Subscribers.Models.Tag> Tags { get; set; } to public ICollection<SubscriberTag> SubscriberTags { get; set; } as it is in the Tag class, since it semm to be a many-to-many relationship.
@IvanStoev That did it (you are correct this is a Many to Many)
@IvanStoev Of course. I see it now. OP created their own many-to-many joining table in code instead of letting EF take care of it. Good catch, I was looking at the class properties instead of seeing the naming convention bigger picture.
@JaredStroeb Actually own join table is the only option you have in EF Core currently :)
@IvanStoev Ah thanks again. I have not had the opportunity to use EF core. I am looking forward to switching over to core in the coming months.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.