SqlException：无法将值 NULL 插入到列 'CarId' 中，表 'AutoServiceDb.dbo.ServiceOrders';列不允许空值。插入失败

Question

当我想添加一个新条目时，它会抛出一个错误

SqlException：无法将值NULL插入列"CarId"、表"AutoServiceDb.dbo.ServiceOrders"；列不允许为null。INSERT失败。语句已终止。

下面是控制器代码、DTO、服务和Blazor控制器

using System;
using Microsoft.AspNetCore.Mvc;
using AutoService.Domain.Entities;
using AutoService.Application.Interfaces;
using System.Threading.Tasks;
using AutoService.API.Models;
using AutoMapper;
using AutoService.Application.DTOs;
namespace AutoService.API.Controllers
{
[ApiController]
[Route("[controller]")]
public class ServiceOrderController : ControllerBase
{
private readonly IServiceOrderService _serviceOrder;
private readonly IMapper _mapper;
public ServiceOrderController(IServiceOrderService serviceOrder , IMapper mapper)
{
_serviceOrder = serviceOrder;
_mapper = mapper;
}
[HttpPost("scheduleservice")]
public IActionResult ScheduleService([FromBody] ScheaduleViewModel model)
{
var order = _mapper.Map<ScheaduleViewModel, OrderDTO>(model);
var result = _serviceOrder.ScheduleService(order);
if (result == null) return BadRequest();
return Ok(result);
}
[HttpGet]
public async Task<IActionResult> GetService()
{
var result = await _serviceOrder.GetServicesAsync();
return Ok(result);
}
}
}

订单DTO

using System;
using System.Collections.Generic;
using System.Text;
namespace AutoService.Application.DTOs
{
public class OrderDTO
{
//create
public string ClientName { get; set; }
public string ClientPhone { get; set; }
public string CarMake { get; set; }
public string CarModel { get; set; }
public string CarPlate { get; set; }
public string CarColor { get; set; }
public DateTime ScheduledDate { get; set; }
}
}

服务

using AutoService.Application.DTOs;
using AutoService.Application.Interfaces;
using AutoService.Domain.Entities;
using AutoService.Infrastructure.Context;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace AutoService.Application.Services
{
public class ServiceOrderService : IServiceOrderService
{
private readonly ServicingContext _context;
public ServiceOrderService(ServicingContext context)
{
_context = context;
}
public async Task CreateService(ServiceOrder serviceOrder)
{
_context.ServiceOrders.Add(serviceOrder);
await _context.SaveChangesAsync();
}
public  async Task<List<ServiceOrder>> GetServicesAsync()
{
var result = await _context.ServiceOrders
.Include(c => c.Client)
.Include(k => k.Car)
.ToListAsync();
return result;
}
public ServiceOrder ScheduleService(OrderDTO orderDTO)
{
var client = _context.Clients.FirstOrDefault(c => c.FirstName == orderDTO.ClientName);
if (client == null)
{
_context.Clients.Add(new Client
{
FirstName = orderDTO.ClientName,
PhoneNumber = orderDTO.ClientPhone
});
}
var car = _context.Cars.Where((c => c.Makes == orderDTO.CarMake && c.Model == orderDTO.CarModel && c.Color == orderDTO.CarColor && c.LicensePlate == orderDTO.CarPlate)).FirstOrDefault();
if (car == null)
{
var entity = _context.Cars.Add(new Car
{
Makes = orderDTO.CarMake,
Model = orderDTO.CarModel,
Color = orderDTO.CarColor,
LicensePlate = orderDTO.CarPlate
});
}
var serviceOrder = _context.Add(new ServiceOrder
{
Client = client,
Car = car,
ScheduledTime = orderDTO.ScheduledDate,
CreationDate = DateTime.UtcNow
});
_context.SaveChanges();

return serviceOrder.Entity;
}
}

Blazor页面添加新订单

@page "/addServiceOrder"
@using System.Text.Json
@using System.Text
@using AutoService.API.Models

<link rel="stylesheet" href="css/bootstrap/bootstrap.min.css" />
<link href="css/site.css" rel="stylesheet" />
<form method="post">
<table>
<label ></label>
<tr>
<td>Client name:</td>
<td><input @bind="ClientName" /></td>
</tr>
<tr>
<td>Client phone</td>
<td><input @bind="ClientPhone" /></td>
</tr>
<tr>
<td>Car model</td>
<td><input @bind="CarModel" /></td>
</tr>
<tr>
<td>Car make</td>
<td><input @bind="CarMake" /></td>
</tr>
<tr>
<td>Car plate</td>
<td><input @bind="CarPlate" /></td>
</tr>
<tr>
<td>Car color</td>
<td><input @bind="CarColor" /></td>
</tr>
<tr>
<td>Scheduled date</td>
<td><input @bind="ScheduledDate" /></td>
</tr>
</table>
<a class="btn btn-secondary" href="serviceOrder">Back</a>
<button type="button" class="btn btn-primary" @onclick="AddNewOrder">&#10004;Add</button> @Alert
<p />
</form>
<h1>Add service order</h1>
<p />

@code {
@inject IHttpClientFactory clientFactory;
protected string ClientName { get; set; }
protected string ClientPhone { get; set; }
protected string CarMake { get; set; }
protected string CarModel { get; set; }
protected string CarPlate { get; set; }
protected string CarColor { get; set; }
protected DateTime ScheduledDate { get; set; }
protected string Alert { get; set; }

protected async Task AddNewOrder()
{
Alert = " ";
if (ClientName == string.Empty || ClientPhone == string.Empty || CarMake == string.Empty || CarModel == string.Empty || CarPlate == string.Empty || CarColor == string.Empty || ScheduledDate == DateTime.Now)
{
Alert = "Fill all fields!";
return;
}
Alert = "Added new order";
var _client = clientFactory.CreateClient();
ScheaduleViewModel _newOrder = new ScheaduleViewModel { ClientName = ClientName, ClientPhone = ClientPhone, CarMake = CarMake, CarModel = CarModel, CarPlate = CarPlate, CarColor = CarColor, ScheduledDate = ScheduledDate, };
var _content = new StringContent(JsonSerializer.Serialize(_newOrder), Encoding.UTF8, "application/json");
await _client.PostAsync("https://localhost:5001/ServiceOrder/scheduleservice", _content);
}
}

Blazor页面"ServiceOrder">

@page "/serviceOrder"
@using AutoService.Domain.Entities
@using System.Text.Json


<h1>Service order</h1>
<p />
<a class="btn btn-primary" href="addServiceOrder">&#10002;Add order</a>
<p />
@if (ServiceOrdersList == null)
{
<div>Loading...</div>
}
else
{
<table class="table">
<thead>
<tr>
<td>Id</td>
<td>Firs name owner</td>
<td>Car model</td>
<td>Creation </td>
<td>Scheduled time</td>
</tr>
</thead>
<tbody>
@foreach (var serviceOrder in ServiceOrdersList)
{
<tr>
<td>@serviceOrder.Id</td>
<td>@serviceOrder.Client.FirstName</td>
<td>@serviceOrder.Car.Model</td>
<td>@serviceOrder.CreationDate</td>
<td>@serviceOrder.ScheduledTime</td>
</tr>
}
</tbody>
</table>
}
@code {
public List<ServiceOrder> ServiceOrdersList { get; set; }
@inject IHttpClientFactory _clientFactory;
protected string info;
protected override async Task OnInitializedAsync()
{
var client = _clientFactory.CreateClient();
var result = await client.GetAsync("https://localhost:5001/serviceOrder");
info = await result.Content.ReadAsStringAsync();
var options = new JsonSerializerOptions
{
IgnoreNullValues = true,
PropertyNameCaseInsensitive = true
};
ServiceOrdersList = JsonSerializer.Deserialize<List<ServiceOrder>>(info, options);

}
}

在此处输入图像描述

车型

using System;
using System.Collections.Generic;
using System.Text;
namespace AutoService.Domain.Entities
{
public class Car
{
public int Id { get; set; }
public string Makes { get; set; }
public string Model { get; set; }
public string Color { get; set; }
public string LicensePlate { get; set; }

}
}

Answer 1

看起来您没有将新的"实体"分配给您试图创建的"汽车"对象。。。

var car = _context.Cars.Where(...).FirstOrDefault();
if (car == null)
{
var entity = _context.Cars.Add(new Car
{
...
});
}
var serviceOrder = _context.Add(new ServiceOrder
{
Client = client,
Car = car, // <-- 'car' is still null when you assign it's value to the service order
...
}

试着添加这样的东西：

var car = _context.Cars.Where(...).FirstOrDefault();
if (car == null)
{
car = new Car // Assign the new car to the null car variable.
{
...
};
_context.Cars.Add(car); // Then add it to the context.
}

由于"ServiceOrder.Car"属性在尝试将其插入SQL时为null，因此数据库尝试将null插入CarId。