How to create Lookup table and define relationships

asp.net-mvc ef-fluent-api entity-framework entity-framework-6 entity-relationship

Accepted Answer

Here, we share a lookup table. Similar to yours, it appears. In place of your enum and value, LookupData has a main key and a foreign key to LookupTypes. There may be some other straightforward fields that are identified in the LookupType metadata table, such as a flag or code. The "GenderLookupId" column of our main table may then point to the LookupData.Id field. The IDs can be entered in any sequence and have no inherent meaning. You should probably add another property if you want genders 1 and 2 to have any significance (see surrogate keys).

Data-driven example

LookupType

ID    Description    CodeDesc        BooleanDesc  
1     Genders        Gender Code     NULL
2     Races          Race Code       Is Active

LookupData

ID    LookupTypeId    Description    Code    Boolean
789   1               Male           M       NULL
790   2               White          W       True
791   1               Female         F       NULL
792   2               Hispanic       H       False

Table by Main Name

NameId   Name          GenderLookupId   RaceLookupId
1234     Joe Smith     789              790
1235     Mary Meyers   791              792

Classes:

public class LookupType
{
    public int Id { get; set; }
    public string Description { get; set; }
    public string CodeDescription { get; set; }
    public string BooleanDescription { get; set; }

}

public class LookupData
{
    public int Id { get; set; }
    public int LookupTypeId { get; set; }
    public string Description { get; set; }
    public string Code { get; set; }
    public bool? BooleanValue { get; set; }
    public LookupType LookupType { get; set; } 

}

public class Name
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public int? GenderLookupId { get; set; }
    public LookupData Gender { get; set; } 
}

config for lookup data

HasRequired(p => p.LookupType).WithMany(p=>p.LookupData).HasForeignKey(p=>p.LookupTypeId).WillCascadeOnDelete(false);

Name Config:

HasOptional(p => p.Gender).WithMany(p=>p.Name).HasForeignKey(p=>p.GenderLookupId).WillCascadeOnDelete(false);
3
3/31/2015 2:35:41 PM


Related Questions





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