locked
EF4 Not Importing Foreign Keys on Nullable Fields Correctly RRS feed

  • Question

  • I have two tables in my SQLite database that have a foreign key relationship between them. ADDRESS and TERRITORY are joined on ADDRESS.TERRITORYID and TERRITORY.TERRITORYID. There can only be one TERRITORY.TERRITORYID but in the ADDRESS table the TERRITORYID field can be null, but if it has a value it must be in TERRITORY.TERRITORYID. If the TERRITORY.TERRITORYID is deleted the cascade effect is to set ADDRESS.TERRITORYID to NULL.

    When I use EF4 to build a model from this I get Error 113:Multiplicity conflicts with the referential constrain in Role 'Territory' in relationship 'FK_Address_0'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principle Role must be '1'.

    Visually, everything looks great in the designer. The relationship shows as a 0..1 to * relationship. I'm new to EF4 (very new) but it seems that I must be missing something major here. If I can represent it in a relational database, why can't EF4 represent it in an ORM?

     

    UPDATE: It seems that the reason for this error is because ADDRESS.TERRITORYID imported as Nullable = False whereas the definition of the table specifies that the field can be NULL. Why would it import this field improperly? Is there a setting I must specify? When I update the EDM from the database the field remains as I've manually set it: Nullable = (None), which is how the other nullable fields in that table were imported.

    Monday, October 25, 2010 4:02 PM

Answers

  • I haven't had a chance to install SQL Server Express to try this out. However, one other thing I tried that gave me an error was instead of setting up a foreign key relationship in the database to simply set it up as a relation in the EF designer. That generated all sorts of errors that I can't remember at the moment.

     

    Since I have things working for now, I'll let the issue alone. However, it seems that the EF doesn't have a very logical, intuitive process for defining objects based on tables. If I use a function to create a relation, and I use a wizard to do so, I shouldn't have any errors afterward. I hope we can get a fix for EF before the next release of VS.

     

    Thank you for your help.

    • Marked as answer by liurong luo Wednesday, November 3, 2010 8:14 AM
    Monday, November 1, 2010 2:47 PM
  • I'm really glad that things work fine now! We are really appreciate if you could share the resolution, that would benefit our community members having the same issue.

    Thanks for your follow up and concern on Entity Framework. If you have suggestions or feedback, I encourage you submit a formal report here: https://connect.microsoft.com/VisualStudio/feedback/CreateFeedback.aspx

    Have a good day!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by liurong luo Wednesday, November 3, 2010 8:14 AM
    Tuesday, November 2, 2010 3:48 AM

All replies

  • Hello,

    It seems this does not occur when working with Sql Server. Did you have a sql server instance? How about it if you create a model from sql server?

    I did a test with Sql Server 2005 Express on my machine. After importing the two tables,  the TERRITORYID (foreign key constraints on two tables) in Address table disappears and the navidation properties added to the entities.

    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Edited by liurong luo Wednesday, October 27, 2010 8:24 AM small tip
    Wednesday, October 27, 2010 8:23 AM
  • I'll have to give SQL Server Express a try. Is it possible that the problem is with the provider in System.Data.SQLite?
    Wednesday, October 27, 2010 2:05 PM
  • Hello,

    Is there any update? I am not so familar with SQLite and don't have an instance on my box, sorry.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Friday, October 29, 2010 10:12 AM
  • I haven't had a chance to install SQL Server Express to try this out. However, one other thing I tried that gave me an error was instead of setting up a foreign key relationship in the database to simply set it up as a relation in the EF designer. That generated all sorts of errors that I can't remember at the moment.

     

    Since I have things working for now, I'll let the issue alone. However, it seems that the EF doesn't have a very logical, intuitive process for defining objects based on tables. If I use a function to create a relation, and I use a wizard to do so, I shouldn't have any errors afterward. I hope we can get a fix for EF before the next release of VS.

     

    Thank you for your help.

    • Marked as answer by liurong luo Wednesday, November 3, 2010 8:14 AM
    Monday, November 1, 2010 2:47 PM
  • I'm really glad that things work fine now! We are really appreciate if you could share the resolution, that would benefit our community members having the same issue.

    Thanks for your follow up and concern on Entity Framework. If you have suggestions or feedback, I encourage you submit a formal report here: https://connect.microsoft.com/VisualStudio/feedback/CreateFeedback.aspx

    Have a good day!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by liurong luo Wednesday, November 3, 2010 8:14 AM
    Tuesday, November 2, 2010 3:48 AM