How to override/configure adding of entity of VIEW as an insert to a several subtables?

I have a view patient in mysql database, which consists of patient_info and patient_documents tables.

View patient:

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int          | NO   |     | 0       |       |
| name          | varchar(30)  | NO   |     | NULL    |       |
| passport_data | char(11)     | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

Table patient_info:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(30)  | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Table patient_documents:

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| documents_id  | int          | NO   | PRI | NULL    | auto_increment |
| patient_id    | int          | NO   | UNI | NULL    |                |
| passport_data | char(11)     | NO   | UNI | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

I want to keep getting of entities from view patient, but change adding of Patient entity to database via DbContext in this way: insert Patient's attributes into patient_info and patient_document.

How can I implement it? I thought it's a good idea because it'd be possible to add extra hidden attributes to patient_info by other types of users and these attributes won't be read by anyone who doesn't have an access to patient_info (view patient restricts getting attributes).

Maybe I recreated the wheel here and there's no sense in such approach?

Summary: how to override adding of entity Patient to DbContext as adding of it's attributes to patient_info and patient_documents, but keep getting of patient info through view patient?

I want something like

public Patient patient
{
    get => PatientView.Patient; // keep EFC behavior
    set
    {
        var insertedPatient = value;
        // breaking patient entity into two entity

        var insertedPatientInfo = new PatientInfo
        {
            Name = insertedPatient.Name;
        };
        PatientInfo(insertedPatientInfo); // insert in one table

        var insertedPatientDocuments = new PatientDocuments
        {
            PasportData = insertedPatient.PassportData;
        }
        PatientDocuments.Add(insertedPatientDocuments); // insert in second table
    }
}

Solution

Mapping to views should be considered only for read-only scenarios, and complex ones at that. That example is probably a simplification, but as it stands, such a relationship does not warrant a view. Even where a view might be beneficial for flattening a complex, conditional arrangement of tables, there should also be an entity structure set up for the arrangement of tables and their relationships directly to perform updates with. (I.e. use the view to get a simpler set on Read, but then fetch the entity aggregate root or specific entities on update, not attempt to update through a view)

That example is suited to either a common one-to-many or an enforced one-to-one data partitioning strategy which EF has no issue respecting either as a traditional join or an "owned" relationship. If it is a one-to-many relationship then it doesn't make any sense at all to use a view. A patient_documents table could either be a one-to-many where you have an Id and a PatientId with a DocumentType (I.e. "passport" or "driverLicense", etc.) and a DocumentData (the value for that document type) or a one-to-one relationship which can have a PatientId but typically would share the same PK as it's corresponding patient. (patient_documents.Id = patient_info.Id) In this sense the table columns would be something like Id, passport_data, driverlicence_data, etc. The one-to-one relation can have a separate Id vs. PatientId but it is honestly not needed.

The entity declarations would be on Patient Info and Patient Documents.

[Table("patient_info")
public class PatientInfo
{
    [Column("id")]
    public int Id { get; protected set;}
    [Column("name")]
    public string Name { get; set; }

    // For a one-to-one relationship
    public virtual PatientDocument PatientDocument { get; set; }

    // Or for a one-to-many relationship
    //public ICollection<PatientDocument> PatientDocuments { get; protected set; } = new[];
}

[Table("patient_documents")]
public class PatientInfo
{
    [Column("id")]
    public int Id { get; protected set;}
    // for one-to-many, also add a DocumentType and DocumentData rather than PassportData...
    //[Column("patient_id")]
    //public string PatientId { get; protected set; }

    // Suitable for one-to-one relationship
    [Column("passport_data")]
    public string PassportData { get; set; }

    // or suited to a one-to-many relationship
    //[Column("document_typeId")]
    //public DocumentTypeIds DocumentTypeId { get; set; } // Enum for types of document, I.e. "Passport"
    //[Column("document_data")]
    //public string DocumentData { get; set; }
}

Then when configuring the entities:

modelBuilder.Entity<PatientInfo)()
    .HasOne(pi => pi.PatientDocument)
    .WithOne();

If you want to use a separate PatientId instead of joining on the PKs then you also need to add:

    .HasPrincipalKey<PatientDocument>(pd => pd.PatientId);

If it's one-to-many:

modelBuilder.Entity<PatientInfo)()
    .HasMany(pi => pi.PatientDocuments)
    .WithOne()
    .HasForeignKey(pd => pd.PatientId);

So instead of a view in the database to get a "Patient", create a Patient ViewModel in your class:

[Serializable]
public class PatientViewModel
{
    public int PatientId { get; set; }
    public string Name { get; set; }
    public string? PassportData { get; set; }
}

Now when you go to read your data to get that view model you leverage EF's projection to populate your view model. For a one-to-one relationship:

var patients = _context.PatientInfo
    .Select(pi => new PatientViewModel
    {
        PatientId = pi.PatientId,
        Name = pi.Name,
        PassportData = pi.PatientDocument.PassportData
    }).ToList();

if you have a one-to-many relationship with documents then:

var patients = _context.PatientInfo
    .Select(pi => new PatientViewModel
    {
        PatientId = pi.PatientId,
        Name = pi.Name,
        PassportData = pi.PatientDocuments
           .Where(pd => pd.DocumentTypeId == DocumentTypeIds.Passport)
           .Select(pd => pd.DocumentData)
           .FirstOrDefault()
    }).ToList();

When you want to update a patient or a patient document, simply fetch the PatientInfo entity by ID, and eager load the patient document(s) if you want to update. For instance to update the passportdata on a patient in a one-to-one relationship:

var patient = _context.PatientInfos
    .Include(pi => pi.PatientDocument)
    .Single(pi => pi.Id == request.PatientId);
patient.Name = request.Name;
patient.PatientDocument.PassportData = request.PassportData;
_context.SaveChanges();