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)