How to solve "SqlException: Invalid column name" while using virtual property in a C# clas-CodePudding

Basically, I need to add the mailing address functionality to my project. SQL wise I already added the column I needed for that with the ForeignKey. I get the problem at backend side. Here is my code:


        [Required]
        [StringLength(255)]
        public string ORGANIZATION_NAM { get; set; }

        public int? ADDRESS_ID { get; set; }

        public bool USING_MAILING_ADDRESS_IND { get; set; }

        public int? MAILING_ADDRESS_ID { get; set; }

        public virtual ADDRESS ADDRESS { get; set; }

I wanted to add the MAILING_ADDRESS property which is an address plain and simple like this:

    [Required]
    [StringLength(255)]
    public string ORGANIZATION_NAM { get; set; }

    public int? ADDRESS_ID { get; set; }

    public bool USING_MAILING_ADDRESS_IND { get; set; }

    public int? MAILING_ADDRESS_ID { get; set; }

    public virtual ADDRESS ADDRESS { get; set; }
    public virtual ADDRESS MAILING_ADDRESS { get; set;}

Results? I get this exception :

SqlException: Invalid column name 'ADDRESS_ADDRESS_ID'. Invalid column name 'ADDRESS_ADDRESS_ID1'. Invalid column name 'MAILING_ADDRESS_ADDRESS_ID'.

Can someone at least explain me why those 3 new columns appeared and how to deal with them?

CodePudding user response：

This issue is typically because EF cannot work out your desired FK relationships. By convention, EF will used the Type name, not the Property name for the FK naming convention. So when you have a property:

public virtual Address Address { get; set; }

EF will look for a property or column named "AddressId" or "Address_Id", and in your case it finds that and everything is fine.

However, if you were to just rename that property to:

public virtual Address MailingAddress { get; set; }

You might think EF would look for "MailingAddressId" or "MailingAddress_Id"... However it doesn't. It will still look for "AddressId" or "Address_Id" based on the type Address. So in your case where you added the extra Address type property, EF wants to try and work this out using unique FK names, hence the extra Address_ and numbers such in the expected FK names.

To solve your issue, you just need to direct EF as to which FK to use for which address:

public int? ADDRESS_ID { get; set; }
public int? MAILING_ADDRESS_ID { get; set; }

[ForeignKey("ADDRESS_ID")]
public virtual ADDRESS ADDRESS { get; set; }
[ForeignKey("MAILING_ADDRESS_ID")]
public virtual ADDRESS MAILING_ADDRESS { get; set;}

... and that should sort you out. The [ForeignKey] attribute can either be placed on the navigation property to point at the FK property, or on the FK property pointing at the navigation property. (in case you see examples the other way around)

Convention seems great when it works, but it's a pain in the ass when suddenly it doesn't. (Personally, I hate "magic" code(less) behavior.)

CodePudding user response：

There are many conflicting things in your codes. Probably the column names in the database and the property names in the C# classes do not match.

You should also check the T-SQL data types. Some properties are called ADDRESS in their classes. I recommend updating the question with more details to get more concrete answers.

Visit docs.microsoft for more information about data types.

You're probably making a mistake at this point:


    public virtual ADDRESS ADDRESS { get; set; }
    public virtual ADDRESS MAILING_ADDRESS { get; set;}

Virtual members need to be included at context. Read this.

Both sides are case sensitive (!) I recommend that you to learn the naming conventions too.