locked
Database First - Foreign Keys Not Showing up as Navigation Properties RRS feed

  • Question

  • User1548655707 posted

    Coding an MVC asp.net web application with an existing database (db first).  

    My existing database has table tbluser with a foreign key idworker refrencing table tblworker primary key id.

    With the models folder selected, I add new item, Data, ADO.net Entity model, connect to my database and choose the 2 tables :

    tbluser and tblworker

    I make sure that Include foreign key columns in model option is selected.

    But the navigation properties are not added.  The model class for tblwork doesnt have a tbluser property of type tbluser.  

    Very new to MVC entity framework The tutorials I ran through movies and ContosoUniversity one are so cool.  I have a lot of tables in this legacy database with foreign keys and would love to see foreign key columns included in my models. Any suggestion is appreciated.

    Tuesday, January 31, 2017 11:11 PM

All replies

  • User197322208 posted

    the navigation properties are not added.  The model class for tblwork doesnt have a tbluser property of type tbluser.

    Could you verify

    1. Are you accessing the correct database?

    2. In the database diagram (with SSMS )the relationship  are there?

    Wednesday, February 1, 2017 1:50 AM
  • User1548655707 posted

    Correct database is a good question. Whenever I think I am going crazy it is usually because I am in the wrong database. I have access to a lot of them. But for this project, yes I am very certain that I am in the correct database.

    In SSMS I used database diagram and see the relationship and when I hover over the connector it shows my fk 

    I also scripted out the foreign key on tbluser and the primary key on tblwork in case that helps.

    ALTER TABLE [dbo].[tblUser] WITH CHECK ADD CONSTRAINT [fk_tblUser_idworker] FOREIGN KEY([idWorker])
    REFERENCES [dbo].[tblWork] ([ID])

    ALTER TABLE [dbo].[tblWork] ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ([ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    Wednesday, February 1, 2017 2:34 AM
  • User1548655707 posted

    Also I am creating my project in MSVS 2013 and NuGet Package Manager shows that EntityFramework is version 6.1.3

    When I did the ContosoUniversity tutorial I used the same version with success.  It must be the database. I wonder if it is the integrity of the data itself.

    Wednesday, February 1, 2017 2:46 AM
  • User-832373396 posted

    Hi aidawn,

    I suggest that you can rebuild and update the two models from the database .

    Here are some detailed steps.          

    1. double-click Model1.edmx          

    2. delete the EF entity model of the two tables          

    3. Ctrl+ S,To save.          

    4.To find the corresponding class file in Models folder , manually delete them.          

    5. right-click in Model1.edmx, select Update Model from Database..          

    6. Ctrl+ S,To save.

    Best regards,

    Jolie

    Thursday, February 2, 2017 7:05 AM
  • User197322208 posted

    yes I am very certain that I am in the correct database.

    It is not enough. Please verify 

    1. In the web.config file in the root of the site

    2. In the code - that you are accessing the correct connectionstring from the point 1.

    Thursday, February 2, 2017 7:24 AM
  • User1548655707 posted

    1 In web.config I found the connection string to be correct and the only one.  I deleted the default to localdb.

    2 I had not added scaffolding to my project as of yet.  Only the models from the database.  In the tutorial at this point I had model classes for my tables and the relationship between the tables with foreign keys were added to the model classes. But not for my tables in my database.

    Could it be the version of SQL Server it is Express 2008R2?

    Saturday, February 4, 2017 5:45 PM
  • User1548655707 posted

    Thank you for your reply but sadly this did not work.  I think I will begin again from the beginning.

    Saturday, February 4, 2017 5:46 PM
  • User1548655707 posted

    I have it working now.  I sincerely thank you for your replies.  

    I opened my edmx and added the Association. 

    It is still a quandary, to me at least, that in the exact same environment (VS2013, EF 6.1.3, SQLServer2012) adding data model from one database provides the association automatically but not for another.  Maybe the multiplicity was ambiguous and stepping through the wizard specifying multiplicity was the issue.  When I used the wizard I did have to switch multiplicity settings on each table from the wizards default.

    Saturday, February 4, 2017 6:43 PM
  • User-832373396 posted

    Hi aidawn,

    I suggest that you could check the 'Include foreign key columns in the model' item whether has selected. From MSDN https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d6c4e9e4-c4b4-4895-92cc-062649ac0279/foreign-key-column-not-shoing-up-in-adonet-data-entity-model-view-why?forum=adodotnetentityframework

    And  a unique, non-clustered index allows for a null value which can't be mapped. From http://stackoverflow.com/questions/4140426/ef-does-not-generate-foreign-key-association-from-foreign-key-in-table

    Please have a try.

    Thanks,

    Jolie

    Tuesday, February 7, 2017 2:11 AM
  • User-1124346387 posted

    Sometime Visual Studio just goes stupid. Try these steps:

    1. Remove the table entities from your model diagram.
    2. Save the model.
    3. Shut down Visual Studio. Exit completely, don't just close the model editor.
    4. Restart Visual Studio.
    5. Update the model from the database and add your table entities.

    These steps worked for me when Visual Studio refused to add the foreign key association. I had to do this multiple times to get all the foreign key associations restored between entities in my model.

    As with most things Microsoft, rebooting often corrects a lot of misbehavior.

    Tuesday, May 8, 2018 11:53 AM
  • User1120430333 posted

    Did you try making another project like a console application and implement EF in it pointing to the database tables to see that it works correctly in building the navigation properties?

    Wednesday, May 9, 2018 9:40 AM