|
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:
|
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 21 Mar '16, 19:13
question was seen: 5,451 times
last updated: 25 Apr '16, 09:46
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.
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?
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.
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:
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
SuitandValuecolumns 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!
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.
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.
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.