question

PetLoki-6899 avatar image
0 Votes"
PetLoki-6899 asked ZhiLv-MSFT answered

DbContext.SaveChanges + Extra Data

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

dotnet-aspnet-core-general
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

your question does not make a lot of logical sense.

In general, a primary key must exist before the key can be used as a foreign key in another table. This primary/foreign key is a constraint that you defined when assigning the the keys.

Primary keys uniquely identity fields within the record. Updates to any of these field is totally under your control. If you want to keep track of the state of these fields over time, then it is up to you to design and write this logic. For example, save the record in another table or add a start and end date to the records.

0 Votes 0 ·

This is what I'd like to do.

Maybe you have never considered (because it might have never happened to you before)
but there are some people (end-users of the applications) that tend to make corrections
after some "already recorded transactions had been stored in the database" and afterwards
when some time had past and for some accounting or auditing reason you need to recreate
what really happened in some specific e.g. Supermarket's Ticket of some sale made
in which if the <label> of the product was somehow changed, let's say from "tomatoes" to "iPad"
fr instance, then the recreated ticket will reflect as if an iPad was sold instead a Tomato
which is silly but if you had kept "both labels" (the previous one stored in the transaction
record not in the master-table) then the recreation will reflect the transaction as it really occurred in the past

I know my diagram is silly because I am not really going to create a column for each DbContext item
but rather the whole DbContext in a column as a .json data in a column125391-countrystatecity.png


0 Votes 0 ·

You also said this:

"For example, save the record in another table or add a start and end date to the records."

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?

So if I have a table with 7 FK's I need to gather all of the FK-Table their corresponding FK-Descriptive column value.

FK1 - Products ... I got ProductID but I also want to gather Product.Name
FK2 - TypeOfProduct ... I got TypeOfProductID but I also want to gather but I also want to gather TypeOfProduct .Description
FK3 - CountryOfOrigin ... I got CountryOfOriginID but I also want to gather CountryOfOrigin.CountryName
FK4 - Provider ... I got ProviderID but I also want to gather Provider.Name ... and so on

Sorry for silly example





0 Votes 0 ·

1 Answer

ZhiLv-MSFT avatar image
1 Vote"
ZhiLv-MSFT answered

Hi @PetLoki-6899,

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Would you mind providing a sample code on how to achieve what you just said?
Documentation provided is not quite clear or I haven't found there my answer yet

0 Votes 0 ·

Hi @PetLoki-6899,

You can refer the following sample:

Create the Student and Grade model and configure one to many relationships:

    public class Student
     {
         public int Id { get; set; }
         public string Name { get; set; }
    
         //foreign key
         public int? GradeId { get; set; } //nullable
         public Grade Grade { get; set; }   //navigation property
     }
    
     public class Grade
     {
         public int GradeId { get; set; }
         public string GradeName { get; set; }
    
         public List<Student> Students { get; set; } //navigation property
     }

Add DbSet in the ApplicationDbContext:

 public class ApplicationDbContext : IdentityDbContext
 {
     public DbSet<Student> Students { get; set; }
     public DbSet<Grade> Grades { get; set; } 
     public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
         : base(options)
     {
     }
 }

After migration and generate the relates table, in the controller, we could use the following code:

126177-image.png

You can check the code from here: 126210-controllercode.txt [Note] To use Include method, we need to add the using Microsoft.EntityFrameworkCore; reference.

Then, the result like this:

126238-22.gif


1 Vote 1 ·
controllercode.txt (1.2 KiB)
22.gif (337.7 KiB)
image.png (56.2 KiB)

Thank you very much

0 Votes 0 ·