Entity Framework Error: The Relationship Could Not be Changed Because One or More of the Foreign-key Properties is Non-nullable

Entity Framework Error: The Relationship Could Not be Changed Because One or More of the Foreign-key Properties is Non-nullable

Nice article.

Original

http://www.c-sharpcorner.com/UploadFile/ff2f08/entity-framework-error-the-relationship-could-not-be-chang/

Introduction
I am currently working on a MVC and Entity Framework based project. After a few days I encountered a problem with Entity Framework when trying to delete an entity from a related data collection and received the following error:

“The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.”

Suppose I have the following entity data model. In this model the MasterTable entity has many DetailTable records. Here there is a one-to-many relationship between MasterTable and DetailTable. Now I want to remove all DetailTable entities related to the MasterTable where the MasterId value is 1.

Table Relationship in Entity diagram

Table Definition and Test Data

CREATE TABLE [dbo].[MasterTable]( [MasterId] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_MasterTable] PRIMARY KEY CLUSTERED ( [MasterId] 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
CREATE TABLE [dbo].[DetailTable]( [DetailId] [int] IDENTITY(1,1) NOT NULL, [MasterId] [int] NOT NULL, [Code] [varchar](50) NULL, [Name] [varchar](50) NULL, [Description] [varchar](50) NULL, CONSTRAINT [PK_DetailTable] PRIMARY KEY CLUSTERED ( [DetailId] 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
SET ANSI_PADDING OFFGO

ALTER TABLE [dbo].[DetailTable] WITH CHECK ADD CONSTRAINT [FK_DetailTable_MasterTable] FOREIGN KEY([MasterId])REFERENCES [dbo].[MasterTable] ([MasterId])GO
ALTER TABLE [dbo].[DetailTable] CHECK CONSTRAINT [FK_DetailTable_MasterTable]GO
SET IDENTITY_INSERT [dbo].[MasterTable] O
INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (1, N'test1')INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (2, N'test2')INSERT [dbo].[MasterTable] ([MasterId], [Name]) VALUES (3, N'test3')
SET IDENTITY_INSERT [dbo].[MasterTable] OFF SET IDENTITY_INSERT [dbo].[DetailTable] ON
INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (1, 1, N't1', N'name1', N'test')INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (2, 1, N't2', N'name2', N'test')INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (3, 1, N't3', N'name3', N'test')INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (4, 2, N't4', N'name4', N'test')INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (5, 2, N't5', N'name5', N'test')INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (6, 2, N't6', N'name6', N'test')INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (7, 3, N't7', N'name7', N'test')INSERT [dbo].[DetailTable] ([DetailId], [MasterId], [Code], [Name], [Description]) VALUES (8, 3, N't8', N'name8', N'test')SET IDENTITY_INSERT [dbo].[DetailTable] OFF
Sample code to delete detail record from the master entity

static void Main(string[] args){
using (Entities context = new Entities())
{
var masterData = context.MasterTables.Include("DetailTables").Where(p => p.MasterId == 1 ).FirstOrDefault();
var childData = masterData.DetailTables.ToList();
foreach (var data in childData)
{
masterData.DetailTables.Remove(data);
}
context.SaveChanges();
}}

Error snapshot

Resolution
We have two ways to resolve the issue.

1. The first solution is to delete the child object (entity) from the object context or DB context. When we delete a DetailTable, the Entity Framework will automatically detach the DetailTable entity from any of its relationships and the DetailTable is to be marked as a deletion. I need to make some small changes in my code. First I need to delete the child data from the Context.

static void Main(string[] args)

{
using (Entities context = new Entities())
{
var masterData = context.MasterTables.Include("DetailTables").Where(p => p.MasterId == 1 ).FirstOrDefault();
var childData = masterData.DetailTables.ToList();
foreach (var data in childData)
{
context.DetailTables.DeleteObject(data);
}
context.SaveChanges();
}
}

2. The second solution is a small change in the Primary Key of DetailTable. Here I include the Primary Key of the MasterTable (in other words MasterId) in a Primary Key of the detail table.

Update the model and my sample code works fine.

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DetailTable]') AND name = N'PK_DetailTable')ALTER TABLE [dbo].[DetailTable] DROP CONSTRAINT [PK_DetailTable]GO

ALTER TABLE [dbo].[DetailTable] ADD CONSTRAINT [PK_DetailTable] PRIMARY KEY CLUSTERED ( [DetailId] ASC, [MasterId] 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]GO

Using LINQ to Add and Get associated entities (C#)

Hi,

 if you have an Entity Database Model with two tables associated with foreign key and you need to add and then get records  using LINQ here is an example.

Entity „Apartments” contains information about apartments in a house. Each apartment can have a electricity meters (1 or more). In the example, tables have 1 to many relationship, as you can see on the picture above.

 Add record to both tables  with function „AddApartment()”:

MyDBContext dbContext = null;

       

public void AddAppartment(string apNumber, int ownerId, int regPeopleCount, int houseId, int electricityMetrics)

        {

            dbContext = new MyDBContext();

            Apartments newApartments = new Apartments();

            newApartments.ApartNumber = apNumber;

            newApartments.ApartOwnerId = ownerId;

            newApartments.HousesId = houseId;

            ElectricityMeters newElectricityMeters = new ElectricityMeters();

            newElectricityMeters.MeterNumber = electricityMetrics;

            newElectricityMeters.VerificationDate = DateTime.Now;

            newElectricityMeters.Apartments = newApartments;

           

            dbContext.Apartments.Add(newApartments);

            dbContext.ElectricityMeters.Add(newElectricityMeters);

           

            dbContext.SaveChanges();

        }

To get all records from both table you can use the „GetApartments()”

public List<ElectricityMeters> GetApartments()

        {

            dbContext = new MyDBContext();

            var someList = dbContext.ElectricityMeters.Include("Apartments").ToList();

            return someList;

        }

Have a good day :)