Coder6652

Coder6652

  • 1.6k
  • 51
  • 1.4k

EF Core and Lookup table with multiple columns

Mar 14 2023 8:09 PM

I have the following entities as follows:

public class ParentOfAddress
{
    public long HouseId;
    -- some other properties
    public HouseAddress address;
}

public class HouseAddress
{
    public long HouseId;
    public string Line1;
    public string Line2;
    public int CityId;
 
    [NotMapped]
    public string CityName;
    public string ProvCode;
    public string CountryCode;
}

public class City
{
    public int Id;
    public string Name;
    public string ProvCode;
    public string CountryCode;
}

I don't want to ever insert/update entity City.  It is just a Lookup Table that is referenced by CityId.  I don't want add a reference to a Navigation property because I always want to access CityName, ProvCode, CountryCode from the HouseAddress entity.  I can easily do this doing a join and a select new and assigning the lookup names to the HouseAddress entity.  However, the HouseAddress entity is a child of another entity and I don't want to repeat this select multitle times and the ParentOfAddress and HouseAddress need to be updated and inserted at the same time.  I understand that there are multiple ways to handle this issue but why on earth is there a not a simple relationship to define a lookup Entity without having to write the necessary Linq query.  I just want to map the CityName/ProvCode/CountryCode properties from the City entity to the HouseAddress class and I don't really need a reference to the City entity from the HouseAddress entity.  

When updating/inserting HouseAddress, I just want to save the CityId.


Answers (12)