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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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();

        }
    }
}
David Browne - Microsoft

Related Questions

Join two tables using LINQ to Entities, then group and total the columns from each tables.

As the title says, my goal is to JOIN two tables (target and transaction) on several columns, then group the result of that join and sum the values of columns from BOTH tables. The following query only allows access to columns from the FIRST table in the join!... var actualsVsTargets = (from target in ObjectContext.PipelineTargets ...
c# entity-framework linq linq-to-entities
asked by Daniel Coffman

How to join two table from two different edmx using linq query

Update...As per your comment, EF wasn't able to parse a combined Expression tree across 2 different contexts....If the total number of records in the tables is relatively small, or if you can reduce the number of records in the join to a small number of rows (say < 100 each), then you can materialize the data (e.g. ....ToList() / .ToArray() / .AsEn...
edmx entity-framework linq
asked by StuartLC

how to insert data into two tables at a time using entity framework

i need to insert data which is in windows forms has to insert to two tables. i am using data source for all controls in windows forms. can you please sugest me how to insert data from windows forms into two tables at same time....my table is like this...**product table** pid salePrice **Product_tax table** pid taxid ...When i click on subm...
c# entity-framework sql windows-applications
asked by Sheela K R

Map Table Column to Enum and Lookup Table to Enum

You typically don't map table to an enum type. You just define an enum type based on what you have in your lookup table and use it without including these tables in the model. For instance for the Northwind.Categories table:...ID Name Description 1 Beverages Soft drinks, coffees, teas, beers, and ales 2 Condiments Sweet a...
entity-framework
asked by Pawel

Entity Framework - How to Insert to table with foreign keys without retrieving foreign table rows first

You can just include foreign key properties in addition to the navigation properties and then set them using the ids you have. If you do this will not have to go to the database to get related entities for just a sake of setting the relationship.
entity-framework entity-framework-6 insert
asked by Pawel

Entity Framework Include related table when table is empty

When you use EF Table1, Table2, etc. are all classes. ...Include(string)... method is used for caching related records in one query. You can remove ...Include... statements and your query will work similar. So if you don't have any realted records in your DB between Table1 and Table2, then you get count = 0 for Table2 property....Add some related ...
.net c# entity-framework entity-framework-6 entity-framework-6.1
asked by rraszewski

Entity Framework 6 - Missing table with only primary keys referencing different tables

We are learning Entity Framework 6.1 (from NuGet) as we move away from Linq2Sql. We have a small handful of tables that associate two separate tables like shown below....EF6 Database First generation...DB Diagram:...Schema Overview:...When in Visual studios, blank class library, doing a Database First EF6 EDMX file, the diagram only generates Tabl...
.net c# entity-framework entity-framework-6
asked by TravisWhidden

Entity Framework 6 Creating Two table from the same entity object

You cannot have multiple ...DbSet... pointing to the same class in one ...DbContext.... Your options are:...Creating new class with same properties ...Inheritance...Using different ...DBContext...s
asp.net c# code-first ef-code-first entity-framework-6
asked by DDan

How to create multiple table references to one table - Entity framework

In order to provide you an alternative, as you said you are using Entity Framework, here is a sample of Code First implementation done on C#. You can create and update the schema via Package Manager Console migrations, within Visual Studio. I have used the Fluent API in order to define the relationships, as this is recommended over the alternative....
asp.net c# database entity-framework-6 sql-server
asked by Renato

Using AutoMapper and Entity Framework to change what columns are fetched from a database table

I found a solution that I am pretty happy with...I declared a Dto class that inherits directly from my model class... public class MyObjectDto : MyObject { } ...Then configured a AutoMapper profile like so...CreateMap<MyObject, MyObjectDto>() .ForAllMembers(s => s.ExplicitExpansion()); ...I then curated the list of fields I d...
automapper automapper-8 c# entity-framework-6 iqueryable
asked by bla9x

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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