EF Core and Lookup table with multiple columns

Hello,

Please use this link as reference,

https://www.c-sharpcorner.com/article/relationships-in-entity-framework-core/

public class HouseAddress
{
    public long HouseId;
    public string Line1;
    public string Line2;
    public int CityId;
}

If you are storing CityId then you no need to store other column related to City because you can fetch based on CityId

Regarding your Entity split solution, it can be a valid approach depending on your specific requirements and database design. However, it can also lead to more complex queries and joins, especially if you have multiple levels of nested entities. It can make it more difficult to maintain and update your database schema in the future.

Regarding your concern about creating a DTO every time HouseAddress is involved in any type of query, you can create a reusable DTO that contains all the properties you need for HouseAddress and its related entities, and use it in multiple queries as needed. This can help simplify your code and avoid repeating the same projection logic in multiple places.

However, if you have a parent entity with many properties, creating a custom DTO for each query can become tedious. In this case, you can use a query projection to retrieve only the specific properties you need for each query, without having to retrieve all the properties of the parent entity. This can help reduce the amount of data retrieved from the database and improve performance.

For example, if you have a parent entity called Person that has a navigation property of HouseAddress, you can retrieve a list of Person entities with their related HouseAddress entities and City entities using the following code:

var people = dbContext.People.Include(p => p.HouseAddress.City).ToList();

This will retrieve all Person entities with their related HouseAddress entities and City entities, allowing you to access the City properties as if they were properties of HouseAddress.

You are correct that the solution I provided only addressed the specific scenario of retrieving HouseAddress with its related City entity. To retrieve a list of parent entities that have a navigation property of HouseAddress or even retrieving a list of grandparents, you can use the same approach of eager loading related entities using the Include method.

You are providing a solution that only involves HouseAddress. What is the solution for retrieving a list of the parent entities that have a navigation property of HouseAddress or even retrieving a list of grandparents.  I don't think is a good idea to create a DTO every time HouseAddress is involved in any type of query.  How would query projection work with the parent entity that has another 15 properties?

What was wrong with my Entity split solution that would be reusable for every time that HouseAddress is involved in a query?

I understand your frustration with the lack of a built-in Fluent API to handle the flattening out of entities or lookups of fields from child entities. I would suggest weighing the pros and cons of each solution and choosing the one that best fits your specific requirements and constraints. Sometimes there is no one-size-fits-all solution, and custom code or views may be necessary.

I understand your concern and it's true that flattening out entities or performing lookups on child entities is a common requirement in database design. However, there is no direct Fluent API method to define this type of relationship, as it depends on the specific structure and requirements of your database.

One possible approach to avoid creating views or writing special code is to use the Include method to eagerly load the related City entity when querying for HouseAddress. This will allow you to access City.Name as if it were a property of HouseAddress. For example:

var houseAddress = dbContext.HouseAddresses.Include(h => h.City).FirstOrDefault();
string cityName = houseAddress.City.Name;

You could define a custom DTO (data transfer object) that contains the fields from both HouseAddress and City, and use a query projection to populate it directly from the database. This can be achieved using the Select method with an anonymous type or a custom class. For example:

var result = dbContext.HouseAddresses
    .Where(h => h.HouseId == 1)
    .Select(h => new {
        HouseId = h.HouseId,
        HouseAddress = h.HouseAddress,
        CityName = h.City.Name,
        ProvCode = h.City.ProvCode,
        CountryCode = h.City.CountryCode
    })
    .FirstOrDefault();

   
This will return a single object that contains the fields you need, without having to create a separate view or write custom code to flatten out the entities.

I really don't like the idea of loading the City entity because it's possible that this type of lookup entity has other mapped properties and I don't want to be loading all the other fields.  I am just interested in the lookup names.  I am looking for a more general approach.

I can change the HouseAddress model class and define the get property for CityName to return City.Name if City is not equal to null, otherwise the CityName property.  This will be the same for the ProvCode and CountryCode.  This is really ugly but it's a solution.

I can use Entity splitting and define the properties from the City table to not be inserted and updated by using [DatabaseGenerated(DatabaseGeneratedOption.Computed)]. I can also use the Metadata.BeforeSaveBehavior and AfterBehavior to ignore.  This may be workable.  I am not sure how this will work for the delete?

I can also create a Database view for the HouseAddress entity which should solve my problem but then I read that I have to use "query types" for this to work.

I can also use Table-Valued function instead of a View.

I am sure many people had the same type of problem to solve and there needs to be a recommended way instead of all these suggestions.  

Thank you Paul for your answer.  I have thought of all that and I decided to prevent the insert/update by defining a virtual navigation property of City.  However the Address entity is a navigation property of another parent entity and the query involves the parent, address, and city entities.  I will need a view for retrieving the parent entity plus another view for retrieving the address entity when it needs to be retrieved without the parent.  The HouseID is a key for the parent and HouseAddress and has nothinig to do with the City entity.  I agree that it is possible to isolate all this.   However, I was hoping that there was a Fluent API to allow for the definition of this type of relationship which is basically just a lookup.  The HouseAddress entity can also have a grandparent and there should be a relationship/property method to say that whenever the HouseAddress needs to be retrieved that it must be flattened out.  I just think it is ugly to use City.Name to get the CityName when retrieving the data but CityName when creating a new record.  I know that I can write code to ensure that CityName is always accessed as HouseAddress.CityName and the same for ProvCode and CountryCode.

The flatenning out of an Entity or even just the lookup of one or more fields from a child entity is something that it is very common and I'm just surprised that there is no fluent API to define this.  The idea of using this Fluent API is to allow for the definition of common relationships and property retrievals so that we don't have to write any linq queries and create views.  

I can use Entity splitting that splits an Entity into 2 or more tables but Entity splitting is used for tables that need to be inserted and updated at the same time.  I can try to make this work but I just wanted to know if there was a more elegant way.  I read the Microsoft documentation on the Fluent API but I know that many times Microsoft does not document everything.

If anyone solved this problem without having to create any view or write any special code, please let me know.  This is a very simple problem that should have a declarative solution.

Hi Coder6652,
One possible solution to your problem is to use a view or a stored procedure to join the HouseAddress and City tables and select the desired fields, including the CityName, ProvCode, and CountryCode fields, into a flattened result set. You can then map this result set to a new read-only entity or DTO (Data Transfer Object) that represents the flattened result.

Or you can use a read-only entity or DTO to represent the City table and use a foreign key relationship between the HouseAddress and City entities. This will allow you to access the CityName, ProvCode, and CountryCode fields directly from the City entity without having to perform a join or select query.

To configure the foreign key relationship, you can use the Fluent API to disable cascade operations for the City entity, as shown below:

modelBuilder.Entity()
    .HasOne(h => h.City)
    .WithMany()
    .HasForeignKey(h => h.CityId)
    .OnDelete(DeleteBehavior.Restrict);

This will prevent EF from attempting to cascade any changes made to the HouseAddress entity to the City entity. You can then use the Include method to eagerly load the related City entity when querying the HouseAddress entity, as shown below:

var houseAddress = context.HouseAddresses
    .Include(h => h.City)
    .FirstOrDefault(h => h.HouseId == houseId);

This will load the City entity into the HouseAddress.City navigation property, allowing you to access its properties directly. However, since you do not want to update or insert the City entity, you can simply ignore the City property when saving changes to the HouseAddress entity.