One to many relationship without foreign key

Question

User2073257352 posted

I've problem with mapping ralations, which there is in existing database. 

Two tables: table1 and table2

table1 has fields: table1Id, table1Code, table1Field (table1Id is primary key)

table2 has fields: table2Id, table1Code, table2Field (table2Id is primary key)

And I've ralation one to many between table2 and table1 so I need in table2 object table2.table1 which will be filled by looking for table1 row in table1 by table1Code. Ofcourse I need ICollection<table2> inside table1.

There is reference in database "ALTER TABLE table2 WITH CHECK ADD CONSTRAINT [FK_table2_table1] FOREIGN KEY([table1Code]) REFERENCES table1 ([table1Code])"

I use https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator which generates mapping below

HasRequired(a => a.table1).WithMany(b => b.table2).HasForeignKey(c => c.table1Code);

and when try to use it I see that generated SQL compares table1Id with table1Code (I think that it's because of use table1Id as foreign key)

Is it possible to create such mapping between those two tables, which are connected by table1Code?

Answer 1

User-832373396 posted

<g class="gr_ gr_9 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="9" data-gr-id="9">Hi</g> <g class="gr_ gr_8 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="8" data-gr-id="8">krazysiek</g>,

krzysiek

Is it possible to create such mapping between those two tables, which are connected by table1Code?

Sir, this is what you are looking for. 

Configuring a Composite Primary Key

ep.

The following example configures the DepartmentID and Name properties to be the composite primary key of the Department type.

modelBuilder.Entity<Department>().HasKey(t => new { t.DepartmentID, t.Name });

From https://msdn.microsoft.com/en-us/data/JJ591617.aspx#1.2 

With regards, Angelina Jolie

Answer 2

User2073257352 posted

Thanks AngelinaJolie :)

I don't know if I understand everythinkg but when I changed my table1 key as below

HasKey(x => new { x.table1Id, x.table1Code});

With table2 configuration

HasRequired(a => a.table1).WithMany(b => b.table2).HasForeignKey(c => c.table1Code).WillCascadeOnDelete(false); 

I've got 

System.Data.Entity.ModelConfiguration.ModelValidationException: One or more validation errors were detected during model generation: ... The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical

Answer 3

User-832373396 posted

Hi Krzysiek,

Here is the full code as shown below

 namespace EFModeling.Configuring.DataAnnotations.Samples.Relationships.CompositeForeignKey
    {
        #region Model

        public class MyContext : DbContext
    {
        public DbSet<Car> Cars { get; set; }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Car>()
                .HasKey(c => new { c.State, c.LicensePlate });

            modelBuilder.Entity<RecordOfSale>().HasOptional
                (s => s.Car)
                .WithMany(c => c.SaleHistory)
                .HasForeignKey(s => new { s.CarState, s.CarLicensePlate });
        }
    }

    public class Car
    {
        public string State { get; set; }
        public string LicensePlate { get; set; }
        public string Make { get; set; }
        public string Model { get; set; }

        public List<RecordOfSale> SaleHistory { get; set; }
    }

    public class RecordOfSale
    {
        public int RecordOfSaleId { get; set; }
        public DateTime DateSold { get; set; }
        public decimal Price { get; set; }

        public string CarState { get; set; }
        public string CarLicensePlate { get; set; }
        public Car Car { get; set; }
    }

Then it should be ok.

Bests,

Jolie

Answer 4

User2073257352 posted

Hi Jolie.

Thanks for replay

As I see I have a litttle different situation. Using Your model I would have Car.CarId which in database is primary key.  I would have also something like Car.CarCode which is unique in Car table (and it's not primary key). In RecordOfSale I would have RecordOfSale.CarCode. Using this CarCode field both tables are connected. Two tables are connected without using primary key.

Answer 5

User2073257352 posted

EF Core is solving my problem during table1 configuration in that way

entity
      .HasOne(d => d.table1)
      .WithMany(p => p.table2)
      .HasPrincipalKey(p => p.table1Code)
      .HasForeignKey(d => d.table1Code)
      .OnDelete(DeleteBehavior.ClientSetNull)
      .HasConstraintName("FK_table2_table1");

Answer 6

User-832373396 posted

<g class="gr_ gr_10 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="10" data-gr-id="10">Hi</g> <g class="gr_ gr_7 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="7" data-gr-id="7">krzysiek</g>,

krzysiek

EF Core is solving my problem

Nice, I recommend that you could mark your answer as an answer so that other people could find the final solution quickly.

and if could, you could add some details words to explain it :)

and EF Core RelationShips Guide

• https://docs.microsoft.com/en-us/ef/core/modeling/relationships 

Bests,

Jolie