locked
One to many relationship without foreign key RRS feed

  • 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?

    Wednesday, April 4, 2018 11:11 AM

Answers

  • 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");

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 9, 2018 11:18 AM

All replies

  • 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

    Thursday, April 5, 2018 7:57 AM
  • 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

    Saturday, April 7, 2018 9:28 PM
  • 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

    Monday, April 9, 2018 8:29 AM
  • 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.

    Monday, April 9, 2018 9:16 AM
  • 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");

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 9, 2018 11:18 AM
  • 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

    Bests,

    Jolie

    Tuesday, April 10, 2018 2:17 AM