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 ?
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.
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.
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.
Member
17 Points
137 Posts
Foreign Key based on another column other than Id ef core model first
Jul 20, 2020 06:00 AM|roguenidb|LINK
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 ?
All-Star
53691 Points
24031 Posts
Re: Foreign Key based on another column other than Id ef core model first
Jul 20, 2020 11:43 AM|mgebhard|LINK
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.
Member
17 Points
137 Posts
Re: Foreign Key based on another column other than Id ef core model first
Jul 20, 2020 10:15 PM|roguenidb|LINK
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.
Member
17 Points
137 Posts
Re: Foreign Key based on another column other than Id ef core model first
Jul 20, 2020 10:18 PM|roguenidb|LINK
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
All-Star
53691 Points
24031 Posts
Re: Foreign Key based on another column other than Id ef core model first
Jul 20, 2020 10:39 PM|mgebhard|LINK
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.