I'm having some trouble with Microsoft .NET Entity Framework Core. I am converting a project from database first to code first. The project is very old, so it also needs to be uplifted from .NET Framework v4.8 to .NET 8 and it is moving from EF6 to EF Core. I ran some kind of a project upgrade thing to create 2 new projects - the data model and the forms/UI stuff to use .NET 8 framework. I also created a completely separate solution and installed EF Core Power Tools. In this new project I requested that my database be Reverse Engineered. With the classes that were produced I went back to my new project folders and added the data entities, fixed up the namespace and so on. I am trying to do everything through FluentAPI and not use "conventions". I have not set up any migration stuff, I thought I would save that for last.
It compiles and runs, but now that I am getting into the complicated JOINs the Entity Framework is generating columns (e.g. PlanId, DDAReferralId, DDAReferralId1, etc.) that don't exist. I'll attach a diagram of my model, but based on the SQL query it seems that the trouble is in this area:
modelBuilder.Entity<DDACaseNote>(entity => { entity.ToTable("CaseNotes_DDACaseNote", tableBuilder => tableBuilder.Property(d => d.Id).HasColumnName("Id")) .HasBaseType<CaseNote>();
entity.HasIndex(e => e.DDAAnnualPlanId, "IX_FK_DDACaseNotePlan");
entity.Property(e => e.Id).ValueGeneratedNever(); entity.Property(e => e.DDAAnnualPlanId).HasColumnName("DDAAnnualPlanId"); entity.Property(e => e.ActionStep).HasColumnName("ActionStep");
entity.HasOne(d => d.DDAAnnualPlan) .WithMany(p => p.DDACaseNotes) .HasForeignKey(d => d.DDAAnnualPlanId) .HasConstraintName("FK_DDACaseNotePlan");
entity.HasOne(d => d.IdNavigation) .WithOne(p => p.DDACaseNote) .HasForeignKey<DDACaseNote>(d => d.Id) .HasConstraintName("FK_DDACaseNote_inherits_CaseNote"); });
The part that I hope works someday goes like this:
var clientCaseNotes = _dbContext.CaseNotes .Include(c => c.DDACaseNote) .Where(cn => cn.ClientId == 42) .ToList();
Which results in a query like this, the mystery colums are highlighted:
SELECT [c].[Id], [c].[ApprovedById], [c].[ClientId], [c].[ClientOtherHours], [c].[ClientPaidHours], [c].[ClientVolunteerHours], [c].[CreatedDate], [c].[IsDDAold], [c].[IsFCS], [c].[Note], [c].[StaffHours], [c].[StaffHoursTypeId], [c].[StaffId], [c].[Timestamp], [c0].[ActionStep], [c0].[DDAAnnualPlanId], [c0].[DDAReferralId], [c0].[DDAReferralId1], [c0].[DDAReferralId2], [c0].[PlanId], CASE WHEN [c0].[Id] IS NOT NULL THEN N'DDACaseNote' END AS [Discriminator], [s].[Id], [s].[ApprovedById], [s].[ClientId], [s].[ClientOtherHours], [s].[ClientPaidHours], [s].[ClientVolunteerHours], [s].[CreatedDate], [s].[IsDDAold], [s].[IsFCS], [s].[Note], [s].[StaffHours], [s].[StaffHoursTypeId], [s].[StaffId], [s].[Timestamp], [s].[ActionStep], [s].[DDAAnnualPlanId], [s].[DDAReferralId], [s].[DDAReferralId1], [s].[DDAReferralId2], [s].[PlanId] FROM [CaseNotes] AS [c] LEFT JOIN [CaseNotes_DDACaseNote] AS [c0] ON [c].[Id] = [c0].[Id] LEFT JOIN ( SELECT [c1].[Id], [c1].[ApprovedById], [c1].[ClientId], [c1].[ClientOtherHours], [c1].[ClientPaidHours], [c1].[ClientVolunteerHours], [c1].[CreatedDate], [c1].[IsDDAold], [c1].[IsFCS], [c1].[Note], [c1].[StaffHours], [c1].[StaffHoursTypeId], [c1].[StaffId], [c1].[Timestamp], [c2].[ActionStep], [c2].[DDAAnnualPlanId], [c2].[DDAReferralId], [c2].[DDAReferralId1], [c2].[DDAReferralId2], [c2].[PlanId] FROM [CaseNotes] AS [c1] INNER JOIN [CaseNotes_DDACaseNote] AS [c2] ON [c1].[Id] = [c2].[Id] ) AS [s] ON [c].[Id] = [s].[Id] WHERE [c].[ClientId] = 42
DDAReferalId is not even defined in the project since I've redefined the only table that references it, so I don't know where that even comes from? If you look at the PDF I've attached, you'll see that it's a fairly complex relationship, but I've gotten quite a few queries to work, but this one is buzzed up and the frustrating part is I don't even know where to look any more? Am I going about this all wrong?
My Entity Framework (Partial)