Linq error for nonexistent column

Question

I am in the process of training at an internship to understand asp.net and MVC. We're currently facing a problem that neither I nor my boss can seem to solve. I have a pre-constructed database of two tables, Camper and Guardian. Camper has a foreign key (guardID) that maps to Guardian's ID.

In my VS project, I have two models, GuardianEntity and CamperEntity. I also used LINQ to SQL to create the database models (dbGuardian and dbCamper). My controller converts the entity model to the db model and vice versa.

Here is the foreign key assignment as LINQ generated it:

        [global::System.Data.Linq.Mapping.AssociationAttribute(Name="Guardian_Camper", Storage="_Guardian", ThisKey="guardID", OtherKey="ID", IsForeignKey=true)]
    public Guardian Guardian
    {
        get
        {
            return this._Guardian.Entity;
        }
        set
        {
            Guardian previousValue = this._Guardian.Entity;
            if (((previousValue != value) 
                        || (this._Guardian.HasLoadedOrAssignedValue == false)))
            {
                this.SendPropertyChanging();
                if ((previousValue != null))
                {
                    this._Guardian.Entity = null;
                    previousValue.Campers.Remove(this);
                }
                this._Guardian.Entity = value;
                if ((value != null))
                {
                    value.Campers.Add(this);
                    this._guardID = value.ID;
                }
                else
                {
                    this._guardID = default(int);
                }
                this.SendPropertyChanged("Guardian");
            }
        }
    }

So it seems to recognize 'guardID' as the foreign key mapping to 'ID'. But when I run my program and add a camper, it fails on db.saveChanges(). The error says "Invalid column name 'Guardian_ID'."

This question is similar to mine: entity framework 4.1 invalid column name

Except when I followed one of the solutions offered there (changed my guardID to Guardian_ID as per convention), the error was still thrown, but this time was looking for 'Guardian_ID1'.

We've also noticed that if the column name in the DB for the key is anything other than 'ID', we get an error that says there is no key assigned, even though it is clear in the .dbml that the keys are specified. From what I'm experiencing, Linq won't work well with any column names that aren't what it expects.

@ElishevaWasserman Sorry, I'm still learning. What file is that and where would it be located in my project? — Emily Maust, Jul 02 '15 at 18:46

score 0 · Answer 1 · answered May 26 '17 at 07:15

So I had this same problem recently and it seems that it was caused by having our migrations out of sync with our Models. What the problem was for me was a parent DataModel referencing the child through an FK. What I had to do was

Remove the references to the "Child" from the "Parent's" Model. Then added a new migration reflecting that change.

Then runUpdate-Database -Script in the Package Manager Console for Visual Studio

When you've got the SQL scripted up, only copy everything after the INSERT clause. (This is what enters the Migration into the _MigrationHistory table)

Once you've copied this, paste it into a new query in SSMS. Executing the query will only add the entry into the history and your migrations will be "in sync", if you will.

Then for good measure I ran Update-Database again. Then ran my application again and it worked fine.

So in a nutshell, what ended up causing this problem was that we had removed references from Child to Parent but not the other way around. To LINQ it seems that it thought the relationship still existed so it made that a part of the query.

Linq error for nonexistent column

1 Answers1