locked
Foreign Key based on another column other than Id ef core model first RRS feed

  • Question

  • User-183185495 posted

    I have a table that I am using for depended dropdowns but I am having an issue I am getting a key violation on my table because I am using a secondary column to group my dropdowns.

    public void GetEnforcementCats() {
    
    List<SelectListItem> list = new List<SelectListItem>();
    var items = _context.EnfCats.Where(w => w.isActive==true && w.isDeleted==false).ToList();
    foreach (var item in items) {
    list.Add(new SelectListItem { Text = item.Description.ToString(), Value = item.CategoryId.ToString() });
    }
    ViewBag.EnforcementCats = list;
    }

    But of course when I look in the table of ef cats and then try to save against that in the error that I get is the following.

    DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
    SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MISobject_EnfCats_Enf_Cat". The conflict occurred in database "MISSystem", table "dbo.EnfCats", column 'Id'. The statement has been terminated.

    Or should I just do away with the foreign key in this case and just save the int Value ?

     

    Monday, July 20, 2020 6:00 AM

All replies

  • User475983607 posted

    The design shown is hard to understand without more information. The error message states the constraint error is on dbo.EnfCats.Id field not categoryId.  

    I assume the categoryId will repeat in the dbo.EnfCats table and dbo.EnfCats.Id is a primary key.  That means the query to build the select list will contain duplicate categoryIds  with different Descriptions.  That's not going to work.  

    Monday, July 20, 2020 11:43 AM
  • User-183185495 posted

    Hi Again,

     The issue we have is that id's are not always guaranteed when we insert into a customers database so I am using category Id to lookup the data in a foreign table., they are depenedent dropdowns so that category id is passed to the second query but as you see cause it brakes the foreign key constraint it wont allow me to save into my model.

    Monday, July 20, 2020 10:15 PM
  • User-183185495 posted

    I want to be stil able to get the description from the foreign table when I need to display the information in a gird for example

    Monday, July 20, 2020 10:18 PM
  • User475983607 posted

    roguenidb

    I want to be stil able to get the description from the foreign table when I need to display the information in a gird for example

    My assumption is categotyId is has a foreign key relationship to another table.  Therefore the categoryId is the selected value not the a key in the options list as currently coded.  The option key should be the primary key of the EnfCats table which I assume is dbo.EnfCats.Id.

    Show your DDL rather than making us guess.

    Monday, July 20, 2020 10:39 PM