none
EF 6.1.1 setting a 0..1 navigation property to null when entity is modified fails RRS feed

  • Question

  • Synopsis
    If you have an EntityObject with a nullable foreign key used in a navigation property (0..1 multiplicity), changing the property from non-null to null fails if the EntityObject is in the Modified state. It succeeds if the EntityObject is not in the Modified state.

    Details
    I first started having this issue with EF v4, and it is still in v6.1.1. Here is the scenario:

    •  Database is SQL Server 2012.
    •  Table is named Country.
    •  The table field Country.SovereigntyCountry_ID is a nullable int, used as a foreign key.
    •  In the C# (2013) project, the class is declared public partial class Country : EntityObject.
    •  It has a navigation property declared public Country SovereigntyCountry with a 0..1 endpoint.
    •  The FK is nullable (public Nullable<global::System.Int32> SovereigntyCountry_ID).

    The idea is to be able to specify an optional country of sovereignty in a Country object. It is usually null, but in a case where the country is a territory of another, this relationship is used.

    Starting with a freshly loaded instance of Country with Country.SovereigntyCountry referencing another Country object, I can set SovereigntyCountry = null and everything works as expected.

    Start with another freshly loaded instance, make a change to Country to set the state to Modified, then try to set SovereigntyCountry = null. It won't change from the original loaded value. Nor does it change the FK member value.

    The ObjectContext involved in this is a long-running one in a WinForm. I am still using STE because I am not wild about completely re-architecting the desktop app and the ASP.NET app to use N-tier. I'll be happy to post all the code anyone wants to help with diagnosis. I have many 1-* relationships what work perfectly. All four of my 0..1-* relationships fail like this example.

    I have spent at least 30 hours researching and trying different ideas I've found, but none of them have worked. I have been developing software for nearly 40 years, and this is my first post asking for help. I am not finding any new hits on my search queries, so I am running out of ideas:

    • Per suggestion (supposed to only be necessary in EF v4), I tried
    if (newSov == null && !this.SovereigntyCountryReference.IsLoaded)
    {
        this.SovereigntyCountryReference.Load();
    }
    this.SovereigntyCountry = newSov;
    • Per suggestion, I tried setting SovereigntyCountry_ID = null after trying unsuccessfully to set SovereigntyCountry = null. SaveChanges then throws with a statement about the FK being out of sync.
    • Changed from EF v4 to 5.0, then 6.1.1.
    • Tried turning off lazy loading.
    • Tried changing all reasonable-looking combination of ObjectContext.ContextOptions flags.
    • Started looking at the EF source code.

    Wednesday, November 26, 2014 4:37 PM

Answers

  • Hi Fred,

    There were never any exceptions. When I tried to set the navigation property to null, it would work only if the entity was not modified.

    I really appreciate your jumping in and trying to reproduce it. During this time, I decided to hedge my bets and I refactored my app into EF 6.1.1 DbContext. The problem is gone. I'm still convinced it had something to do with the way EntityObjects track themselves with ObjectContext. I actually like the POCO objects better. Not nearly as many side-effects. POCO did have a couple of unexpected glitches, mainly not being able to use a navigation property collection as a data source for the DataGridView control. BindingSource came to the rescue.

    I could swear the app performs better, too.

    Thanks again, Fred. I'm still curious about the problem, but I'm now so far behind schedule on the app I can't spend any more investigative time on it. Maybe later on.

    Regards,

    Robert

    Wednesday, December 3, 2014 10:21 PM

All replies

  • Hello Robert,

    From your description, it seems that the Country is a self-reference table, but I am not sure, could you please share the related tables with us?

    >>Starting with a freshly loaded instance of Country with Country.SovereigntyCountry…

    Please also provide code to set the SovereigntyCountry to be null both successful and unsuccessful, this would help narrow this issue.

    >>Changed from EF v4 to 5.0, then 6.1.1.

    Since you could use EF 5.0 and even 6.1.1, do you try to use the DbContext instead to see if it works for you since the ObjectContext is a legacy one.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 27, 2014 7:31 AM
    Moderator
  • First off, thanks for the speedy reply!

    There are two tables that exhibit this problem. The one I used in my post is self-referencing, but the other one is not. I'll post both tables:

    Country:

    /****** Object:  Table [dbo].[Country]    Script Date: 11/27/2014 10:57:58 AM ******/
    CREATE TABLE [dbo].[Country](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Inactive] [bit] NOT NULL CONSTRAINT [DF_Country_EffectiveDate]  DEFAULT ((0)),
    	[Alpha2] [char](2) NOT NULL CONSTRAINT [DF_Country_Alpha2]  DEFAULT ('  '),
    	[Alpha3] [char](3) NOT NULL CONSTRAINT [DF_Country_Alpha3]  DEFAULT ('   '),
    	[Numeric] [smallint] NOT NULL CONSTRAINT [DF_Country_Numeric]  DEFAULT ((0)),
    	[ShortName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Country_ShortNameLowerCase1]  DEFAULT (''),
    	[ShortNameLowerCase] [nvarchar](50) NOT NULL CONSTRAINT [DF_Country_CommonName]  DEFAULT (''),
    	[FullName] [nvarchar](75) NOT NULL CONSTRAINT [DF_Country_FormalName]  DEFAULT (''),
    	[PostalName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Country_PostalName]  DEFAULT (''),
    	[Notes] [ntext] NOT NULL CONSTRAINT [DF_Country_Notes]  DEFAULT (''),
    	[Type] [tinyint] NOT NULL CONSTRAINT [DF_Country_Type]  DEFAULT ((0)),
    	[SubType] [nvarchar](50) NOT NULL CONSTRAINT [DF_Country_SubType]  DEFAULT ((0)),
    	[SovereigntyCountry_ID] [int] NULL,
    	[Capital] [nvarchar](100) NOT NULL CONSTRAINT [DF_Country_Capital]  DEFAULT (''),
    	[LanguageCodes] [varchar](50) NOT NULL CONSTRAINT [DF_Country_LanguageCodes]  DEFAULT (''),
    	[Tlds] [varchar](12) NOT NULL CONSTRAINT [DF_Country_Tlds]  DEFAULT (''),
    	[CurrencyCodes] [varchar](50) NOT NULL CONSTRAINT [DF_Country_CurrencyCode]  DEFAULT ('   '),
    	[PostalSubName] [bit] NOT NULL CONSTRAINT [DF_Country_PostalSubName]  DEFAULT ((0)),
    	[AddressAlignment] [tinyint] NOT NULL CONSTRAINT [DF_Country_AddressAlignment]  DEFAULT ((0)),
    	[AddressPosition] [tinyint] NOT NULL CONSTRAINT [DF_Country_AddressPosition]  DEFAULT ((0)),
    	[AddressLines] [tinyint] NOT NULL CONSTRAINT [DF_Country_AddressLines]  DEFAULT ((3)),
    	[AddressLineMaxLength] [tinyint] NOT NULL CONSTRAINT [DF_Country_AddressLineMaxLength]  DEFAULT ((0)),
    	[AddressThruCountry_ID] [int] NULL,
    	[CityLineFormat] [nvarchar](50) NOT NULL CONSTRAINT [DF_Country_CityLineFormat]  DEFAULT (''),
    	[PostalCodeFormat] [nvarchar](50) NOT NULL CONSTRAINT [DF_Country_PostalCodeFormat]  DEFAULT (''),
    	[PostalNotes] [ntext] NOT NULL CONSTRAINT [DF_Country_Notes1]  DEFAULT (''),
    	[DiallingCountryCode] [smallint] NOT NULL CONSTRAINT [DF_Country_TelephoneCode]  DEFAULT ((0)),
    	[DiallingNumberFormat] [varchar](75) NOT NULL CONSTRAINT [DF_Country_DialNumberFormat]  DEFAULT (''),
    	[DiallingInternationalPrefix] [varchar](50) NOT NULL CONSTRAINT [DF_Country_DialInternationalPrefix]  DEFAULT (''),
    	[DiallingNationalPrefix] [varchar](50) NOT NULL CONSTRAINT [DF_Country_DialNationalPrefix]  DEFAULT (''),
    	[DiallingNotes] [ntext] NOT NULL,
    	[SubdivisionNames] [nvarchar](75) NOT NULL CONSTRAINT [DF_Country_SubdivisionNames]  DEFAULT (''),
     CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO

    Address:

    /****** Object:  Table [dbo].[Address]    Script Date: 11/27/2014 11:00:13 AM ******/
    CREATE TABLE [dbo].[Address](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Account_ID] [int] NOT NULL CONSTRAINT [DF_Address_Account_ID]  DEFAULT ((0)),
    	[Name] [nvarchar](75) NOT NULL CONSTRAINT [DF_ContactInfo_Name]  DEFAULT (N''),
    	[Address1] [nvarchar](75) NOT NULL CONSTRAINT [DF_ContactInfo_Address1]  DEFAULT (N''),
    	[Address2] [nvarchar](75) NOT NULL CONSTRAINT [DF_ContactInfo_Address2]  DEFAULT (N''),
    	[Address3] [nvarchar](75) NOT NULL CONSTRAINT [DF_Address_Address3]  DEFAULT (N''),
    	[CountrySubdivision1_ID] [int] NULL,
    	[Locality] [nvarchar](50) NOT NULL CONSTRAINT [DF_ContactInfo_Locality]  DEFAULT (N''),
    	[CountrySubdivision2_ID] [int] NULL,
    	[City] [nvarchar](50) NOT NULL CONSTRAINT [DF_ContactInfo_City]  DEFAULT (N''),
    	[PostalCode] [nvarchar](20) NOT NULL CONSTRAINT [DF_ContactInfo_PostCode]  DEFAULT (N''),
    	[Country_ID] [int] NOT NULL CONSTRAINT [DF_Address_Country_ID]  DEFAULT ((0)),
    	[Country] [nvarchar](50) NOT NULL CONSTRAINT [DF_ContactInfo_Country]  DEFAULT (N''),
    	[CountryCode] [char](2) NOT NULL CONSTRAINT [DF_Address_CountryCode]  DEFAULT ('US'),
    	[IsStandardized] [bit] NOT NULL CONSTRAINT [DF_ContactInfo_IsStandardized]  DEFAULT ((0)),
     CONSTRAINT [PK_ContactInfo] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

    Both Country and Address have nullable [0..1] FKs; Country's are self-referencing (AddressThruCountry_ID and SovereigntyCountry_ID), while Address's are not (CountrySubdivision1_ID and CountrySubdivision2_ID).

    There is a third table that in of itself does not have any problems, but references Country is referenced by Address. Here is its definition:

    /****** Object:  Table [dbo].[CountrySubdivision]    Script Date: 11/27/2014 12:08:17 PM ******/
    
    CREATE TABLE [dbo].[CountrySubdivision](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Inactive] [bit] NOT NULL CONSTRAINT [DF_CountrySubdivision_Inactive]  DEFAULT ((0)),
    	[Category] [nvarchar](50) NOT NULL CONSTRAINT [DF_CountrySubdivision_Category]  DEFAULT (''),
    	[Code] [varchar](10) NOT NULL CONSTRAINT [DF_Table_1_Alpha2]  DEFAULT (''),
    	[Country_ID] [int] NOT NULL,
    	[Name] [nvarchar](200) NOT NULL CONSTRAINT [DF_CountrySubdivision_RomanizedName]  DEFAULT (''),
    	[NativeName] [nvarchar](200) NOT NULL CONSTRAINT [DF_CountrySubdivision_NativeName]  DEFAULT (''),
    	[LanguageCodes] [varchar](40) NOT NULL CONSTRAINT [DF_CountrySubdivision_LanguageCodes_1]  DEFAULT (''),
    	[ParentCode] [varchar](10) NOT NULL CONSTRAINT [DF_CountrySubdivision_Code1]  DEFAULT (''),
     CONSTRAINT [PK_CountrySubdivision] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    Note that the Country_ID FK in CountrySubdivision is not nullable because it doesn't have to be. There is a 1-to-* relationship because a CountrySubdivision must have a parent Country. In the cases of the Country and Address tables, the [0..1]-to-* relationships are that way because "no relationship" is a valid case.

    Here is the code that sets the SovereigntyCountry. It is the same piece of code that executes when it is successful and unsuccessful. Success can happen in a couple of scenarios:

    1. If SovereigntyCountry is not null, it always succeeds.
    2. If SovereigntyCountry is null, but the entity object has not been previously modified, it succeeds.

    If SovereigntyCountry is null and the entity object has been modified (e.g. previous editing changes), it fails to be set to null and retains its previous value.

    if (newSovereigntyCountry == null && !this.SovereigntyCountryReference.IsLoaded)
    {
        // this Load call is not supposed to be necessary in EF5 and above
        this.SovereigntyCountryReference.Load();
        }
    this.SovereigntyCountry = newSovereigntyCountry;

    As you can see, the code that is setting the SovereigntyCounty is very simple. The problem is with the internal state of "this." I have tried it with and without the Load method call with all 3 versions of EF. Again all four [0..1] navigation properties in Country and Address behave this way. The code fragments for the other three navigation properties look just like this.

    I should probably reiterate the application architecture. It is a Windows Forms application that is the UI for all of the tables. The "offending" code lives in the Country (and Address) object editors. The Country editor open a Country in an ObjectContext and leave it open until the user either closes the country or saves the changes. The UI edits directly (more or less) on the Entity objects. I do provide some infrastructure to handle change conflict, store refresh, and table record changes made by other open editors. The database classes are also used in my ASP.NET web site; currently, the web site does not have a UI to modify the Country table, but it does have one for the Address table. It is currently on hold pending the status of this issue.

    As to the suggestion of changing from the self-tracking entities to DbContext, this project is rather large, it's now very much behind schedule (largely because of this issue), and I don't think I can afford to undertake the huge amount of refactoring that would be necessary to make that change. This issue is obscure enough that I didn't catch it until after months of development. I am using some of the EntityContext features such as property change notification within the object classes, which are gone using DbContext. I did take a few hours to give it a go, and I think I'm looking at about 40 to 80 hours to make the change. From what I saw, I do think it's very likely that this problem would go away. If I were to go that route, I would probably start over and opt for a WCF-based N-tier solution and eat the schedule. Really don't want to go there that at this point. I just need to get this working for now. Probably will in a year or two... ;-)


    Thursday, November 27, 2014 4:28 PM
  • Hello Robert,

    Thanks for providing detail information, I am trying to create a test demo with these table and to see if I could reproduce this issue, this may take some time and I would post back as soon as I get any result.

    Update:

    With your provided table scripts, I executed them in my SQL Server and create the three tables successfully. After importing them to the design window, however, I do not found any constraint as foreign key:

    Do you use the logical constraints in your development? If it is, I afraid that Entity Framework does not support it. Or if I miss something, please feel free let me know.

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, November 28, 2014 9:40 AM
    Moderator
  • Fred, the forum is not letting me insert the images I have prepared for you. It says I cannot use images until my account is verified. I thought it was... Sorry about that.

    Hi Fred. Nothing that fancy... Just missing the navigation properties. Here are the two for Country (they are self-referencing). The FKs are Soverreignty_Country_ID and AddressThruCountry_ID:

    (oops, the forum is not letting me insert my image)

    and the ones for Address <=> Country (FK is Country_ID) and Address => CountrySubdivision (FK is CountrySubdivision1_ID).

    (oops, the forum is not letting me insert my image)

    There is another one for CountrySubdivision2 which looks very similar to CountrySubdivision1.

    Please note that the problem I'm having is with the ObjectContext (not the DbContext) AKA Self Tracking Entities, AKA Legacy EF 4.0. When I tested using EF 6, I used the EF 6.x EntityObject Generator to create the model from the database. The DbContextGenerator is of no use for the purposes of this experiment. Sorry if this is obvious or redundant, but better to make sure we're on the same page now before lots of code is written :-)

    The reading I've done since I posted has led me to the conclusion that I should eventually re-write my model and code to use EF 6.x POCO with virtual navigation properties (lazy loading) and not use change-tracking proxies. I'd still like to see if it's possible to get what I currently have working, but I have the sinking feeling that this problem we're chasing is one of those "issues" related to self-tracking entities (my code is for 4.0, predating the POCO proxies). I say that because as long as there are no changes to the entity, it does the Country.AddressThruCountry = null just fine.

    I would also like to thank you for taking this otherwise thankless task on. I hope it reaches one of these three positive outcomes:

    1. You can show me my stupid mistake and I'll say thanks again and slither away in shame
    2. You can prove the behavior in your test app and MS will fix it
    3. You can prove the behavior in your test app and MS will document the issue (hopefully with a workaround)

    Regards,

    Robert

    Tuesday, December 2, 2014 2:54 PM
  • Hello Robert,

    With your additional description, I reduce three table as below:

    CREATE TABLE [dbo].[Country] (
    
        [ID]                    INT           IDENTITY (1, 1) NOT NULL,
    
        [Name]                  NVARCHAR (50) NULL,
    
        [SovereigntyCountry_ID] INT           NULL,
    
        [AddressThruCountry_ID] INT           NULL,
    
        CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED ([ID] ASC),
    
        CONSTRAINT [FK_Country_C1] FOREIGN KEY ([SovereigntyCountry_ID]) REFERENCES [dbo].[Country] ([ID]),
    
        CONSTRAINT [FK_Country_C2] FOREIGN KEY ([AddressThruCountry_ID]) REFERENCES [dbo].[Country] ([ID])
    
    );
    
    
    CREATE TABLE [dbo].[Country] (
    
        [ID]                    INT           IDENTITY (1, 1) NOT NULL,
    
        [Name]                  NVARCHAR (50) NULL,
    
        [SovereigntyCountry_ID] INT           NULL,
    
        [AddressThruCountry_ID] INT           NULL,
    
        CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED ([ID] ASC),
    
        CONSTRAINT [FK_Country_C1] FOREIGN KEY ([SovereigntyCountry_ID]) REFERENCES [dbo].[Country] ([ID]),
    
        CONSTRAINT [FK_Country_C2] FOREIGN KEY ([AddressThruCountry_ID]) REFERENCES [dbo].[Country] ([ID])
    
    );
    
    
    CREATE TABLE [dbo].[CountrySubdivision] (
    
        [ID]   INT           IDENTITY (1, 1) NOT NULL,
    
        [Name] NVARCHAR (50) NULL,
    
        CONSTRAINT [PK_CountrySubdivision] PRIMARY KEY CLUSTERED ([ID] ASC)
    
    );
    

    The relationship is:

    According to your original post, create a test demo:

    using (TestDBEntities db = new TestDBEntities())
    
                {
    
                    Country country = db.Countries.FirstOrDefault(c => c.ID == 3);
    
                    country.Name = DateTime.Now.ToString();
    
                    country.SovereigntyCountry_ID = null;
    
                    db.SaveChanges();
    
                }
    

    However, this is worked for me even Both with DbContext and ObjectContext and you describes that when you set SovereigntyCountry_ID = null, it throws an exception, could you please share it? I am wondering if it is caused by database constraints because of data.

    Or if the relationship is not correct, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 3, 2014 6:58 AM
    Moderator
  • Hi Fred,

    There were never any exceptions. When I tried to set the navigation property to null, it would work only if the entity was not modified.

    I really appreciate your jumping in and trying to reproduce it. During this time, I decided to hedge my bets and I refactored my app into EF 6.1.1 DbContext. The problem is gone. I'm still convinced it had something to do with the way EntityObjects track themselves with ObjectContext. I actually like the POCO objects better. Not nearly as many side-effects. POCO did have a couple of unexpected glitches, mainly not being able to use a navigation property collection as a data source for the DataGridView control. BindingSource came to the rescue.

    I could swear the app performs better, too.

    Thanks again, Fred. I'm still curious about the problem, but I'm now so far behind schedule on the app I can't spend any more investigative time on it. Maybe later on.

    Regards,

    Robert

    Wednesday, December 3, 2014 10:21 PM
  • It is glad to hear that you got a workaround for this issue.

    Cherr.

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 8, 2014 8:44 AM
    Moderator