Mapping Table in DB first without foreign key

Question

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.

Try EF Core Power Tools

ErikEJ
– ErikEJ

2023-01-10 17:25:43 +00:00
Commented Jan 10, 2023 at 17:25 — ErikEJ
– ErikEJ, Commented Jan 10, 2023 at 17:25

Okan Karadag · Accepted Answer · 2023-01-10 17:27:04Z

1

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);

answered Jan 10, 2023 at 17:27

Okan Karadag

3,1051 gold badge16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shahreyar Butt Over a year ago

So what do I have to do I don't understand your answer?

Okan Karadag Over a year ago

you should add Id column to ServicePlanDetail.

Shahreyar Butt Over a year ago

But what if I cannot make any changes to the DB because in this way i have to add a primary key column Id to the table , and if i add that would i have to mention it in the function while mapping

Steve Py · Accepted Answer · 2023-01-10 21:01:24Z

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.

Collectives™ on Stack Overflow

Mapping Table in DB first without foreign key

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related