Mapping Table in DB first without foreign key-CodePudding

I have a program written with a database-first approach; I have a table ServicePlan and another ServicePlanDetails. They are not mapped to each other, but they have a common column PlanId; a servicePlan can contain multiple ServicePlanDetails like a list of it.

I don't want to make any change to the database, but I want to map them as well. How can I do this? Does doing this within the method of on model creating will do the work for me and will not change anything in the database? I have tried this but could get the result.

For simplicity, I have just added few columns and their mapping and not all of them:

public partial class ServicePlan
{
    public ServicePlan()
    {
        ServicePlanDetails = new HashSet<ServicePlanDetail>();
    }

    public long PlanId { get; set; }
    public decimal PhoneId { get; set; }
    public byte? NLines { get; set; }
    public DateTime? DateStart { get; set; }
    public DateTime? DateEnd { get; set; }
    public virtual ICollection<ServicePlanDetail> ServicePlanDetails { get; set; }
}

public partial class ServicePlanDetail
{
    public long PlanId { get; set; }
    public string? ServCode { get; set; }
    public string? CountryCode { get; set; }
    public bool? IsPlan { get; set; }
    public decimal? Cost { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ServicePlan>(entity =>
    {
        entity.HasKey(e => e.PlanId).HasName("PK_UsersPlan");

        entity.ToTable("ServicePlan");

        entity.HasIndex(e => e.VideoTronId, "IDX_VTID").HasFillFactor(80);

        entity.HasIndex(e => new { e.PhoneId, e.IsApproved }, "Ix_SrvcPlan").HasFillFactor(80);

        entity.Property(e => e.Zone).HasMaxLength(50);
        entity.HasMany(p => p.ServicePlanDetails)
            .WithOne()
            .HasPrincipalKey(p => p.PlanId)
            .HasForeignKey(p => p.PlanId);
    });
}

The error I get is :

Unable to determine the relationship represented by navigation 'ServicePlan.ServicePlanDetails' of type 'ICollection'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.'

I want to get the serviceplandetails with the same planid as the serviceplan into a list in the serviceplan.

CodePudding user response：

PlanId cannot be both foreign and principal key for one to many.

public partial class ServicePlanDetail
{
    public long Id { get; set; }
    public long PlanId { get; set; }
    public string? ServCode { get; set; }
    public string? CountryCode { get; set; }
    public bool? IsPlan { get; set; }
    public decimal? Cost { get; set; }
}

Configuration

 entity.HasMany(p => p.ServicePlanDetails)
            .WithOne()
            .HasPrincipalKey(p => p.PlanId)
            .HasForeignKey(p => p.PlanId);

CodePudding user response：

If in the database a Plan can have many ServicePlanDetails, and you link them by Plan ID, how do you differentiate one ServicePlanDetail against that Plan from another? What makes two ServicePlanDetail records unique? That is the crux of your problem. Your FK mapping is correct, but it won't work if PlanId is the PK on ServicePlanDetail. PKs must uniquely identify a single record. For instance if your plan is associated to service plan details applying to various users where multiple users reference the same plan and there is a UserID on ServicePlanId, the PK should be a composite of PlanId UserId.

As a DB-First approach the database should already have the PKs and constraints set up. You just set up EF keys and relationship types to match that.

Now if the ServicePlanDetail's PK is declared as just PlanId, then the answer is that the relationship between Plan and ServicePlanDetail is 1-to-1, not 1-to-many. This becomes a .HasOne(p => p.ServicePlanDetail).WithOne(sp => sp.Plan) and there's really nothing you can do about that without altering the data relationships. You cannot magically change the relationship that EF will use if the underlying database schema cannot support that relationship.