locked
Modeling a 1-1..0 relationship in entity framework (tables already exist) RRS feed

  • Question

  • Hi,

    We have the following setup:

    class A
    {
            public Guid Id {get; set;}

            virtual B b {get; set;}

    }

    class B
    {
           Guid Id;
           Guid AId {get; set;}
           virtual A a {get; set;}

    }

    The fluent mapping is:
    modelBuilder.Entity<A>()
          .HasOptional(e => e.b)
          .WithRequired(e => e.a);

    Both Id's in class A and B are tagged with DatabaseGeneratedOption.Identity

    When we call SaveChanges on A, we get an exception:
    "A dependent property in a ReferentialConstraint is mapped to a store-generated column."

    Now if we change the DatabaseGeneratedOption.Identity to DatabaseGeneratedOption.None, we have to explicitly supply the Id's. This, we fear, has the potential to fragment the database because the generated id's might not essentially be truly sequential. We do have the Id columns in our db tables for A and B, decorated with the Default constraint of newsequentialid(), and are wanting to let it remain and not supply Id's from code.

    How can we model the entities above, so that EF can handle the SaveChanges successfully?

    Note:
    In effect, we are trying to model a 1:1..0 relationship, where B can be optionally present in A, but if it does, then it has to have the corresponding A as its parent.

    Thanks,

    Mohit

    Friday, September 1, 2017 8:53 PM

Answers

  • Hi,

    Check below sample: There were at most one account for an Employee .

    Employee: Account => 1: 0..1

     class Program
        {
            public class Employee
            {
                public int Id { get; set; }
                public string Name { get; set; }
                public virtual Account Account { get; set; }
            }
            public class Account
            {
                public int Id { get; set; }
                public string UserName { get; set; }
                public string Password { get; set; }
                public virtual Employee Employee { get; set; }
            }
            public class EmployeeMap : EntityTypeConfiguration<Employee>
            {
                public EmployeeMap()
                {
                    this.ToTable("Employee");
    
                    this.HasKey(p => p.Id);
    
                    this.Property(p => p.Id).IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                    this.Property(p => p.Name).IsRequired().HasMaxLength(63);
                }
            }
            public class AccountMap : EntityTypeConfiguration<Account>
            {
                public AccountMap()
                {
                    this.ToTable("Account");
                    this.HasKey(p => p.Id);
    
                    this.Property(p => p.Id).IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                    this.Property(p => p.UserName).IsRequired().HasMaxLength(63);
                    this.Property(p => p.Password).IsRequired().HasMaxLength(63);
    
                    this.HasRequired(m => m.Employee)
                        .WithOptional(m => m.Account)
                        .Map(m => m.MapKey("AccountId"))
                        .WillCascadeOnDelete(true);
                }
            }
            public class MyContext : DbContext
            {
                public IDbSet<Account> AccountSet { get; set; }
    
                public IDbSet<Employee> EmployeeSet { get; set; }
    
                protected override void OnModelCreating(DbModelBuilder modelBuilder)
                {
                    modelBuilder.Configurations.Add(new AccountMap());
                    modelBuilder.Configurations.Add(new EmployeeMap());
                    base.OnModelCreating(modelBuilder);
                }
            }
    
            static void Main(string[] args)
            {
                using (var db = new MyContext())
                {
                    db.EmployeeSet.Add(new Employee() { Name = "Bob" });
    
                    db.EmployeeSet.Add(new Employee()
                    {
                        Name = "Ding",
                        Account = new Account() { UserName = "Admin", Password = "123456"  }
                    });
    
                    db.SaveChanges();
                }
            }
        }

    Best Regards,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by V1.0.0.0 Tuesday, September 5, 2017 5:08 PM
    Monday, September 4, 2017 7:06 AM

All replies

  • Note:
    In effect, we are trying to model a 1:1..0 relationship, where B can be optionally present in A, but if it does, then it has to have the corresponding A as its parent.

    So what are you talking about? An orphaned B with no A? B has a relationship with A. It doesn't mean that a B has to be there for an A. An A doesn't need a B to exist within the relationship. This is from a DB engine perspective.

    So what is it that you are trying to model here if modeling with DB engine concepts in mind?

    Friday, September 1, 2017 9:46 PM
  • Hi,

    Check below sample: There were at most one account for an Employee .

    Employee: Account => 1: 0..1

     class Program
        {
            public class Employee
            {
                public int Id { get; set; }
                public string Name { get; set; }
                public virtual Account Account { get; set; }
            }
            public class Account
            {
                public int Id { get; set; }
                public string UserName { get; set; }
                public string Password { get; set; }
                public virtual Employee Employee { get; set; }
            }
            public class EmployeeMap : EntityTypeConfiguration<Employee>
            {
                public EmployeeMap()
                {
                    this.ToTable("Employee");
    
                    this.HasKey(p => p.Id);
    
                    this.Property(p => p.Id).IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                    this.Property(p => p.Name).IsRequired().HasMaxLength(63);
                }
            }
            public class AccountMap : EntityTypeConfiguration<Account>
            {
                public AccountMap()
                {
                    this.ToTable("Account");
                    this.HasKey(p => p.Id);
    
                    this.Property(p => p.Id).IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                    this.Property(p => p.UserName).IsRequired().HasMaxLength(63);
                    this.Property(p => p.Password).IsRequired().HasMaxLength(63);
    
                    this.HasRequired(m => m.Employee)
                        .WithOptional(m => m.Account)
                        .Map(m => m.MapKey("AccountId"))
                        .WillCascadeOnDelete(true);
                }
            }
            public class MyContext : DbContext
            {
                public IDbSet<Account> AccountSet { get; set; }
    
                public IDbSet<Employee> EmployeeSet { get; set; }
    
                protected override void OnModelCreating(DbModelBuilder modelBuilder)
                {
                    modelBuilder.Configurations.Add(new AccountMap());
                    modelBuilder.Configurations.Add(new EmployeeMap());
                    base.OnModelCreating(modelBuilder);
                }
            }
    
            static void Main(string[] args)
            {
                using (var db = new MyContext())
                {
                    db.EmployeeSet.Add(new Employee() { Name = "Bob" });
    
                    db.EmployeeSet.Add(new Employee()
                    {
                        Name = "Ding",
                        Account = new Account() { UserName = "Admin", Password = "123456"  }
                    });
    
                    db.SaveChanges();
                }
            }
        }

    Best Regards,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by V1.0.0.0 Tuesday, September 5, 2017 5:08 PM
    Monday, September 4, 2017 7:06 AM
  • Thanks Bob. The sample you provided, helped me resolve the issue. I was missing the MapKey in my type configuration code.
    Tuesday, September 5, 2017 5:10 PM
  • Please refer to Bob's answer below for more clarity. There potentially cannot be an orphaned 'B'. I should have rather said in my note:

    If there is a 'B', it has to have an 'A' as its parent. A 'B' without an 'A' should not be allowed.

    Thanks

    Tuesday, September 5, 2017 5:13 PM