Need help on table design with Entity Framework Core

asp.net-core asp.net-mvc c# entity-framework entity-framework-core

Question

I am developing a simple web application where a doctor is adding multiple prescription records for patients and will select multiple drugs while doing prescription. So one patient has multiple prescription and one prescription has multiple selected drugs. I have taken one another table patientrecords for reporting purpose/Normalization perspective where I am referencing patientID and PrescriptionID.

  • One patient --> many prescriptions --> one to many relationship
  • One prescriptions -> many drugs --> one to many relationship

Below is the model for patient, prescription and drugs, PatientRecord table.

PatientModel

DrugModel

PrescriptionModel

PatientRecordModel

While running migration, I get this error:

Error Number:1769,State:1,Class:16
Foreign key 'FK_Drugs_Prescriptions_PrescriptionID' references invalid column 'PrescriptionID' in referencing table 'Drugs'.

I am confused with explanation of one to many relationships on Microsoft website.

Can anyone help me with it?

1
0
4/16/2020 12:02:02 PM

Popular Answer

There are two ways to configure the relationships in EF Core

  • Conventions :By default, a relationship will be created when there is a navigation property discovered on a type. Not applicable to many-to-many relationship

  • Fluent API:you start by identifying the navigation properties that make up the relationship. HasOne or HasMany identifies the navigation property on the entity type you are beginning the configuration on. HasOne/WithOne are used for reference navigation properties and HasMany/WithMany are used for collection navigation properties.

From your screenshots and the benjamin suggested, you could configure the model like below

Patient - Prescription --> one to many relationship Prescription - Drug --> many to many relationship

public class Prescription
{
    public int PrescriptionId { get; set; }
    [Required]
    public string Description { get; set; }
    [Required]
    public DateTime PrescriptionDate { get; set; }

    public int PatientId { get; set; }

    public Patient Patient { get; set; }

    public ICollection<DrugPrescription> DrugPrescriptions { get; set; }
}
public class Drug
{
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }
    [Required]
    public int CurrentStock { get; set; }
    public int DrugCost { get; set; }
    public string Description { get; set; }

    public ICollection<DrugPrescription> DrugPrescriptions { get; set; }
}

//represent a many-to-many relationship by including an entity class for 
//the join table and mapping two separate one-to-many relationships.
 public class DrugPrescription
{
    public int DrugId { get; set; }
    public Drug Drug { get; set; }

    public int PrescriptionId { get; set; }
    public Prescription Prescription { get; set; }
}


//DbContext
public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {}

    public DbSet<Patient> Patient { get;set; }
    public DbSet<Drug> Drug { get;set; }
    public DbSet<Prescription> Prescription { get;set; }

    public DbSet<PatientRecord> PatientRecord { get; set; }


    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        #region Drug-Prescription Many-to-Many
        builder.Entity<DrugPrescription>()
            .HasKey(dp => new { dp.DrugId, dp.PrescriptionId });

        builder.Entity<DrugPrescription>()
            .HasOne(dp => dp.Prescription)
            .WithMany(p => p.DrugPrescriptions)
            .HasForeignKey(dp => dp.PrescriptionId)
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<DrugPrescription>()
            .HasOne(dp => dp.Drug)
            .WithMany(d => d.DrugPrescriptions)
            .HasForeignKey(dp => dp.DrugId)
            .OnDelete(DeleteBehavior.Restrict);
        #endregion

    }
}
0
4/17/2020 9:38:47 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow