Assigning Foreign Keys in a Razor Pages App

0

I am trying to assign a value to a foreign key in an application that I am working on. However, whenever I select a value in the dropdown and click submit, the following SQL error appears:

    SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint
    "FK_Printer_Printer_Model_ModelID". The conflict occurred in database 
    "_360_Office_ManagementContext-577009b7-1da0-4f2f-8dbc-3196d6e7a3d9", 
    table "dbo.Printer_Model", column 'ID'. 
    The statement has been terminated.

The database table looks like this. The foreign keys are supposed to reference the ID field of their respective tables:

CREATE TABLE [dbo].[Printer] (
    [ID]                 INT           IDENTITY (1, 1) NOT NULL,
    [MachineID]          NVARCHAR (50) NULL,
    [Log_ID]             INT           NOT NULL,
    [Lease_ID]           INT           NOT NULL,
    [Serial_Number]      NVARCHAR (50) NULL,
    [Solution]           NVARCHAR (10) NULL,
    [Additional_Info]    NVARCHAR (50) NULL,
    [InstallationDate]   DATETIME2 (7) NOT NULL,
    [Mono_Click]         REAL          NOT NULL,
    [Colour_Click]       REAL          NOT NULL,
    [Minimum]            INT           NOT NULL,
    [Parts_Warranty]     INT           NOT NULL,
    [IT_Support]         INT           NOT NULL,
    [Contract_Type]      NVARCHAR (50) NULL,
    [SiteID]             INT           NULL,
    [ModelID]            INT           NULL,
    [Previous_AddressID] INT           NULL,
    CONSTRAINT [PK_Printer] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Printer_Printer_Model_ModelID] FOREIGN KEY ([ModelID]) REFERENCES [dbo].[Printer_Model] ([ID]),
    CONSTRAINT [FK_Printer_Address_Previous_AddressID] FOREIGN KEY ([Previous_AddressID]) REFERENCES [dbo].[Address] ([ID]),
    CONSTRAINT [FK_Printer_Company_Site_SiteID] FOREIGN KEY ([SiteID]) REFERENCES [dbo].[Company_Site] ([ID])
);


GO
CREATE NONCLUSTERED INDEX [IX_Printer_ModelID]
    ON [dbo].[Printer]([ModelID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Printer_SiteID]
    ON [dbo].[Printer]([SiteID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Printer_Previous_AddressID]
    ON [dbo].[Printer]([Previous_AddressID] ASC);

The method that assigns the dropdown values is as follows:

public SelectList AddressSelectList { get; set; }
    public SelectList ModelSelectList { get; set; }
    public SelectList SiteSelectList { get; set; }

    public void PopulateAddressDropDownList(_360_Office_ManagementContext _context,
        object selectedAddress = null)
    {
        var addressQuery = from d in _context.Address
                            orderby d.Address_1
                            select d;

        AddressSelectList = new SelectList(addressQuery.AsNoTracking(),
            "ID", "Address_1", selectedAddress);
    }

    public void PopulateModelDropDownList(_360_Office_ManagementContext _context,
        object selectedModel = null)
    {
        var siteQuery = from d in _context.Printer_Model
                          orderby d.Model
                          select d;

        ModelSelectList = new SelectList(siteQuery.AsNoTracking(),
            "ID", "Model", selectedModel);
    }
    public void PopulateSiteDropDownList(_360_Office_ManagementContext _context,
        object selectedSite = null)
    {
        var siteQuery = from d in _context.Company_Site
                        orderby d.Department
                        select d;

        ModelSelectList = new SelectList(siteQuery.AsNoTracking(),
            "ID", "Department", selectedSite);
    }
public IActionResult OnGet()
        {
            PopulateAddressDropDownList(_context);
            PopulateSiteDropDownList(_context);
            PopulateModelDropDownList(_context);

            return Page();
        }

And the HTML that displays the dropdown is as follows:

<div class="form-group">
                <label asp-for="Printer.Previous_Address" class="control-label"></label>
                <select asp-for="Printer.Previous_AddressID" name="PrevAddressID" class="form-control" asp-items="Model.AddressSelectList" ><option value=""> Select Previous Address</option></select>
                <span asp-validation-for="Printer.Previous_AddressID" class="text-danger"></span>
            </div>

Any help in resolving this issue so that the relationships show up in the database would be greatly appreciated.

EDIT: There appears to be 2 separate errors at play, an SQL exception and a DBUpdateException. I have included both of the sets of raw exception details below. The code that seems to be causing the issue is in some of the base libraries which I haven't touched at all so its confusing me a bit.

SQLException details:

Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customer_Address_AddressID". The conflict occurred in database "_360_Office_ManagementContext-577009b7-1da0-4f2f-8dbc-3196d6e7a3d9", table "dbo.Address", column 'ID'.
The statement has been terminated.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:10f5732e-a540-4add-af22-746954b3f6f8
Error Number:547,State:0,Class:16

DbUpdateException details:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customer_Address_AddressID". The conflict occurred in database "_360_Office_ManagementContext-577009b7-1da0-4f2f-8dbc-3196d6e7a3d9", table "dbo.Address", column 'ID'.
The statement has been terminated.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:10f5732e-a540-4add-af22-746954b3f6f8
Error Number:547,State:0,Class:16
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at _360_Office_Management.Pages.AccountsManagement.Customers.CreateModel.OnPostAsync() in C:\Users\Samuel\Desktop\Uni\3YP\360 Office Management\360 Office Management\Pages\AccountsManagement\Customers\Create.cshtml.cs:line 42
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.GenericTaskHandlerMethod.Convert[T](Object taskAsObject)
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.GenericTaskHandlerMethod.Execute(Object receiver, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync()
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync()
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Edit 2 - I have found the method that is causing the issue and have included it below. This method is the standard creation code so I am still none the wiser as to why it is causing the issue assigning Foreign Keys.

public async Task<IActionResult> OnPostAsync()
        {
            if (!ModelState.IsValid)
            {
                return Page();
            }

            _context.Customer.Add(Customer);
            await _context.SaveChangesAsync();

            return RedirectToPage("./Index");
        }
sql-server
entity-framework
asp.net-core
razor-pages
asked on Stack Overflow May 29, 2020 by Samuel Rose • edited Jun 2, 2020 by Samuel Rose

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0