How to refer two tables, using a column in another table - Entity Framework

.net asp.net entity-framework entity-framework-6 sql-server

Question

enter image description here

As per the image above, I have a requirement where, Table C needs to refer both Table A and Table B.

Note: RefId in Table_C is a reference key for both Tables A and B.

Please, refer the code snippets,

Table_A Class

public partial class Table_A
{
   public int Id { get; set; }
   public string Name { get; set; }
   public virtual ICollection<Table_C> Table_C { get; set; }
}

Table_B Class

public partial class Table_B
{
   public int Id { get; set; }
   public string Name { get; set; }
   public virtual ICollection<Table_C> Table_C { get; set; }
}

Table_C Class

public partial class Table_C
{
   public int Id { get; set; }
   public int RefId { get; set; }
   public Type Type {get; set; }
   public virtual Table_A Table_A { get; set; }
   public virtual Table_B Table_B { get; set; }
}

Fluent API

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Table_1>()
        .Property(e => e.Name)
        .IsFixedLength();

    modelBuilder.Entity<Table_1>()
        .HasMany(e => e.Table_3)
        .WithRequired(e => e.Table_1)
        .HasForeignKey(e => e.RefId)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<Table_2>()
        .Property(e => e.Name)
        .IsFixedLength();

    modelBuilder.Entity<Table_2>()
        .HasMany(e => e.Table_3)
        .WithRequired(e => e.Table_2)
        .HasForeignKey(e => e.RefId)
        .WillCascadeOnDelete(false);
}

However, the below error occurs when trying to achieve this requirement using the code shown above.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.TableC.TableA_Id". The conflict occurred in database "TestDB", table "dbo.TableA", column 'Id'. The statement has been terminated

How can this be implemented using Entity Framework 6 (SQL Server 2014, .NET framework 4.6.1)?

1
2
9/19/2018 8:45:38 AM

Accepted Answer

I managed to fulfill this requirement using the comments made by @David Browne - Microsoft. Therefore, usage of multiple columns to refer multiple related tables is the approach for this sort of a scenario. Hope this will help anyone looking for an answer to a question of this nature. Thanks @David Browne - Microsoft for the valuable input.

0
9/20/2018 5:39:42 AM

Popular Answer

Seems to work fine for me:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Entity;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ef6Test
{

    public partial class Table_A
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Table_C> Table_C { get; } = new HashSet<Table_C>();
    }
    //Table_B Class
    public partial class Table_B
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Table_C> Table_C { get; } = new HashSet<Table_C>();
    }
    //Table_C Class
    public partial class Table_C
    {
        public int Id { get; set; }
        public int RefId { get; set; }
        public string Type { get; set; }

        public virtual Table_A Table_A { get; set; }

        public virtual Table_B Table_B { get; set; }
    }

    class Db: DbContext
    {
        public DbSet<Table_A> Table_A { get; set; }
        public DbSet<Table_B> Table_B { get; set; }
        public DbSet<Table_C> Table_C { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {


            modelBuilder.Entity<Table_A>()
                .HasMany(e => e.Table_C)
                .WithRequired(e => e.Table_A)
                .HasForeignKey(e => e.RefId)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Table_B>()
                .HasMany(e => e.Table_C)
                .WithRequired(e => e.Table_B)
                .HasForeignKey(e => e.RefId)
                .WillCascadeOnDelete(false);
        }
    }


    class Program
    {
        static void Main(string[] args)
        {

            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {
                db.Database.Log = m => Console.WriteLine(m);
                db.Database.Initialize(true);

                var a = new Table_A();
                var b = new Table_B();
                var c = new Table_C();

                a.Table_C.Add(c);
                b.Table_C.Add(c);

                db.Table_A.Add(a);
                db.Table_B.Add(b);
                db.Table_C.Add(c);

                db.SaveChanges();
            }
            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