SQLite 异常：SQLite 错误 19："Foreign Key Constraint failed"

Question

我已经在线查看了这个问题(SQLite EF Core - 'FOREIGN KEY约束失败')，我看过以前的例子，我以前这样做过，但我仍然无法解决这个问题下面是外键、DB和启动的模型类的代码。

  public class AppointmentBooking
{
    public int Id { get; set; }
    public DateTime StartDateTimeBooking { get; set; }
    public DateTime TimeOfBooking { get; set; } - Potentially use this
    public DateTime EndDateTimeBooking { get; set; }
    public Doctor NameOfDoctor { get; set; }
    public PreferenceOfAttendance PreferenceOfAttendence {get; set;}
    public string DetailOfBooking { get; set; }
    //EF Dependant Relationship Appointment belongs to a patient
    public int PatientId { get; set; }
    //Navigation property
    public Patient Patient { get; set; }
    //EF Dependant Relationship Appointment belongs to a doctor
}

Services Service DB和我只包括在调试中出现X的地方:

 public class NameOferviceDb : NameOfService
{
    private readonly DatabaseContext db;
    public NameOfServiceDb()
    {
        db = new DatabaseContext(); 
    }
    public void Initialise()
    {
       db.Initialise(); 
    }
  public AppointmentBooking AddAppointmentBooking(AppointmentBooking ab)
    {
        var existing = GetPatientById(ab.Id);
        if (existing != null)
        {
            return null;
        }
        else
        {
            var booking = new AppointmentBooking
            {
                PatientId = ab.Id,
                StartDateTimeBooking = ab.StartDateTimeBooking,
                NameOfDoctor = ab.NameOfDoctor,
                PreferenceOfAttendence = ab.PreferenceOfAttendence,
                DetailOfBooking = ab.DetailOfBooking,
            };
            db.Bookings.Add(booking); //Add new booking to the db
            db.SaveChanges(); //Savechanges and return to the db 
            return booking; // return booking 
        }
    }

Startup.cs

  public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }
    public IConfiguration Configuration { get; }
    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {   
        
        // ** Add Cookie Authentication via extension method **
        //services.AddCookieAuthentication();
        // ** Add Cookie and Jwt Authentication via extension method **
        services.AddCookieAndJwtAuthentication(Configuration);
        // ** Enable Cors for and webapi endpoints provided **
        services.AddCors();
        
        // Add UserService to DI - change to use real UserService           
        services.AddTransient<NameOfService,NameOfServiceDb>();
        // ** Required to enable asp-authorize Taghelper **            
        services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>();
        
        services.AddControllersWithViews();
    }
    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IWebHostEnvironment env, IServiceProvider provider)
    {
        if (env.IsDevelopment())
        {
            //app.UseDeveloperExceptionPage();

            // seed users - using service provider to get UserService from DI
            Seeder.Seed(provider.GetService<INameOfService>());
        }
        else
        {
            app.UseExceptionHandler("/Home/Error");
            // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
            app.UseHsts();
        }
        app.UseHttpsRedirection();
        app.UseStaticFiles();
        app.UseRouting();
        // ** configure cors to allow full cross origin access to any webapi end points **
        app.UseCors(c => c.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
        // ** turn on authentication/authorisation **
        app.UseAuthentication();
        app.UseAuthorization();
        app.UseEndpoints(endpoints =>
        {
            endpoints.MapControllerRoute(
                name: "default",
                pattern: "{controller=Home}/{action=Index}/{id?}");
        });
    }
}

数据库上下文

public class DatabaseContext :DbContext
{
    public DbSet<Patient> Patients { get; set; }
    public DbSet<AppointmentBooking> Bookings { get; set; }
   
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder                  
            .UseSqlite("Filename=data.db")
            //.LogTo(Console.WriteLine, LogLevel.Information) // remove in production
            //.EnableSensitiveDataLogging()                   // remove in production
            ;
    }
    public void Initialise()
    {
        Database.EnsureDeleted();
        Database.EnsureCreated();
    }
}

Answer 1

问题是您分配PatientId = ab.Id，但我确信ab.Id = 0并且没有具有此Id的患者。这就是它抛出异常的原因。试试这个

       
var existingPatient =  db.Patients.Any(b=> b.PatientId==ab.PatientId);
        if (!existingPatient)
        {
            return null;
        }      
            var booking = new AppointmentBooking
            {
                PatientId = ab.PatientId,
                .....
            };
            db.Bookings.Add(booking); //Add new booking to the db
            db.SaveChanges(); //Savechanges and return to the db 
            return booking;