locked
FK Column error - MVC Core 2 - SqlException: Invalid column name 'DepartmentID1'. RRS feed

  • Question

  • User-973886032 posted

    hi guys,
    I have two simple classes with a Primary Key and FK relationship.

    In the Sub_Department class, I get the error

    > SqlException: Invalid column name 'DepartmentID1'.
    System.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__108_0(Task<SqlDataReader> result)


    Here is my code

    public class Departments_Category_Registration
    {
    [Key]
    public int CategoryID { get; set; } // This is the PK
    public Departments DepartmentID { get; set; } // this is a FK


    and my Departments class with the PK

    public class Departments
    {
    [Key]
    public int DepartmentID { get; set; }
    [Display(Name ="Departments")]
    [Required]
    public string Department_Name { get; set; }
    [Display(Name ="Departments")]
    [Required]
    public string Description_Long { get; set; }
    [Display(Name ="Short Description_Short")]
    [Required]
    public string Description_Short { get; set; }
    public bool IsEnabled { get; set; }
    }



    however if I update my db to change the FK to department1 it works, funny thing is that I never used 1 in my code, I had changed it from ID to DepartmentID (if that helps)

    Secondly, I thought I ask, if its wise to create a separate controller for each model or not ?

    Ehi

    Monday, March 26, 2018 2:03 PM

Answers

  • User1751268424 posted

    Hi,

    Look at this code:

    public class Departments_Category_Registration
    {
    [Key]
    public int CategoryID { get; set; } // This is the PK
    public Departments DepartmentID { get; set; } // this is a FK                 THIS IS NOT FK because it should be "int"

    On DepartmentID type is Departments, which is your model, since you use this, EF core automatically generate your FK, becouse DeparmentID is not int (type of your Departments Class Key). EF automatically generate FK with department1.
    You may change your code like this:

    public class Departments_Category_Registration
    {
    [Key]
    public int CategoryID { get; set; } // This is the PK
    public int DepartmentID { get; set; } // this is a FK
    public Departments Departments { get; set; }

    or you can just use FK with the auto generate by EF Core

    Have fun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 26, 2018 3:33 PM

All replies

  • User475983607 posted

    however if I update my db to change the FK to department1 it works, funny thing is that I never used 1 in my code, I had changed it from ID to DepartmentID (if that helps)

    My best guess is you changed property names but forgot to create a migration and update the database.  Maybe you need to back out a previous migration.  when a "1" is appended to the property usually indicates you already created the property with that name.

    Secondly, I thought I ask, if its wise to create a separate controller for each model or not ?

    A bit more context can help answer this question.

    For Web API it makes sense because you are generally dealing with one type.  This is not always the case though.  

    In MVC, you're generally dealing with models and view models.  View models are dependant on the UI and, by definition, are different for each View.  Models are usually based on a DB table and used to populate a view model. 

    Therefore, It is common to have a single controller with many different view models.  You could create controller for each model but that would create a lot of controllers and, IMHO, make the code difficult to maintain. 

    Monday, March 26, 2018 2:41 PM
  • User1751268424 posted

    Hi,

    Look at this code:

    public class Departments_Category_Registration
    {
    [Key]
    public int CategoryID { get; set; } // This is the PK
    public Departments DepartmentID { get; set; } // this is a FK                 THIS IS NOT FK because it should be "int"

    On DepartmentID type is Departments, which is your model, since you use this, EF core automatically generate your FK, becouse DeparmentID is not int (type of your Departments Class Key). EF automatically generate FK with department1.
    You may change your code like this:

    public class Departments_Category_Registration
    {
    [Key]
    public int CategoryID { get; set; } // This is the PK
    public int DepartmentID { get; set; } // this is a FK
    public Departments Departments { get; set; }

    or you can just use FK with the auto generate by EF Core

    Have fun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 26, 2018 3:33 PM
  • User-973886032 posted

    I see

    thanks a lot

    Do you have any advise on creating multiple controllers or append controller/models actions to a hierachy of models. e.g

    Department class has 

    (1) Categories

    (2) Sub Categories

    Do I create a controller for each or append ?

    Monday, March 26, 2018 10:03 PM
  • User1120430333 posted

    I see

    thanks a lot

    Do you have any advise on creating multiple controllers or append controller/models actions to a hierachy of models. e.g

    Department class has 

    (1) Categories

    (2) Sub Categories

    Do I create a controller for each or append ?

    https://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

    <copy>

    An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic. For example, if you are using the Microsoft Entity Framework to access your database, then you would create your Entity Framework classes (your .edmx file) in the Models folder.+

    A view should contain only logic related to generating the user interface. A controller should only contain the bare minimum of logic required to return the right view or redirect the user to another action (flow control). Everything else should be contained in the model.

    In general, you should strive for fat models and skinny controllers. Your controller methods should contain only a few lines of code. If a controller action gets too fat, then you should consider moving the logic out to a new class in the Models folder.

    <end>

    Monday, March 26, 2018 11:12 PM