EF Core - Foreign Key Constraints using SQL Identity on Parent/Child relationship

.net-core entity-framework entity-framework-core sql

Question

I have an already existing Database that we are trying to use EF Core with. Because of this, I have a situation where I have two tables with a modified* Parent/Child relationship are auto-incremented. Here is the EF Core code I have for them :

            modelBuilder.Entity<RunActualRow>(model =>
        {
            model.ToTable("RunActual");
            model.Property(a => a.ID).UseSqlServerIdentityColumn().HasColumnName("ID");
            model.HasKey(a => a.ID);
            model.HasOne(d => d.RunRow)
                .WithMany(p => p.RunActualRows)
                .HasForeignKey(d => new { d.RunID})
                .HasConstraintName("FK_RunActual_Run");
        });

        modelBuilder.Entity<RunRow>(entity =>
        {
            entity.ToTable("Run");
            entity.Property(e => e.RunID).UseSqlServerIdentityColumn();
            entity.HasKey(e => new { e.RunID });
        });

So basically above, what you are seeing is that the RunRow is the parent entity with the RunActualRow being the child entity. RunID (RunRow) is the a SQL Identity generated Foreign Key to RunActualRow.

When I try to do an insert where I declare the parent and children in one RunRow object, I get an error that Entity Framework's foreign keys are missing. I am unable to set the foreign key until sql server assigns Runs row a PK....however, EF Core seems to not set that correctly, instead EF Core just gives me an error, and Profiler shows I'm trying to enter a RunActual row with an invalid FK of 0.

Has anybody run into this specific scenario, and if so, is it a known issue or am I missing something? Do I have to make this two separate transactions?

  • WHY MODIFIED? because in this scenario we are actually not strictly enforcing that you have to have a parent to have a child.
1
1
12/17/2018 8:29:16 PM

Accepted Answer

It happened to NOT really be an Entity Framework Core error, although I would definitely argue that Entity Framework could handle result sets better to figure out what is actually happening or to throw a more informative error letting you know that the result set that it was looking at and it returned didn't contain the query you requested.

Thanks David for helping me figure this out - I'm not sure I would have thought to create a new table structure that mimic'd the old one close as it did to see that it does indeed work.

The error turned out to be due to a trigger on the database - that trigger was running a stored procedure which turned around and returned a 1 or 0 result set to say if there was an error (0 if there was no error, 1 if there was an error).

Due to this, it would cause entity framework to pick up that value (even though the resultsets column name didn't match the select statement it sent in --- which is something that could be handled better IMO) and on the child table try to use that as the foreign key. Because 0 was not a valid FK, it would error out.

Funny enough, I think that if I had gotten a 1 (Error), it would have tried to use that as the foreign key, which would have worked, because there IS a record with 1 as the primary key.

To conclude this - if someone runs into this issue, run your profiler, and see what Entity Framework is sending in, and what the result of the query is, if you see that you are getting multiple result sets back, take a look at the triggers being run.

Thanks all!

0
12/18/2018 7:09:51 PM

Popular Answer

Ok. By "post a short, but complete repro", I meant something like this (but that actually fails):

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
//using Microsoft.Samples.EFLogging;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.Threading.Tasks;

namespace EFCore2Test
{

   public class RunRow
    {
        public int RunID { get; set; }
        public ICollection<RunActualRow> RunActualRows { get; } = new HashSet<RunActualRow>();
    }
    public class RunActualRow
    {
        public int ID { get; set; }

        public int RunID { get; set; }
        public RunRow RunRow { get; set; }
    }

    public class Db : DbContext
    {
        public DbSet<RunRow> RunRows { get; set; }

        public DbSet<RunActualRow> RunActualRows { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        { 
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<RunActualRow>(model =>
            {
                model.ToTable("RunActual");
                model.Property(a => a.ID).UseSqlServerIdentityColumn().HasColumnName("ID");
                model.HasKey(a => a.ID);
                model.HasOne(d => d.RunRow)
                    .WithMany(p => p.RunActualRows)
                    .HasForeignKey(d => new { d.RunID })
                    .HasConstraintName("FK_RunActual_Run");
            });

            modelBuilder.Entity<RunRow>(entity =>
            {
                entity.ToTable("Run");
                entity.Property(e => e.RunID).UseSqlServerIdentityColumn();
                entity.HasKey(e => new { e.RunID });
            });
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=(local);Database=EFCoreTest;Trusted_Connection=True;MultipleActiveResultSets=true");
            base.OnConfiguring(optionsBuilder);
        }
    }



    class Program
    {


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

                db.Database.EnsureCreated();
                db.Database.ExecuteSqlCommand("alter table RunActual drop constraint FK_RunActual_Run");

            }
            using (var db = new Db())
            { 

                //db.ConfigureLogging(s => Console.WriteLine(s));

                var r = new RunRow();
                db.RunRows.Add(r);

                db.SaveChanges();

                r.RunActualRows.Add(new RunActualRow());
                r.RunActualRows.Add(new RunActualRow());
                r.RunActualRows.Add(new RunActualRow());

                db.SaveChanges();
            }

            using (var db = new Db())
            {
                var count = db.RunActualRows.Count();
                Console.WriteLine($"{count} RunActualRows");
            }


            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow