none
Defining 1 to 0..1 relationship RRS feed

  • Question

  • I'm trying to determine how to define a 1 to 0..1 relationship with EF. For example, assume a master entity for organizations and then another entity that tracks organizations

        public class Organization
        {
            [Key]
            public string orgId { get; set; }
            public string parentOrgId { get; set; }
            public string OrgName { get; set; }
        }
        public class OrgMonitorList
        {
            [Key, Column(Order=0)]
            public string orgid { get; set; }
            [Key, Column(Order = 1)]
            public int orgMonitorSourceId { get; set; }
            public DateTime insertDate { get; set; }
            public bool isActive { get; set; }
            public virtual OrgMonitorSource orgMonitorSrc { get; set; }
            public virtual Organization org { get; set; }
        }

    An Organization can be removed without a corresponding delete to the OrgMonitorList. This is by design and makes up the 0..1 part of the relationship.

    My goal would be to generate a client that can display the OrgMonitorList and include information from the Organization entity if it exists like the OrgName. But from what I'm reading there are issues with this. The first issue might be that the key is a string and thus can be made nullable as this post implies it needs to be. The other alternative would be to remove the navigation property and craft the LINQ to Entity so that the query does the work for me similar to what you see here but then I'm no longer returning an entity type that I've defined. But from what I've read so far and because the key is a string I think that might be the only solution. The configuration below throws an error upon hitting the query:

        public class OrgMonitorListConfiguration : EntityTypeConfiguration<OrgMonitorList>
        {
            public OrgMonitorListConfiguration()
            {
                HasKey(k => new { k.orgid, k.orgMonitorSourceId });
                HasRequired<OrgMonitorSource>(fk => fk.orgMonitorSrc);
                HasOptional<Organization>(o => o.org).WithMany().HasForeignKey(i => i.orgid);
                ToTable("t_orgMonitorList");
            }
        }

    Multiplicity conflicts with the referential constraint in Role 'OrgMonitorList_org_Target' in relationship 'OrgMonitorList_org'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.

            public ActionResult Index()
            {
                var orgmonitorlists = db.OrgMonitorLists.Include(o => o.orgMonitorSrc).Include(o => o.org);
                return View(orgmonitorlists.ToList());
            }


    Tuesday, July 3, 2012 2:08 PM

Answers

  • hello,

    I think that the trouble comes from the undestanding of what we mean by 1 to 0..1.

    You have a FK_B_Id in table A to table B. From here The FK_B_Id should be null or something existing in table B. The value can't be something that do not exists in table B, at least during the insertion. After you may try to play with .WillCascadeOnDelete(true/false).


    thierry

    • Marked as answer by pretzelb Thursday, July 5, 2012 7:19 PM
    Wednesday, July 4, 2012 7:38 AM

All replies

  • hello,

    I think you have at least a problem here :

            [Key, Column(Order=0)]
            public string orgid { get; set; }
            [Key, Column(Order = 1)]
            public int orgMonitorSourceId { get; set; }

             HasOptional<Organization>(o => o.org).WithMany().HasForeignKey(i => i.orgid);

    That's means that orgid should be null. But orgid is part of the PK. In my knowledge, SQL server does not allow null value in a PK.

    That does not respond to your problem but illustrate that you can't solve it as long as you need a null value in a PK.

    I hope this helps

    thierry


    thierry

    Tuesday, July 3, 2012 3:01 PM
  • That is helpful. But I think there might not be a solution for me in this case.

    The key of orgid and orgMonitorSourceId was to avoid unwanted duplicates (an org should only appear once from a single source). Making this the compound key solves that issue. But switching to an autogenerated DB key might be a better design.

    public class OrgMonitorList
        {
    	[Key]
    	public int orgMonitorListId {get; set;}
            public string orgid { get; set; }
            public int orgMonitorSourceId { get; set; }
            public DateTime insertDate { get; set; }
            public bool isActive { get; set; }
            public virtual OrgMonitorSource orgMonitorSrc { get; set; }
            public virtual Organization org { get; set; }
        }

    But orgid on the OrgMonitorList entity will never be null, it is the parent entity Organizaiton that can be removed. Let me try a change to the table and see what that does. I lose the data integrity check from the compound key but it might make more sense that way.
    Tuesday, July 3, 2012 4:31 PM
  • hello again

    In this case let EF handle the keys... you will get clearest classes and also separates more clearly the model and the persistance/storage needs.

    public class OrgMonitorList
        {
    	[Key]
    	public int orgMonitorListId {get; set;}
            
            public DateTime insertDate { get; set; }
            public bool isActive { get; set; }
            public virtual OrgMonitorSource orgMonitorSrc { get; set; }
            public virtual Organization org { get; set; }
        }

    And make required what is required and optional what id optional. If you want to control the fk column name you can use .Map(m => m.HasLeftKey("FKName")) (by memory), otherwise names will follow a pattern like (by memory also) : tableName_PKName

    have fun


    thierry


    Tuesday, July 3, 2012 4:56 PM
  • I would prefer to not have EF generate the model for me. I am trying to show that this code first approach can be viable for our work. I'm already a bit frustrated in that the Organization entity is on a separate database and I am using a view to gain access to it.

    I added a identity key (orgMonitorListId) to the table instead of the compound keys of orgid and orgMonitorSourceId. I tried to use the following below but an error is thrown when trying to generate a controller for this model.

    public class OrgMonitorList { [Key] public int orgMonitorListId { get; set; } [Required(ErrorMessage = "Org is required.")] public string orgid { get; set; } public int orgMonitorSourceId { get; set; } public DateTime insertDate { get; set; } public bool isActive { get; set; } public virtual OrgMonitorSource orgMonitorSrc { get; set; } public virtual Organization org { get; set; } } public class OrgMonitorListConfiguration : EntityTypeConfiguration<OrgMonitorList> { public OrgMonitorListConfiguration() { HasKey(k => k.orgMonitorListId); HasRequired<OrgMonitorSource>(fk => fk.orgMonitorSrc); HasOptional<Organization>(o => o.org).WithMany().HasForeignKey(i => i.orgid); ToTable("t_orgMonitorList"); } }

    Multiplicity conflicts with the referential constraint in Role 'OrgMonitorList_org_Target' in relationship 'OrgMonitorList_org'.
    Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'

    It sounds like the message is saying that if there is no matching org entity then there cannot be a value in orgid which isn't what I'm expecting out of 1 to 0..1. The OrgMonitorList entity will always have a value for orgid. A matching Organization entity may or may not exist.

    Tuesday, July 3, 2012 7:39 PM
  • hello,

    imho, still something strange :

    [Required(ErrorMessage = "Org is required.")] public string orgid { get; set; }

    but :

    HasOptional<Organization>(o => o.org).WithMany().HasForeignKey(i => i.orgid);

    here is my full solution: (I will need your full code if more precisions are needed)

    using System;
    using System.Linq;
    using System.Data.Entity;
    using System.Data.Entity.ModelConfiguration;
    using System.ComponentModel.DataAnnotations;
    
    namespace testEF {
        class Program {
            static void Main(string[] args) {
                using (EFContext efc = new EFContext()) {
                    Console.WriteLine(efc.OrgMonitorLists.Count()); 
                }
            }
        }
    
        public class EFContext : DbContext {
            public IDbSet<OrgMonitorList> OrgMonitorLists { get; set; }
    
            public EFContext()
                : base() {
                 Database.SetInitializer<EFContext>(new DropCreateDatabaseAlways<EFContext>());
            }
            
            protected override void OnModelCreating(DbModelBuilder modelBuilder) {
                base.OnModelCreating(modelBuilder);
    
                modelBuilder.Configurations.Add(new OrgMonitorListConfiguration());
            }
        }
    
        public class Organization {
            public int Id { get; set; }
        }
    
        public class OrgMonitorSource {
            public int id { get; set; }
        }
    
        public class OrgMonitorList {
            [Key]
            public int Id { get; set; }       
    
            public DateTime insertDate { get; set; }
            public bool isActive { get; set; }
            public virtual OrgMonitorSource orgMonitorSrc { get; set; }
            public virtual Organization org { get; set; }
        }
    
        public class OrgMonitorListConfiguration : EntityTypeConfiguration<OrgMonitorList> {
            public OrgMonitorListConfiguration() {
                ToTable("OrgMonitorLists");
                HasKey(k => k.Id);
                Property(k => k.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    
                HasRequired<OrgMonitorSource>(fk => fk.orgMonitorSrc);
                HasOptional<Organization>(o => o.org).WithMany();
                
            }
        }
    }
    


    thierry

    Tuesday, July 3, 2012 8:02 PM
  • Great idea! Let me provide a full test console module to show what I mean. I took what you did and altered it a bit.

    One key thing to note is that the org ID is a string. This is a legacy DB and we can't alter that point. Also, the org ID must exist on the OrgMonitorList entity because it will always exist. The matching value on the Organization entity may NOT exist so it's the 0..1 in the equation.

    It again appears that I'm doing something backwards because this code never makes it to the main loop. It fails when trying to seed the DB. It fails because I'm trying to add an OrgMonitorList entity that has a value for org_id that is not part of the Organizations collection.

    The INSERT statement conflicted with the FOREIGN KEY constraint \"OrgMonitorList_org\". The conflict occurred in database \"TestContext\", table \"dbo.Organizations\", column 'org_id'.\r\nThe statement has been terminated.

    using System;
    using System.Linq;
    using System.Data.Entity;
    using System.Data.Entity.ModelConfiguration;
    using System.ComponentModel.DataAnnotations;
    using System.Collections.Generic;
    using System.Text;
    using System.Collections.ObjectModel;
    //using System.ComponentModel.DataAnnotations.Schema;
    namespace ConsoleTest_Csharp
    {
        class OrgTestProgram
        {
            static void Main(string[] args)
            {
                // prime db with data
                Database.SetInitializer(new TestingContextInitializer());
                using (TestContext context = new TestContext())
                {
                    IEnumerable<OrgMonitorList> testList = context.OrgMonitorLists;
                    foreach (var o in testList)
                    {
                        Console.WriteLine(" * " + o.omlist_id + " " + o.isActive + " " + o.org);
                    }
                    Console.WriteLine("Hit enter to continue");
                    Console.ReadKey(true);
                }
            }
        }
    }
    public class TestContext : DbContext
    {
        public DbSet<Organization> Organizations { get; set; }
        public DbSet<OrgMonitorSource> OrgMonitorSources { get; set; }
        public DbSet<OrgMonitorList> OrgMonitorLists { get; set; }
        protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
        }
    }
    public class Organization
    {
        [Key]
        public string org_id { get; set; }
        public string orgname { get; set; }
    }
    public class OrgMonitorSource
    {
        [Key]
        public int oms_id { get; set; }
        public string desc { get; set; }
    }
    public class OrgMonitorList
    {
        [Key]
        public int omlist_id { get; set; }
        public string org_id { get; set; }
        public int oms_id { get; set; }
        public bool isActive { get; set; }
        public virtual OrgMonitorSource orgMonitorSrc { get; set; }
        public virtual Organization org { get; set; }
    }
    public class OrgMonitorListConfiguration : EntityTypeConfiguration<OrgMonitorList>
    {
        public OrgMonitorListConfiguration()
        {
            ToTable("OrgMonitorLists");
            HasKey(k => k.omlist_id);
            Property(k => k.omlist_id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            HasRequired<OrgMonitorSource>(fk => fk.orgMonitorSrc);
            HasOptional<Organization>(o => o.org).WithMany();
        }
    }
    public class TestingContextInitializer : DropCreateDatabaseAlways<TestContext>
    {
        protected override void Seed(TestContext context)
        {
            context.Organizations.Add(new Organization { org_id = "abc123", orgname = "Org One" });
            context.Organizations.Add(new Organization { org_id = "def456", orgname = "Org Two" });
            context.OrgMonitorSources.Add(new OrgMonitorSource { oms_id = 1, desc = "app one" });
            context.OrgMonitorSources.Add(new OrgMonitorSource { oms_id = 2, desc = "app two" });
            context.OrgMonitorLists.Add(new OrgMonitorList { omlist_id = 1, org_id = "abc123", oms_id = 1, isActive = true });
            context.OrgMonitorLists.Add(new OrgMonitorList { omlist_id = 2, org_id = "abc123", oms_id = 1, isActive = true });
            // this record has an org_id value that does not exist in the Organizations DbSet but with a 1 to 0..1 relationship
            // the assumption is that this is OK. 
            // error thrown is 
            //The INSERT statement conflicted with the FOREIGN KEY constraint \"OrgMonitorList_org\". 
            //The conflict occurred in database \"TestContext\", table \"dbo.Organizations\", column 'org_id'.\r\n
            //The statement has been terminated."
            context.OrgMonitorLists.Add(new OrgMonitorList { omlist_id = 3, org_id = "xyz999", oms_id = 1, isActive = true });
            context.SaveChanges();
        }
    }

    Tuesday, July 3, 2012 9:52 PM
  • hello,

    I think that the trouble comes from the undestanding of what we mean by 1 to 0..1.

    You have a FK_B_Id in table A to table B. From here The FK_B_Id should be null or something existing in table B. The value can't be something that do not exists in table B, at least during the insertion. After you may try to play with .WillCascadeOnDelete(true/false).


    thierry

    • Marked as answer by pretzelb Thursday, July 5, 2012 7:19 PM
    Wednesday, July 4, 2012 7:38 AM
  • here it is some code that's seems to be hat you want to achieve (note that I let EF managing the keys)

    public class Head {
            public Int64 Id { get; set; }
    
            public virtual List<Detail> Details { get; set; }
        }
    
        public class Detail {
            public Int64 Id { get; set; }
            public String Label { get; set; }
            public virtual Head Header { get; set; }
        }
    
        public class DetailConfiguration : EntityTypeConfiguration<Detail> {
            public DetailConfiguration()
                : base() {
                HasOptional(d => d.Header).WithMany(h => h.Details).WillCascadeOnDelete(false);
            }
        }
    
        public class TestEFContext : DbContext {
            public IDbSet<Head> Heads { get; set; }
            public IDbSet<Detail> Details { get; set; }
    
            public TestEFContext(String cs)
                : base(cs) {
                Database.SetInitializer<TestEFContext>(new DropCreateDatabaseAlways<TestEFContext>());
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder) {
                base.OnModelCreating(modelBuilder);
    
                modelBuilder.Configurations.Add(new DetailConfiguration());
            }
        }
    
        class Program {
            static void Main(String[] args) {
                using (TestEFContext c = new TestEFContext(@"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True")) {
                    Console.WriteLine("Details : {0}", c.Details.Count());
                    c.Heads.Add(new Head { Details = new List<Detail> { new Detail {Label = "D1"}, new Detail { Label = "D2" } } });
                    c.SaveChanges();
                }
    
                Console.WriteLine("changing connexion...");
    
                using (TestEFContext c = new TestEFContext(@"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True")) {
                    Console.WriteLine("Details : {0}", c.Details.Count());
                    foreach ( var d in c.Details ) {
                        Console.WriteLine("{0} for {1}", d.Label, d.Header.Id);
                    }
                    c.Heads.Remove(c.Heads.First());
                    c.SaveChanges();
                }
    
                using (TestEFContext c = new TestEFContext(@"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True")) {
                    Console.WriteLine("Details : {0}", c.Details.Count());
                    Console.WriteLine("Heads : {0}", c.Heads.Count());
                }
            }
        }

    the resulting rows for Details are :

    And it can't be other way because of 

    the way to achieve what you want is, perhaps, to use Code First on an existing database, or to drop the FK in the table Detail


    thierry

    Wednesday, July 4, 2012 8:09 AM
  • I think you are correct. The problem is the definition of 1 to 0..1 or how I am trying to use it. The OrgMonitorList can refer to an Organizaiton that no longer exists but entity mapping does not appear to be able to map this relationship. I think the main issue is that it isn't really a relationship as we commonly define one. In my case the master entity (Organization) can be removed without an effect on the child collection (OrgMonitorList).

    FYI, the entities/tables are split between separate databases. The list of organizations exists in separate DB which other DBs will reference. As of now we do not have triggers to handle changes to the master DB that would sync with all the other DBs that share the data. This is why an org can exist in a DB after that org was removed from the master. We do have jobs to sync on a regular bases but those are covered by each separate DB. It might be better if we did consolidate the triggers for changes in the master DB to fire off updates to the all the referring DBs but that is not something for us to tackle now (assuming we could even get permissions to have it work that way).

    This might need to be reposted under a title of "outer join". What I would do in TSQL would be an outer join from the OrgMonitorList to the master Organization list. If a match is found I include the data from the master Organization table/entity. If no match is found then I still include the data from the child OrgMonitorList/table.

    Thursday, July 5, 2012 1:26 PM
  • I searched on EF and outer joins and found some LINQ code that works. It is a bit clumsy but it does solve the issue. I had to alter the model configuration to tell EF to ignore or not map the Organization navitgation property on the OrgMonitorList entity because that mapping may not exist back to the source. I left the property on the entity so that I could still have the strongly typed entity to pass back and forth.

    The code below is my solution but it no longer really is a 1 to 0..1 relationship as the title suggests. The master table (Organization) is the one that might optionally have 0..1 records and that is opposite of how the mapping works.

    Sorry for the confusion. The title of the post should have been about left outer join.

    using System;
    using System.Linq;
    using System.Data.Entity;
    using System.Data.Entity.ModelConfiguration;
    using System.ComponentModel.DataAnnotations;
    using System.Collections.Generic;
    using System.Text;
    using System.Collections.ObjectModel;
    //using System.ComponentModel.DataAnnotations.Schema;
    namespace ConsoleTest_Csharp
    {
        class OrgTestProgram
        {
            static void Main(string[] args)
            {
                // prime db with data
                //Database.SetInitializer(new TestingContextInitializer());
                using (TestContext context = new TestContext())
                {
                     var result2 = from oms in context.OrgMonitorLists
                                  join x in context.Organizations on oms.org_id equals x.org_id into temp
                                  let a = temp.FirstOrDefault()
                                  select new {
                                      omlist_id = oms.omlist_id,
                                      org_id = oms.org_id,
                                      oms_id = oms.oms_id,
                                      isActive = oms.isActive,
                                      orgMonitorSrc = oms.orgMonitorSrc,
                                      org = a
                                  };
                    foreach (var o in result2)
                    {
                        string stemp = "";
                        stemp = " * " + o.omlist_id + " " + o.isActive + " " + o.org_id + " ";
                        if (o.org != null)
                        {
                            stemp = stemp + ' ' + o.org.orgname;
                        }
                        Console.WriteLine(stemp);
                    }
                    Console.WriteLine("Hit enter to continue");
                    Console.ReadKey(true);
                }
            }
        }
    }
    public class TestContext : DbContext
    {
        public DbSet<Organization> Organizations { get; set; }
        public DbSet<OrgMonitorSource> OrgMonitorSources { get; set; }
        public DbSet<OrgMonitorList> OrgMonitorLists { get; set; }
        protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
            //MyBase.OnModelCreating(modelBuilder)        
            Database.SetInitializer<TestContext>(new TestingContextInitializer());
            modelBuilder.Configurations.Add(new OrgMonitorListConfiguration());
        }
    }
    public class Organization
    {
        [Key]
        public string org_id { get; set; }
        public string orgname { get; set; }
    }
    public class OrgMonitorSource
    {
        [Key]
        public int oms_id { get; set; }
        public string desc { get; set; }
    }
    public class OrgMonitorList
    {
        [Key]
        public int omlist_id { get; set; }
        public string org_id { get; set; }
        public int oms_id { get; set; }
        public bool isActive { get; set; }
        public virtual OrgMonitorSource orgMonitorSrc { get; set; }
        [NotMapped]
        public virtual Organization org { get; set; }
    }
    public class OrgMonitorListConfiguration : EntityTypeConfiguration<OrgMonitorList>
    {
        public OrgMonitorListConfiguration()
        {
            ToTable("OrgMonitorLists");
            HasKey(k => k.omlist_id);
            Property(k => k.omlist_id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            HasRequired<OrgMonitorSource>(fk => fk.orgMonitorSrc);
            Ignore<Organization>(p => p.org);
            //HasOptional<Organization>(o => o.org).WithMany();
        }
    }
    public class TestingContextInitializer : DropCreateDatabaseAlways<TestContext>
    {
        protected override void Seed(TestContext context)
        {
            context.Organizations.Add(new Organization { org_id = "abc123", orgname = "Org One" });
            context.Organizations.Add(new Organization { org_id = "def456", orgname = "Org Two" });
            context.OrgMonitorSources.Add(new OrgMonitorSource { oms_id = 1, desc = "app one" });
            context.OrgMonitorSources.Add(new OrgMonitorSource { oms_id = 2, desc = "app two" });
            context.OrgMonitorLists.Add(new OrgMonitorList { omlist_id = 1, org_id = "abc123", oms_id = 1, isActive = true });
            context.OrgMonitorLists.Add(new OrgMonitorList { omlist_id = 2, org_id = "abc123", oms_id = 1, isActive = true });
            // this record has an org_id value that does not exist in the Organizations DbSet but with a 1 to 0..1 relationship
            // the assumption is that this is OK. 
            context.OrgMonitorLists.Add(new OrgMonitorList { omlist_id = 3, org_id = "xyz999", oms_id = 1, isActive = true });
            context.SaveChanges();
        }
    }

    Thursday, July 5, 2012 7:19 PM