EF Core 2: The MERGE statement conflicted with the FOREIGN KEY constraint

0

I have following classes:

public class GSProject
{
    public int Id { get; set; }
    public ICollection<GSPoint> Points { get; set; }
    public ICollection<GSPlanarSurface> PlanarSurfaces { get; set; }
}

public class GSPoint
{
    public int Id { get; set; }
    public int? PlanarSurfaceId { get; set; }
    public GSPlanarSurface PlanarSurface { get; set; }
}

public class GSPlanarSurface
    {
        public int Id { get; set; }

        public ICollection<GSPoint> Points { get; set; }

        public int? PxyId { get; set; }
        public GSPoint Pxy { get; set; }

        public int? PyId { get; set; }
        public GSPoint Py { get; set; }
}

and context class (OnModelCreating() related content):

modelBuilder.Entity<GSPlanarSurface>()
                .HasMany(e => e.Points)
                .WithOne(e => e.PlanarSurface)
                .HasForeignKey(e => e.PlanarSurfaceId);

Then I created ef migration and related migration .cs file content is:

migrationBuilder.CreateTable(
                name: "GSPoints",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    PlanarSurfaceId = table.Column<int>(nullable: true),
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_GSPoints", x => x.Id);
                    table.ForeignKey(
                        name: "FK_GSPoints_GSPlanarSurfaces_PlanarSurfaceId",
                        column: x => x.PlanarSurfaceId,
                        principalTable: "GSPlanarSurfaces",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

Error:
{System.Data.SqlClient.SqlException (0x80131904): The MERGE statement conflicted with the FOREIGN KEY constraint "FK_GSPoints_GSPlanarSurfaces_PlanarSurfaceId". The conflict occurred in database "GSTDb", table "dbo.GSPlanarSurfaces", column 'Id'.

appears if I try to call:

context.Set<GSProject>().Add(entity);
await context.SaveChangesAsync();

entity is an instance of GSProject with a few GSPoints and without any instance of GSPlanarSurface (entity.PlanarSurfaces Count = 0)

EDIT:
and here is a script auto generated in t-sql for the key:

USE [GSTDb]
GO

ALTER TABLE [dbo].[GSPoints]  WITH CHECK ADD  CONSTRAINT [FK_GSPoints_GSPlanarSurfaces_PlanarSurfaceId] FOREIGN KEY([PlanarSurfaceId])
REFERENCES [dbo].[GSPlanarSurfaces] ([Id])
GO

ALTER TABLE [dbo].[GSPoints] CHECK CONSTRAINT [FK_GSPoints_GSPlanarSurfaces_PlanarSurfaceId]
GO


I executed simple tsql script:

insert into GSProjects ([Description] ,[Name]) values ('d1', 'proj1')
insert into GSPoints([Number], [IsSlave] ,[X], [Y], [Z]) values (0, 0, 0, 0, 0)
insert into GSPoints([GSProjectId], [Number], [IsSlave] ,[X], [Y], [Z]) values (5, 0, 0, 0, 0, 0)


and there were no problems with insertion. First GSPoint has GSProjectId set to null, second GSPoint has GSProjectId set to non null value (5 here). Both ones have GSPlanarSurfaceId set to null and both were inserted correctly.

c#
tsql
entity-framework-core
.net-core-2.0
asked on Stack Overflow Sep 12, 2018 by ael • edited Sep 12, 2018 by ael

2 Answers

0

Answer is not related to EntityFramework. In my case PlanarSurfaceId was set by default to 0. I changed it to null and it works.

answered on Stack Overflow Sep 12, 2018 by ael
0

I had similar problem, I implemented "import" function to sql from excel table, where all data was "common names" and when finding references for correct ID to particular column via firstordefault() I got 0 as result - not found, so in attemts to save context, I got this error. I got same error even when I tried to delete whole table data, while another table keeps ID used by master table. I hope this helps for some1, i spent a lot of time debugging this, because test import of 10 rows was good, and full (36k rows) throws me this error over and over.

answered on Stack Overflow Apr 19, 2020 by Keyser Söze

User contributions licensed under CC BY-SA 3.0