SqlException: Invalid column name for string that doesn't exist in solution

Question

I'm trying to get Entity Framework working with a database that I have built, and I'm getting a strange error that doesn't make sense to me.

When I try to run my program I get an exception with the following stacktrace:

Unhandled Exception: System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. 
See the inner exception for details. ---> System.Data.SqlClient.SqlException: Invalid column name 'CorrespondenceAddress_AddressId'.

The program sounds like it is trying to look for the column CorrespondenceAddress_AddressId, and throws an exception when it can't find it. However this column has not been defined anywhere in my database or my the code within my solution, so I don't understand where it is getting this column name from.

The class in question that has the CorrespondenceAddress property is my Property class. As defined below:

public partial class Property
    {
        private int _propertyId;
        private int _instructionId;
        private int _referenceNumber;
        private string _caseOwner;
        private int _amountBorrowed;
        private Address _securityAddress;
        private int _correspondenceAddressId;
        private int _securityAddressId;

        public Property()
        {
            Occupiers = new HashSet<Occupier>();
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Property(int propertyId, int instructionId, int reference, int amountBorrowed, JurisdictionTypes jurisdiction, FunderTypes funder,
                                bool correspondenceAddressIsSecurityAddress, Address correspondenceAddress, Address securityAddress)
        {
            Occupiers = new HashSet<Occupier>();

            PropertyId = propertyId;
            InstructionId = instructionId;
            ReferenceNumber = reference;
            CaseOwner = "";
            AmountBorrowed = amountBorrowed;
            Jurisdiction = jurisdiction;
            Funder = funder;
            CorrespondenceAddressIsSecurityAddress = correspondenceAddressIsSecurityAddress;
            SecurityAddress = securityAddress;
        }

        [Key]
        public int PropertyId
        {
            get => this._propertyId;
            set => this._propertyId = Math.Abs(value);
        }

        public int InstructionId
        {
            get => this._instructionId;
            set => this._instructionId = Math.Abs(value);
        }

        public int CorrespondenceAddressId
        {
            get => this._correspondenceAddressId;
            set => this._correspondenceAddressId = Math.Abs(value);
        }

        public int SecurityAddressId
        {
            get => this._securityAddressId;
            set => this._securityAddressId = Math.Abs(value);
        }

        public int ReferenceNumber
        {
            get => this._referenceNumber;
            set => this._referenceNumber = Math.Abs(value);
        }

        [StringLength(3)]
        public string CaseOwner
        {
            get => this._caseOwner;
            set => this._caseOwner = value.Trim();
        }

        public int AmountBorrowed
        {
            get => this._amountBorrowed;
            set => this._amountBorrowed = Math.Abs(value);
        }

        public TenureTypes Tenure { get; set; }

        public JurisdictionTypes Jurisdiction { get; set; }

        public FunderTypes Funder { get; set; }

        public bool CorrespondenceAddressIsSecurityAddress { get; set; }

        public virtual Address CorrespondenceAddress { get; set; }

        public virtual Address SecurityAddress
        {
            get => _securityAddress;
            set => _securityAddress = CorrespondenceAddressIsSecurityAddress ? null : value;
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Occupier> Occupiers { get; set; }

        public virtual SolicitorInstruction SolicitorInstruction { get; set; }
    }

The database table that corresponds to this class was created using the following query:

CREATE TABLE Property
(
    PropertyId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    InstructionId INT NOT NULL FOREIGN KEY REFERENCES SolicitorInstruction(InstructionId),
    CorrespondenceAddressId INT NOT NULL FOREIGN KEY REFERENCES SolicitorInstruction(InstructionId),
    SecurityAddressId INT NOT NULL FOREIGN KEY REFERENCES SolicitorInstruction(InstructionId),

    ReferenceNumber INT NOT NULL,
    CaseOwner VARCHAR(3),
    AmountBorrowed INT,

    --2 tenure types: 1 = Freehold and 2 = Leasehold
    Tenure INT,

    --1 for Scotland, 2 for E&W, 3 for NI
    Jurisdiction INT,

    --5 funder types: Standard, PIC, LT, JR, Partnership
    Funder INT,

    CorrespondenceAddressIsSecurityAddress BIT NOT NULL,

    CONSTRAINT CHK_Tenure CHECK (Tenure BETWEEN 1 AND 2),
    CONSTRAINT CHK_Jurisdiction CHECK (Jurisdiction BETWEEN 1 AND 3),
    CONSTRAINT CHK_Funder CHECK (Funder BETWEEN 1 AND 5),
)

Can anyone explain this error for me as I'm not sure how to go about fixing it.

score 0 · Answer 1 · answered Oct 10 '18 at 13:05

Change the order of CorrespondenceAddressId and SecurityAddressId both in your entity class and table. Does the error message now change to SecurityAddress_AddressId?

EF infers things with default behaviours when things are not implicitly declared. CorrespondenceAddress_AddressId is an attempt to look for something that is not declared.

You are only declaring one:

SolicitorInstruction SolicitorInstruction { get; set; }

But there are 3 FK to SolicitorInstruction. Ef is able to get the first one working for InstructionId and guessing the others.

You have the code of the solution here:

https://stackoverflow.com/a/5559300/10192838

SqlException: Invalid column name for string that doesn't exist in solution

1 Answers1