Unintended Joins between Inherited Classes With EF-Core

  Kiến thức lập trình

When having a class inherit from another class with EF-Core, this causes automatic mapping between mappings where no relationship should exist.

I am using EFCore version 6.0.1

So I have two tables called “Cards” and “CardsHistory”. CardsHistory is a history table and contains all of the columns in Cards, but with a few extra such as “HistoryId”, and “DateActive”. I represent this in EF as A CardsHistory entity which inherits the Cards Entity, with a few extra fields.

Cards looks like the below class. Note, no where in this class is there a reference to CardsHistory

public class Card{
  int id {get;set;}
  ...
  int cost {get;set;}
}

CardsHistory then looks like this

public class CardHistory: Card {
  int HistoryId
  DateTime DatesActive
}

The entities are then put in DBsets in a db context.

The unexpected behavior happens when I do any query on Card. Currently there is only one card in my database with an id “1”. It has been modified once so there are two entries in its history table.
If i do a query such as
dbContext.Cards.Where(c=>c.id==1).toList()
I expect just one card. I end up getting two cards, one for each entry in the cards history table. This behavior scales with the amount of entries for a given card. Meaning, cards modified 4 times will have 4 cards returned, rather than the sole card I expect to be returned.

I managed to track down the query that is sent to the database with an unintended discriminator block which is listed below.

-- @__p_1='150'
-- @__p_0='0'
SELECT t."Id", ..., t."Cost"
FROM (
    SELECT c."Id", ... ,  c0."HistoryId", CASE
        WHEN (c0."Id" IS NOT NULL) THEN 'CardHistory'
    END AS "Discriminator"
    FROM "Cards" AS c
    LEFT JOIN public_history."CardsHistory" AS c0 ON c."Id" = c0."Id"
    WHERE c."InventoryId" = 30001
    ORDER BY c."InventoryId"
    LIMIT @__p_1 OFFSET @__p_0
) AS t
LEFT JOIN "Products" AS p0 ON t."ProductId" = p0."Id"
LEFT JOIN "Users" AS u ON t."PurchasedById" = u."Id"
ORDER BY t."InventoryId"

I can get the original intended behavior to come back by removing CardHistory from the dbContext.

I can’t tell if I am breaking the conventions of EFcore or if I am making an OO mistake defining this relationship. Other posts on stack overflow and other programming sites point to this being some combination of

  1. Tbt vs tbh
  2. Lazy loading
  3. Properly configuring a FK relationship

The relationship is not a FK relationship so I could never implement points 2 or 3. I attempted to configure TBT rather than TBH in the image below but this produced no difference in output.
Image of model builder definitions for Card & CardHistory

New contributor

Jarred Moyer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

LEAVE A COMMENT