locked
SqlException: Invalid column name 'SemesterId'. after model modified , migration and update database RRS feed

  • Question

  • User-1202579601 posted

    public class Subject{
    public int Id {get;set;}
    public string Name { get; set; }
    public string Code { get; set; }
    public int Units {get;set;}
    public string StaId { get; set; }
    public string SemId{ get; set; }

    //public int SemesterId{get;set;}
    }

    I had removed the SemesterId and after model modified , migration and update database done i am getting this error.

    Monday, March 30, 2020 8:04 AM

Answers

  • User-1202579601 posted

    I traced for two days everywhere. I just created a new database. Everything works fine.

    SqlException: Invalid column name 'SemesterId'.

    System.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__122_0(Task<SqlDataReader> result)

    I am naive to how model builder, migrations work internally.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 31, 2020 6:40 AM

All replies

  • User475983607 posted

    The error is very clear.  Unfortunately, the shared code is incomplete and it is not clear how you removed the column.  If you removed the column in SSMS then you made a fundamental Code First mistake.  Always use Code First to update the database.

    Monday, March 30, 2020 10:51 AM
  • User-1202579601 posted

    I use code first approach always. I am using  many models. 2-3 revision were done successfully. I have checked in database, no column SemesterId exists.

    Monday, March 30, 2020 11:08 AM
  • User475983607 posted

    I use code first approach always. I am using  many models. 2-3 revision were done successfully. I have checked in database, no column SemesterId exists.

    Your response is not helpful at all.  The community can only see the code and exception messages that you share.  At this point you have shared nothing...  

    Anyway, the error message is very very clear.  The SQL script passed to SQL server contains the SemesterId but the column does not exist in the database.  ASP.NET exception messages are very detailed show the line number that caused the exception.  I recommend reviewing the code that runs before the line that causes the exception.   I assume this code starts in an Action and maybe calls business logic.  

    Dude, please debug your code or provide relevant code for community support.  Debugging yourself is best since you  can reproduce this exception and the community cannot.

    Monday, March 30, 2020 11:35 AM
  • User-1202579601 posted

    public async Task<IActionResult> Index(string serctype)
    {
    var subj = _context.Subjects
    .AsNoTracking();
    if (!String.IsNullOrEmpty(serctype))
    {
    HttpContext.Session.SetString("SearchString", serctype);
    subj = subj.Where(s => s.Code.Contains(serctype))
    .OrderBy(s => s.Code)
    .AsNoTracking();
    }
    return View(await subj.ToListAsync());
    }

    not much in code to debug, i am naive to the working of migrations ans model builders so i posted.

    Monday, March 30, 2020 3:14 PM
  • User475983607 posted

    If I assume the error (SqlException: Invalid column name 'SemesterId') happens when the highlight code executes the query then the code generator is sending SemeserId in the select.

    public async Task<IActionResult> Index(string serctype)
    {
    	var subj = _context.Subjects.AsNoTracking();
    	if (!String.IsNullOrEmpty(serctype))
    	{
    		HttpContext.Session.SetString("SearchString", serctype);
    		subj = subj.Where(s => s.Code.Contains(serctype)).OrderBy(s => s.Code).AsNoTracking();
    	}
    	return View(await subj.ToListAsync());
    }

    The only logical explanation I can come up with is you deleted the SemesterId from the table directly and not through Code First.  You did state that the column does not exist and the error message confirms the column is missing.  Next, you commented the SemesterId in a non-entity class thiking it would fix the issue.  Again this is only a guess.  For all I know the error is happening somewhere else as you did not share what line of code caused the exception.

    IMHO, the problem you are facing is learning how to troubleshoot and debug.   Usually, when you find the line of code that causes an exception then you figure out how to fix the error.

    Try right clicking on Subjects in...

    var subj = _context.Subjects.AsNoTracking();

    ... and go to the definition.   Do you see SemesterId? 

    Monday, March 30, 2020 3:43 PM
  • User711641945 posted

    Hi pmdrait,

    >I had removed the SemesterId and after model modified , migration and update database done i am getting this error.

    > I have checked in database, no column SemesterId exists.

    From your description,it seems you have updated the database successfully,right?Be sure that you firstly remove the SemesterId in your model and then run the command `add-migration` `update-database` successfully.

    If you still makes error after doing such operation,please set breakpoints to debug your code to see which line makes error.Then share your action and point out which line in this action make such error.

    Besides,could you share your DbContext and other related models?

    For how to set breakpoints to debug your code,refer to:

    https://docs.microsoft.com/en-us/visualstudio/debugger/debugger-feature-tour?view=vs-2019#set-a-breakpoint-and-start-the-debugger

    For similar issue,refer to:

    https://entityframeworkcore.com/knowledge-base/52526786/getting-invalid-column-name--employmenttypeentityemploymenttypeid-in-entity-framework-core

    Best Regards,

    Rena

    Tuesday, March 31, 2020 6:03 AM
  • User-1202579601 posted

    I traced for two days everywhere. I just created a new database. Everything works fine.

    SqlException: Invalid column name 'SemesterId'.

    System.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__122_0(Task<SqlDataReader> result)

    I am naive to how model builder, migrations work internally.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 31, 2020 6:40 AM