I’m working on an application where I need to insert data into a database while avoiding duplicates. To achieve this, I group the imported data by several fields, extract the key fields, and then query the database to check if records already exist. The problem I’m facing is that EF Core isn’t able to translate my query to SQL, resulting in runtime exceptions.

Following Microsoft documentation with TransactionScope
https://learn.microsoft.com/en-us/ef/ef6/saving/transactions#passing-an-existing-transaction-to-the-context

Lets say I have 2 entities, there is no relationship between them
I wanted to make sure in db I have unique addresses so I tried the following

I have an EF class along the following, that declares a one-to-zero-or-one relationship between instances of Work.

I have an entity setup like this:

I have an entity setup like this:

I have a main entity class PurchasePlanEntity and a detail entity class PurchasePlanDetailEntity, which have a one-to-many relationship. I explicitly defined the foreign key in the DbContext class, but after using migrations, two shadow foreign keys PurchasePlanEntityOrgId and PurchasePlanEntityPurcPlanId were still generated in the table. Additionally, two foreign key constraints FK_purchasePlanDetailEntity_purchasePlan_PurchasePlanEntityPurcPlanId_PurchasePlanEntityOrgId and FK_purchasePlanDetailEntity_purchasePlan_purcPlanId_orgId were created. Why is this happening even though I have explicitly configured the foreign keys?