时间戳

首页 > 解决方案 > SqlException：无法将值 NULL 插入列“CarId”、表“AutoServiceDb.dbo.ServiceOrders”；列不允许空值。插入失败

c# - SqlException：无法将值 NULL 插入列“CarId”、表“AutoServiceDb.dbo.ServiceOrders”；列不允许空值。插入失败

问题描述

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

SqlException：无法将值 NULL 插入列“CarId”、表“AutoServiceDb.dbo.ServiceOrders”；列不允许空值。插入失败。该语句已终止。

下面是控制器代码、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 页面 AddNewOrder

@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; }



    }
}

标签： c#sql-serverblazor

解决方案

看起来您没有将新的“实体”分配给您尝试创建的“汽车”对象......

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 插入 CarId。

推荐阅读