Compound/composite foreign keys not working?

[Former Member]

I recently upgraded from Entity Framework 4 to EF6, but there seems to be an issue with the SQL Anywhere ADO.NET provider. In a database-first project, when I update the model (.edmx) from database, foreign keys that consist of two or more columns are not represented as an association afterwards. I'm currently using SA 16.0.0.2158. Does anyone have any experience with this, and/or any suggestions on how to fix this?

EDIT: Apparently, Entity Framework 4.4 (which is what I upgraded from) is actually Entity Framework 5.

I'm getting the following warning in Visual Studio:

Error 6035: The relationship X has columns that are not part of the key of the table on the primary side of the relationship. The relationship was excluded.

[Former Member]

Is it possible your foreign keys are also a case of "...Working with Overlapping Keys "?

If not let us know some of the specific details.

[Former Member]

I don't think the foreign keys overlap. When using Entity Framework 4 and the non-EF6 provider, the same database works fine. So if the keys overlap, it should have never worked in the first place, right?

[Former Member]

I'm still just guessing here but ... This could still have something specific to do with the "Database First" logic which was not part of EF4.

One possibility could be your foreign key may not be referencing a declared primary key. If that key is just a unique, not nullable index or a table uniqueness constraint, those can be used (with SQL Anywhere) as the reference target of a foreign key but EF5+'s Database First gear won't be able to find those; at least according to one link I've found.

Otherwise, the devil will be in the details and if it is a bug it will be best to capture a step by step way to see this: including table definition, connection info, ...

Not being an EF6 expert there could be much I am missing here ... but maybe you can fill in enough the gaps that any real EF6 experts reading this can help.

[Former Member]

I took the time to do some more investigation. To clarify, I upgraded from EF4.4, which apparently is considered a part of Entity Framework 5. So I've been using "Database First" the entire time.

I'm able to easily reproduce this issue in a new database. You can find the SQL script here. I created the database, tables and foreign key using Sybase's wizards, this SQL is what Sybase generated for me.

After creating a new Visual Studio solution and project, and doing all the required steps to add an ADO.NET model generated from an SQL Anywhere database, the foreign key is still not represented as an association in the new model. I noticed Visual Studio displays the following warnings in the error list:

Error 6035: The relationship 'FK_PlayingCardLog_PlayingCard_Suit' has columns that are not part of the key of the table on the primary side of the relationship. The relationship was excluded.

Error 6035: The relationship 'FK_PlayingCardLog_PlayingCard_Value' has columns that are not part of the key of the table on the primary side of the relationship. The relationship was excluded.

These warnings confuse me, because the columns used in the foreign key are definitely a part of the primary key. Even the model knows this, both the Suit and Value columns from my example have a key symbol next to them, indicating they are a part of the primary key.

I am getting the warnings in both Visual Studio 2012 and 2013. I tested with multiple different projects and configurations, but no luck. I also confirmed the exact same project and database works fine when using Entity Framework 4.4 and the non-EF6 provider for SA.

EDIT: I should note that I tested everything locally. So the database I tested with was running on my own machine.

If you need more information let me know. Thanks!

[Former Member]

I'm not able to reproduce this at this very moment but that's an issue in my current setup and probably due to a lack of knowledge on my part. ... but ... the errors do indicate an issue that would need to be corrected first.

* ` As such I do recommend starting a 2nd question with this newest info and setup. * * *

FWIW it appears you have somehow created two (2) foreign key definitions (one per column and thus an incomplete definition for either) instead of the single one needed. Get that corrected and this my be resolved. Some threads indicate you may be able to modify the data model's XML directly but there is (likely) also a GUI/Wizard way to do that as well ... <just_not_my_territory that's="" all=""/>

If it does turn out to be a bug in either the provide and VS Integration do let us know ... back on this thread.

[Former Member]

The way Entity Framework works is that it automatically generates associations in .NET into the model's XML, based off foreign keys in a database. There's no "creating foreign key definitions" step required in Visual Studio. My SQL only contains one foreign key so I don't see how I could've created two foreign keys on accident, I checked the database but there's only one foreign key.

Manually editing the XML could work but then I'm essentially doing what the data provider is supposed to do for me automatically. And I'm sure whenever the database model is updated all manual changes in the XML will be lost as it's an automatically generated file.

As I said the warnings confuse me, as they don't seem to interpret my database relationships correctly. A quick Google revealed most users get this warning when they try to have a foreign key point to a unique column that is not part of a primary key. But that is not comparable to the issue I'm seeing.

My guess is that there is a bug in the provider which makes Entity Framework think there are 2 foreign keys instead of one composite foreign key. It's a logical explanation which fits the warnings and explains why the same database, same IDE and same machine have no issues with the previous SA provider.

[Former Member]

Working through a support incident it appears the App.config file and connections being used were not taking direct advantage of the SQL Anywhere .Net provider and correcting for that seems to be required to address this.