Entity Framework insert conflicts with another foreign key

I am having difficulty inserting data with its related entities.

public class Status : Entity, IAggregateRoot
 {
    //other properties
     public readonly List<Video> _videos;
     public readonly List<Photo> _photos;
 }

--

public class Log : Entity, IAggregateRoot
  {
    //other properties
     public readonly List<Video> _videos;
     public readonly List<Photo> _photos;
     public readonly List<Sound> _audios;
  }

--

public class Photo : Entity, IAggregateRoot
    {
        //other properties
        public string Type { get; set; }
        public int TypeId { get; set; }
    }

Basically, A status object can have zero or more videos or pictures. A log object can also have zero or more videos, sound or pictures. Below is the fluent api code used to achieve this:

class LogEntityTypeConfiguration : IEntityTypeConfiguration<Log>
    {
        public void Configure(EntityTypeBuilder<Log> logConfiguration)
        {
            logConfiguration.HasMany(b => b.Videos)
              .WithOne()
              .HasForeignKey("TypeId")
              .OnDelete(DeleteBehavior.Cascade);

            logConfiguration.HasMany(b => b.Photos)
              .WithOne()
              .HasForeignKey("TypeId")
              .OnDelete(DeleteBehavior.Cascade);

            logConfiguration.HasMany(b => b.Audios)
              .WithOne()
              .HasForeignKey("TypeId")
              .OnDelete(DeleteBehavior.Cascade);
        }
    }

--

public void Configure(EntityTypeBuilder<Status> statusConfiguration)
    {
        statusConfiguration.HasMany(b => b.Videos)
          .WithOne()
          .HasForeignKey("TypeId")
          .OnDelete(DeleteBehavior.Cascade);

        statusConfiguration.HasMany(b => b.Photos)
          .WithOne()
          .HasForeignKey("TypeId")
          .OnDelete(DeleteBehavior.Cascade);
    }

This builds just fine, the image below shows the generated foreign keys.

I have got a log repository class, when trying to insert a log object, i get the following error:

System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Photos_Statuses_TypeId". The conflict occurred in database "xxxx", table "dbo.Statuses", column 'Id'

public async Task<Log> AddAsync(Log log, LogFiles files)
    {
        var strategy = _context.Database.CreateExecutionStrategy();

        await strategy.ExecuteAsync(async () => {

            using (var txn = _context.Database.BeginTransaction())
            {
                try
                {
                    if (log.IsTransient())
                    {
                        _context.Logs.Add(log);
                        _context.SaveChanges();

                        if (files.Video != null)
                        {
                            Video vid = new Video();
                            vid = files.Video;
                            log._videos.Add(vid);
                        }

                        if(files.Picture != null)
                        {
                            Photo ph = new Photo();
                            ph = files.Picture;
                            log._photos.Add(ph);
                        }

                        if(files.Audio != null)
                        {
                            Sound aud = new Sound();
                            aud = files.Audio;
                            log._audios.Add(aud);
                        }
                        _context.SaveChanges();

                        txn.Commit();
                    }

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                }
            }

        });

        return log;
    }

I also don't understand why the foreign key of the status object is showing up among the error list, when i'm trying to insert a log object??

p.s if you have a better way i could model the relationship please share.

This is most likely due to the disassociated entities. What is the source of the Log & LogFiles properties? My guess is that these are coming from a web client?

To outline the problem with passing entities: Lets look at a Photo entity which has a Status reference.

public class Status
{
   public int StatusId { get; set; }
   public string Name { get; set; }
}
public class Photo
{
   public int PhotoId { get; set; }
   public virtual Status Status { get; set; }
}

Now if I go and load a set of photos from a DbContext I might bring back 2 photos with a status of "New".

As far as "instances" go, I would have:

 Photo (ID: 1)  \
                  ==> Status (ID: 1 [New])
 Photo (ID: 2)  /

The problem is when I send those disconnected entities back to something like Controller, they are de-serialized and would look like the following:

 Photo (ID: 1)  ==> Status (ID: 1 [New])
 Photo (ID: 2)  ==> Status (ID: 1 [New])

In your case you are passing back a new Photo (fine) but it should be associated to an existing Status. Your Photo entity will probably be set up to generate a PK, but a lookup like Status would not be. Either way, if EF doesn't "know" about the Status, it will be treated as a New entity along with the photo. This leads to a FK constraint as EF tries to insert Status ID 1.

Passing Entities back to Controllers leads to all kinds of problems. If you were performing an Edit of a Photo for instance, passing back Photo ID 1, you would find that you would need to somehow tell EF about Photo #1 (using Attach and setting the State to Modified for example), and then also face FK errors around any associated entities to the photo. Attaching related entities (like Status) will solve your problem initially, but then leads to further complications like above where multiple references to the same Status are actually separate instances of a Status object. Calling attach on the first instance will work, but then you will get an exception as soon as you save something in that context with the same status. Different references and EF will complain that an instance with the same ID is associated with the context if you try and attach the 2nd.

Attaching entities from a client itself is a dangerous practice because you are implicitly trusting the data coming back from the client. A savvy, malicious user could easily modify the data in any number of ways beyond what your web page allows, corrupting your data store.

The main advice I give to devs around EF is "don't pass entities around". Nothing good becomes of it. :) If you pass Log view models, photo view models, etc. then it reduces the amount of data being shipped back and forth between server and client (making the system faster and less resource intensive) and forces you to think about the data coming back.

For instance, if I take back a LogInsertViewModel and a set of associated PhotoInsertViewModels

public async Task<Log> AddAsync(LogInsertViewModel logVm, ICollection<PhotoInsertViewModel> photoVms)
{
   // TODO: Validate that the log & files are correct/complete and applicable to the current session user...

   // If I need to lookup values from collections... (1 hit to DB to get all applicable)
   statusIds = photoVms.Select(x => x.StatusId).ToList();
   var statuses = context.Statuses.Where(x => statusIds.Contains(x.StatusId)).ToList();

   // Alternatively if I know all new photos were going to be associated a "New" status...
   var newStatus = context.Statuses.Single(x => x.Status = Statuses.New);

   // Create a Log.
   var log = new Log
   {
      //.. copy values.


      Photos = photoVms.Select(x => new Photo
      {
         // copy values.
         Status = statuses.Single(s => s.StatusId = x.StatusId); // or newStatus
      }).ToList();
   };
   context.Logs.Add(log);
   context.SaveChanges();
}