One Document can contain many Images; one Image can reference only one Document:

CREATE TABLE [Images] 
(
     [Id] [bigint] NOT NULL IDENTITY(1,1),
      ...
     [DocumentId] [uniqueidentifier] NOT NULL,

     CONSTRAINT [PK_Images_Id] 
         PRIMARY KEY CLUSTERED ([Id] ASC),
     CONSTRAINT [FK_Images_Documents] 
         FOREIGN KEY([DocumentId]) REFERENCES [Documents]([Id])
)

CREATE TABLE [Documents] 
(
     [Id] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
      ...
     [DocumentId] [uniqueidentifier] NOT NULL,

     CONSTRAINT [PK_Documents_Id] 
         PRIMARY KEY CLUSTERED ([Id] ASC),
     ...
)

C# classes:

public class Image
{
   public long Id {get; set;}
   public string Name {get; set;}
   ...
   public Document Document { get; set; }
}

public class Document
{
   public Guid Id {get; set;}
   public string Name {get; set;}
   ....
   public ICollection<Image>? Images { get; set; } = new List<Image>();
}

And this finally is the mapping

public class ImageConfiguration : IEntityTypeConfiguration<Image>
{
    public void Configure(EntityTypeBuilder<Image> builder)
    {
       builder.HasKey(it => it.Id);   
       ....      
       builder.HasOne(it => it.Document).WithMany(c => c.Images);
    }
}

When I try to insert a Document together with an Image:

var document = new Document
{
   Name = "Test doc",
   Images = new List<Image>{new Image{ Name = "test"}};
};

var db = new MyDbContext();

db.Documents.Add(document);
db.SaveChanges();

I'm getting this error:

Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while saving the entity changes.
See the inner exception for details.

Microsoft.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Images_Documents".

The conflict occurred in database "MyDb", table "Documents", column 'Id'.

If I change create table script for image and make property DocumentId as NULL and remove the corresponding FK (FK_Images_Documents) and mark Document as nullable on the Image model the insert (both document and image) will pass successfully.

What am I doing wrong?

CodePudding user response：

Your configuration is messed up. You've probably defined an extra foreign key property. So remove your fluent configuration and start over.

This works fine:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;

namespace EfCore6Test
{

    public class Image
    {
        public long Id { get; set; }
        public string Name { get; set; }
   
   public Document Document { get; set; }
    }

    public class Document
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public ICollection<Image>? Images { get; set; } = new List<Image>();
    }

    public class Db : DbContext
    {
        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.Entity<Document>();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=localhost;database=efCore6Test;Integrated Security=true;TrustServerCertificate=true", o => o.UseRelationalNulls(true))
                .LogTo(Console.WriteLine, LogLevel.Information);
            base.OnConfiguring(optionsBuilder);
        }
    }


    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new Db())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                var document = new Document()
                {
                    Name = "Test doc",
                    Images = new List<Image> { new Image { Name = "test" } }
                };
                db.Set<Document>().Add(document);
                db.SaveChanges();
            }
        }
    }
}

CodePudding user response：

Good question for "attention test" :)

When I tried to execute this sample, got following error:

SqlException: Cannot insert the value NULL into column 'DocumentId', table 'testDb.dbo.Documents'; column does not allow nulls. INSERT fails.

The reason is that when you're creating db-table Documents:

CREATE TABLE [Documents] 
(
     [Id] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
      ...
     [DocumentId] [uniqueidentifier] NOT NULL,

     CONSTRAINT [PK_Documents_Id] 
         PRIMARY KEY CLUSTERED ([Id] ASC),
     ...
)

I suppose that you ocasionally added excessive column "DocumentId" into db-table Documents not null (but you've already defined Id primary key column there), which is not getting set

var document = new Document
{
   Name = "Test doc",
   Images = new List<Image>{new Image{ Name = "test"}};
};

So we have a Documents db-table with not nullable column DocumentId which is not getting set, so the error happening is expected behavior

To avoid error above, please just remove redundant "DocumentId" column in Documents table