Using dotConnect with foreign keys that are compound primary keys?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
occamman
Posts: 2
Joined: Thu 31 Oct 2019 18:31

Using dotConnect with foreign keys that are compound primary keys?

Post by occamman » Fri 01 Nov 2019 17:42

I'm trialing dotConnect for Oracle, and trying to use it with an old Oracle database that would be difficult to make changes to (it's for medical use).

When I try to generate the data model, I get an error "The relationship XXX has columns that are not part of the key of the table on the primary side of the relationship."

Searching around, this seems to be a problem involving foreign keys and compound primary keys. I have:
  • a table "patients" with compound primary key of "patient_id" + "mrn", and
  • a second table "visits" with a foreign key to patient.patient_id, and its primary key is this foreign key + visits.visit_id column.
which seems to throw the error. It appears to be an Entity Framework limitation that Microsoft knows about and is not intending to fix. However, it looks like the model gets generated other than the offending relationsihps.

So... I'd appreciate any thoughts on any the following:
  • Is there some clever (or straightforward) work-around for this?
  • Is it safe to use the generated model without the relationships? Or should i assume that the model is simply broken and not use it? (note that while this is a medical system, the particular application I'm building is not something that will affect safety or change treatments.)
  • If we can't get this approach to work, what's the recommended way to access our database from C# without EF?
Thanks in advance!

Shalex
Site Admin
Posts: 8926
Joined: Thu 14 Aug 2008 12:44

Re: Using dotConnect with foreign keys that are compound primary keys?

Post by Shalex » Mon 04 Nov 2019 18:09

occamman wrote:
Fri 01 Nov 2019 17:42
Is it safe to use the generated model without the relationships?
Please remove invalid relationships from your model. After this, you can use the model safely.

JIC: in Entity Framework, you can apply JOIN to several classes in your LINQ query even if the underlying tables do not have associations/navigation properties (FK)

occamman
Posts: 2
Joined: Thu 31 Oct 2019 18:31

Re: Using dotConnect with foreign keys that are compound primary keys?

Post by occamman » Mon 04 Nov 2019 21:41

Thank you!

Post Reply