sql server - Why do I get this TPT inheritance error, "Unable to cast object of type 'Seller' to type &

This is very difficult for me to describe without an example. It took me a long time, but I've boiled it down to a simple example.

I have a database consisting of Buyers, Sellers, and Sales. However, each Buyer and each Seller inherits from Contact. It looks like this:

Now if I include a single record into Sales, everything works. However, when I insert the second one, I get errors like:

Unable to cast object of type 'Seller' to type 'Buyer'.

This might make sense if the first record failed, but I don't understand why it only happens on the second one.

I've created a repository in GitHub with everything needed (database and app) to recreate the problem. You can find it here:

Just to provide enough information to understand:

1. The query: context.Sales.Include(x => x.Buyer).Include(s => s.Seller)

2. The records:

   insert into Sales (SellerId, BuyerId, Quantity) values (7113, 7221, 1);
   insert into Sales (SellerId, BuyerId, Quantity) values (7221, 7113, 2);
   

This is very difficult for me to describe without an example. It took me a long time, but I've boiled it down to a simple example.

I have a database consisting of Buyers, Sellers, and Sales. However, each Buyer and each Seller inherits from Contact. It looks like this:

Now if I include a single record into Sales, everything works. However, when I insert the second one, I get errors like:

Unable to cast object of type 'Seller' to type 'Buyer'.

This might make sense if the first record failed, but I don't understand why it only happens on the second one.

I've created a repository in GitHub with everything needed (database and app) to recreate the problem. You can find it here: https://github.com/plettb/EFTPT

Just to provide enough information to understand:

1. The query: context.Sales.Include(x => x.Buyer).Include(s => s.Seller)

2. The records:

   insert into Sales (SellerId, BuyerId, Quantity) values (7113, 7221, 1);
   insert into Sales (SellerId, BuyerId, Quantity) values (7221, 7113, 2);
   

• sql-server
• .net-core
• entity-framework-core
• table-per-type

Share Improve this question Follow edited Jan 3 at 17:05 TylerH 21.1k7979 gold badges7979 silver badges114114 bronze badges asked Jan 2 at 1:31 Bradley PlettBradley Plett 32744 silver badges1111 bronze badges 2

• 1 Please add the relevant source code to the question. Without it the question gets useless when the link breaks. – Gert Arnold Commented Jan 2 at 15:20
• Unfortunately there is too much code. However, I've edited the question to include at least the most important bits. – Bradley Plett Commented Jan 2 at 17:54

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

The problem is that your sales have a buyer and seller with the same ContactId. So as far as EF is concerned, they are the same entity. IE EF cannot load two different Contacts with the same ContactId, but it has to as one is the buyer and one is the seller.

To resolve, remove the TPT inheritance. Instead of buyer being a subtype of Contact, a sale has two FKs to contact. If there are more attributes that need to attach to the buyer or seller, add those to the Sale.

The issue occurs when you try to load a Seller and Buyer into the change tracker where both have the same ContactId. It's fine in the database; this is an EF limitation.

It was a matter of turning the EF change tracker off.

context.Sales.Include(x => x.Buyer).Include(s => s.Seller).AsNoTracking().ToList();

Obviously this wouldn't solve the problem if there were to be any kind of editing done on the result, but in my scenario that's not an issue.