DbContext.SaveChanges + Extra Data

Question

While DbContext.SaveChanges is being executed for an entity (table) which has let's say 7 Foreign Keys,
somehow (by virtue of EF or VS2019 or C# or DBContexrt or "this" or all of them) when you press the "Save Button" all of the data is available for that table, including all of the ID's corresponding to each one of its 7 Foreign Keys.

I'd like to also have available the 1st non-Foreign Key pertaining to each one of the Foreign Keys.

For instance, if I have Country, States and Cities entities with CountryID, StateID and CityID Primary keys and also CountryName, StateName and CityName as the "descriptive" column in each one of these tables then I'd like to modify State by also having an extra-column with the name <CountryName> beside to the CountryID (its FK), StateID (its PK) and StateName columns.

For Cities I'd like to have CountryID (FK) , StateID (FK) and CityID (PK) plus CityName and also have extra CountryName and StateName

So the pseudo algorithm would be "save as normally do and also save the corresponding 1st non-FK column of every FK that each table has

In this way I'd like to preserve the integrity of the <labels> used in each transaction for auditing purposes.

Is clear that any of these column <labels> might be accidentally or deliberately changed in the course of coming days, months or years. So the reference you can make (as it does normally when you execute a query) to a <label> of prior transactions (if changed) then it won't match the real picture of what really happened before the change was made.

Is that possible at all and how? I ask, if anyone might be interested in answering, how to do it with real code because I have no the slightest clue in how to achieve it

Answer 1

Hi @Pet Loki ,

I don't know how to do that carrying on the additional data I need to gather from "FK table", master table, parent table or however its name might be. Maybe that is my real question: How do you access or gather the values that I need to store at SaveChanges moment?

Before inserting related entities, you should check whether the related entity is exists or not? For example, before inserting the State entity, you will query whether the Country exists or not. If it exists, you can get the Country from the Countries table and assign to the State entity. If the country doesn't exist, you need to create a new country. Then, call the SaveChanges method to save the new data. After that, you can get the relates entities and its properties via the Navigation property, because you have configured the one-to-many relationship between them.

Refer the following articles: Navigation Properties , EF Core Relationships and Loading Related Data.

Besides, about the <labels> changed problem, as AgaveJoe said, you can add another table to store the update information (such as updator, tablename, original label, new label, updatetime,description (add log like this“{user} has changed the column from XX to YY at datetime” ) ). Then, you use it as a filter condition when query data.