FHIR crud app using aspnet core 8.0 and sql server
Tags: microsoft, hl7, sqlserver, apnetcore
Published: 2024-07-13 11:35:58

Hi EveryOne!
I would like to discuss today, the crud operation for the patient resource using HL7 R4 model using aspnet core and saving of patient data on sql server using EF Core. For modelling of data i have used this package.
Ref- https://www.nuget.org/packages/Hl7.Fhir.R4, https://www.hl7.org/fhir/resource.html#identification
Here each resource is uniquely identified by the id. I have given Id as database generated, we dont need to specifically specify that while sending request. So basically, this is my PatientEntity class.

```
   public class PatientEntity
  {
      [Key]
      [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      public string Id { get; set; } = Guid.NewGuid().ToString();


      [Required]
      public string FamilyName { get; set; } = string.Empty;

      [Required]
      public string GivenName { get; set; } = string.Empty;

      [Required]
      public string Gender { get; set; } = string.Empty;

      [Required]
      public DateTime BirthDate { get; set; } = DateTime.MinValue;

      public byte[]? PhotoData { get; set; }

      public static PatientEntity FromFhirPatient(Patient patient)
      {
          byte[]? photoData = null;
          if(patient.Photo != null && patient.Photo.Count > 0 && patient.Photo[0].Data != null)
          {
              photoData = patient.Photo[0].Data;
          }
          DateTime birthDate = patient.BirthDateElement?.ToDateTimeOffset()?.DateTime ?? DateTime.MinValue;

          return new PatientEntity
          {
              Id = patient.Id ?? Guid.NewGuid().ToString(),
              FamilyName = patient.Name.FirstOrDefault()?.Family ?? string.Empty,
              GivenName = patient.Name.FirstOrDefault()?.Given.FirstOrDefault()?? string.Empty,
              Gender = patient.Gender.HasValue ? patient.Gender.Value.ToString(): string.Empty,
              BirthDate = birthDate,
              PhotoData = photoData
          };
      }

      public Patient ToFhirPatient()
      {
          return new Patient
          {
              Id = Id,
              Name = new List<HumanName>
              {
                  new HumanName
                  {
                      Family = FamilyName,
                      Given = new List<string> { GivenName }
                  }
              },
              Gender = !string.IsNullOrEmpty(Gender) ? (AdministrativeGender)Enum.Parse(typeof(AdministrativeGender), Gender, true) : null,
              BirthDate = BirthDate.ToString("yyyy-MM-dd"),
              Photo = PhotoData != null ? new List<Attachment> { new Attachment { Data = PhotoData } } : null
          };
      }

```
The properties defined above are as per HL7 R4 model and then i have defined two static methods viz. FromFhirPatient(converts a Patient object from Fhir model to PatientEntity object) and ToFhirPatient(converts a PatientEntity object to a Fhir Patient object).

The details for modelling classes could be find here. we can customize accordingly. 
Flag `Σ` means its a modifier element, and it can change interpretation of resource, and is helpful for client to search summary only from large resource of data and helps in improving efficiency, and then there is cardinality ex.(0..1) meaning this particular field can appear minimum 0 times and maximum of 1. More details for data modelling can be find here Ref- `https://www.hl7.org/fhir/patient.html`

The blow code is for configuring DbContext.
```
        public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }
        
        public DbSet<PatientEntity> Patients { get; set; }
   
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }
```

The constructor here `public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }` Here ApplicationDbContext is properly configured by Dependency injection system which helps in SOC and it helps in configuring options for DbContext which typically includes things like database connection etc inside Program.cs which i will discuss next.
`base(options)` passes the options parameters to the base DbContext constructor.

This property `public DbSet<PatientEntity> Patients { get; set; }` creates a table inside database 
ModelBuilder class provides API surface for configuring a DbContext to map entities to db schema.


This is my Program class 
```
using Hl7.Fhir.Model;
using Hl7.Fhir.Serialization;
using Microsoft.EntityFrameworkCore;
using NetCrudApp.Data;
using System.Text.Json.Serialization;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers()
    .AddJsonOptions(opt =>
    {
        IList<JsonConverter> fhirConverters = opt.JsonSerializerOptions.ForFhir(ModelInfo.ModelInspector).Converters;
        IList<JsonConverter> convertersToAdd = new List<JsonConverter>(fhirConverters);
        foreach(JsonConverter fhirConverter in convertersToAdd)
        {
            opt.JsonSerializerOptions.Converters.Add(fhirConverter);
        }
        opt.JsonSerializerOptions.Encoder = System.Text.Encodings.Web.JavaScriptEncoder.UnsafeRelaxedJsonEscaping;
    });

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
});

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddHttpClient(); // added this line to converse with fhir server microsoft



var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();
using (var scope = app.Services.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    await dbContext.Database.MigrateAsync();
}

app.Run();

``` 
Here, in this line
```
        IList<JsonConverter> fhirConverters = opt.JsonSerializerOptions.ForFhir(ModelInfo.ModelInspector).Converters;
        IList<JsonConverter> convertersToAdd = new List<JsonConverter>(fhirConverters);
        foreach(JsonConverter fhirConverter in convertersToAdd)
        {
            opt.JsonSerializerOptions.Converters.Add(fhirConverter);
        }
        opt.JsonSerializerOptions.Encoder = System.Text.Encodings.Web.JavaScriptEncoder.UnsafeRelaxedJsonEscaping;
```
is required for Serialization of data, otherwise this will throw error `The collection type 'Hl7.Fhir.Model.Patient' is abstract, an interface, or is read only, and could not be instantiated and populated.` 
Also, we need to initialize a new converter as `IList<JsonConverter> convertersToAdd = new List<JsonConverter>(fhirConverters);` else we will end up in modifying the original list which is not of course we want. Also, this line `opt.JsonSerializerOptions.Encoder = System.Text.Encodings.Web.JavaScriptEncoder.UnsafeRelaxedJsonEscaping;` allows characters that are particularly escaped like &, <, > to remain unescaped, which is useful for interoperability and readability of json output but it should be cautiously used. In summary this converter is used for serialization and deserialization of fhir model data which is of complex type and is challenging to perform **deserialization** for saving data on database and ** serializing** while returning response to client using native converters like NewtonSoft. Ref here - `https://github.com/FirelyTeam/firely-net-sdk/issues/2583`

This line ```
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
});
```
specifies the connection string for the app which is defined inside appSetting.json. For simplicity sake i have just defined the variables inside my appsettings.json as below as we are developing is using local sql server only. For azure deployment, we can configure services to manage secrets properly. appsettings.json is as below, it simply specifies the connection string and logging level, if we want more info from logging just change from `warning` to `Information` inside appsettings.json or appsettings.Development.json
```
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=FhirLocalDb;Integrated Security=true;TrustServerCertificate=true;"
  }
}
```
Further, this line inside Program.cs 
```
using (var scope = app.Services.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    await dbContext.Database.MigrateAsync();
}
```
is used to apply any pending migration. But before that we need to add migration and update database using below commands.
```
dotnet ef migrations add InitialCreate
dotnet ef database update
```

Next, this is my PatientController class
```
[Route("fhir/patient")]
[ApiController]
public class PatientResourceProvider : ControllerBase
{
    private readonly ApplicationDbContext _context;

    public PatientResourceProvider(ApplicationDbContext context)
    {
        _context = context;
    }

    [HttpGet("{id}")]
    public async Task<IActionResult> GetPatient(string id)
    {
        try
        {
            //https://stackoverflow.com/questions/62899915/converting-null-literal-or-possible-null-value-to-non-nullable-type
            PatientEntity? patientEntity = await _context.Patients.FindAsync(id);
            if(patientEntity != null)
            {
                return Ok(patientEntity);
            }else
            {
                return NotFound(new { Message = "Patient not found" });
            }

        }catch(FhirOperationException ex) when (ex.Status == System.Net.HttpStatusCode.NotFound)
        {
            return NotFound(new { Message = "Patient not found" });
        }catch(Exception ex)
        {
            return StatusCode(500, new { Message = "Ann error occurred", Details = ex.Message });
        }
    }

    [HttpPost]
    public async Task<IActionResult> CreatePatient([FromBody] Patient patient)
    {
        using var transaction = await _context.Database.BeginTransactionAsync();
        try
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            PatientEntity patientEntity = PatientEntity.FromFhirPatient(patient);
            _context.Patients.Add(patientEntity);
            await _context.SaveChangesAsync();

            await transaction.CommitAsync();

            return CreatedAtAction(nameof(GetPatient), new { id = patientEntity.Id }, patientEntity.ToFhirPatient());
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();  
            return StatusCode(500, new { Message = "An error occurred", Details = ex.Message });
        }
    }

    [HttpPut("{id}")]
    public async Task<IActionResult> UpdatePatient(string id, [FromBody] Patient patient)
    {
        if(id == null)
        {
            return BadRequest(new {Message = "Patient id cannot be null"});
        }

        using var transaction = await _context.Database.BeginTransactionAsync();

        try
        {
            PatientEntity? patientEntity = await _context.Patients.FindAsync(id);
            if(patientEntity == null)
            {
                return NotFound(new { Message = "Patient not found" });
            }

            if(patient.Name == null || patient.Name.Count == 0 || string.IsNullOrEmpty(patient.Name[0].Family)) {
                return BadRequest(new { Message = "Patient name is required" });
            }

            patientEntity.FamilyName = patient.Name.FirstOrDefault()?.Family ?? string.Empty; 
            patientEntity.GivenName = patient.Name.FirstOrDefault()?.Given.FirstOrDefault() ?? string.Empty;
            patientEntity.Gender = patient.Gender.HasValue ? patient.Gender.Value.ToString().ToLower() : string.Empty;
            patientEntity.BirthDate = patient.BirthDateElement?.ToDateTimeOffset()?.DateTime ?? DateTime.MinValue;

            _context.Entry(patientEntity).State = EntityState.Modified;

            await _context.SaveChangesAsync();

            await transaction.CommitAsync();

            return NoContent();
        }catch(DbUpdateConcurrencyException) 
        {
            if(!_context.Patients.Any(e => e.Id == id))
            {
                return NotFound(new { Message = "Patient not found" });
            }
            else
            {
                throw;
            }
        }catch(Exception ex)
        {
            await transaction.RollbackAsync();
            return StatusCode(500, new { Message = "An error occurred", Details = ex.Message });
        }
    }


    [HttpDelete("{id}")]
    public async Task<IActionResult> DeletePatient(string id)
    {
        using var transaction = await _context.Database.BeginTransactionAsync();
        try
        {
            var patientEntity = await _context.Patients.FindAsync(id);
            if(patientEntity == null)
            {
                return NotFound(new { Message = "Patient not found" });
            }
            _context.Patients.Remove(patientEntity);
            await _context.SaveChangesAsync();
            await transaction.CommitAsync();
            return NoContent();
        }catch(Exception ex)
        {
            await transaction.RollbackAsync();
            return StatusCode(500, new { Message = "An error occurred", Details = ex.Message });
        }
    }

    [HttpGet("search")]
    public async Task<IActionResult> SearchPatient([FromQuery] string? name = null, string? id = null)
    {
        try
        {
            IQueryable<PatientEntity> query = _context.Patients;
            if (!string.IsNullOrEmpty(name))
            {
                query = query.Where(n =>  n.FamilyName == name || n.GivenName == name);
            }
            if (!string.IsNullOrEmpty(id))
            {
                query = query.Where(p => p.Id == id);
            }

            var patients = await query.ToListAsync();
            if (!patients.Any())
            {
                return NotFound(new { Message = "No Patient found" });
            }
            return Ok(patients);
        }catch(Exception ex)
        {
            return StatusCode(500, new { Message = "An error occurred", Details = ex.Message });
        }
    }

}
```
Starting from Post request as below.
```
 [HttpPost]
 public async Task<IActionResult> CreatePatient([FromBody] Patient patient)
 {
     using var transaction = await _context.Database.BeginTransactionAsync();
     try
     {
         if (!ModelState.IsValid)
         {
             return BadRequest(ModelState);
         }
         PatientEntity patientEntity = PatientEntity.FromFhirPatient(patient);
         _context.Patients.Add(patientEntity);
         await _context.SaveChangesAsync();
         await transaction.CommitAsync();

         return CreatedAtAction(nameof(GetPatient), new { id = patientEntity.Id }, patientEntity.ToFhirPatient());
     }
     catch (Exception ex)
     {
         await transaction.RollbackAsync();  
         return StatusCode(500, new { Message = "An error occurred", Details = ex.Message });
     }
 }
```
We start writing defensively to catch any errors, first database transaction is started asynchronously to maintain integrity of data saved on sql server. If any parts fails here it will Rollback the transaction. Then here `if (!ModelState.IsValid)
         {
             return BadRequest(ModelState);
         }` model state is verified as per the attributes and rules defined inside Patient model class, if there is model validation error it will throw 400.

Then this line of code `PatientEntity patientEntity = PatientEntity.FromFhirPatient(patient);` will **deserialize** data from entity class in order to save data on database.

These three lines `_context.Patients.Add(patientEntity);
         await _context.SaveChangesAsync();
         await transaction.CommitAsync();` will add new patient based upon request from body and save in db and commit transaction. 
If any error is thrown transactionwill be rolled back here `await transaction.RollbackAsync(); ` and in case of any error it will be catched inside catch block. The successful post request in postman would look something like this. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8p956z1jtb4tbzuezz3o.png)


Similarly, for get request here 
```
 [HttpGet("{id}")]
 public async Task<IActionResult> GetPatient(string id)
 {
     try
     {
         PatientEntity? patientEntity = await _context.Patients.FindAsync(id);
         if(patientEntity != null)
         {
             return Ok(patientEntity);
         }else
         {
             return NotFound(new { Message = "Patient not found" });
         }

     }catch(FhirOperationException ex) when (ex.Status == System.Net.HttpStatusCode.NotFound)
     {
         return NotFound(new { Message = "Patient not found" });
     }catch(Exception ex)
     {
         return StatusCode(500, new { Message = "Ann error occurred", Details = ex.Message });
     }
 }
```
We first try to find a given entity using primary id here `PatientEntity? patientEntity = await _context.Patients.FindAsync(id);` Here **PatientEntity?** question mark is used in order to ward off `warning	CS8600	Converting null literal or possible null value to non-nullable type.`	
If patientEntity is not null, then a matching patientEntity is returned from db here  using response as `return Ok(patientEntity);` else the errors are catched inside catch blocks. **FhirOperationException** is derived from base Exception class and it represents HL7 FHIR errors that occur during application execution.
Successful, Postman request for get request is as below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t6ye19p11bigne43j5v4.png)
Likewise, for put operation here 
```
 [HttpPut("{id}")]
 public async Task<IActionResult> UpdatePatient(string id, [FromBody] Patient patient)
 {
     if(id == null)
     {
         return BadRequest(new {Message = "Patient id cannot be null"});
     }

     using var transaction = await _context.Database.BeginTransactionAsync();

     try
     {
         PatientEntity? patientEntity = await _context.Patients.FindAsync(id);
         if(patientEntity == null)
         {
             return NotFound(new { Message = "Patient not found" });
         }

         if(patient.Name == null || patient.Name.Count == 0 || string.IsNullOrEmpty(patient.Name[0].Family)) {
             return BadRequest(new { Message = "Patient name is required" });
         }

         patientEntity.FamilyName = patient.Name.FirstOrDefault()?.Family ?? string.Empty; 
         patientEntity.GivenName = patient.Name.FirstOrDefault()?.Given.FirstOrDefault() ?? string.Empty;
         patientEntity.Gender = patient.Gender.HasValue ? patient.Gender.Value.ToString().ToLower() : string.Empty;
         patientEntity.BirthDate = patient.BirthDateElement?.ToDateTimeOffset()?.DateTime ?? DateTime.MinValue;

         _context.Entry(patientEntity).State = EntityState.Modified;

         await _context.SaveChangesAsync();

         await transaction.CommitAsync();

         return NoContent();
     }catch(DbUpdateConcurrencyException) 
     {
         if(!_context.Patients.Any(e => e.Id == id))
         {
             return NotFound(new { Message = "Patient not found" });
         }
         else
         {
             throw;
         }
     }catch(Exception ex)
     {
         await transaction.RollbackAsync();
         return StatusCode(500, new { Message = "An error occurred", Details = ex.Message });
     }
 }
```
First, null check of id is done which is found inside query parameter of request url and then to update the given property is locked using this block as in post request to maintain data integrity inside db here `using var transaction = await _context.Database.BeginTransactionAsync();`
Here in this line `    if(patientEntity == null)
    {
        return NotFound(new { Message = "Patient not found" });
    }` if patient with given id is not found, then 404 not found is returned as response with its according message.
Here `                if(patient.Name == null || patient.Name.Count == 0 || string.IsNullOrEmpty(patient.Name[0].Family)) {
                    return BadRequest(new { Message = "Patient name is required" });
                }` it validates the Patient incoming object. And it checks if Name property of patient object is null or if Name count is zero or if the family Name of the first HumanName is empty or null will throw 400 validation error as below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/93m9xzy3fgg5s5p0ylaq.png)

Here, in these four lines FamilyName, GivenName, Gender and BirthDate are mapped to either the values provided by the client or else will be mapped to empty string. Only, BirthDate is first converted to DateTimeOffset and then to DateTime and if conversion fails or is null then it assigns DateTime.MinValue;

This line `_context.Entry(patientEntity).State = EntityState.Modified;` marks the patient entity to be modified and tells EF core that **patientEntity** has been modified and needs to be updated in database.

And another two lines here `                await _context.SaveChangesAsync();

                await transaction.CommitAsync();` save changes in database and commit transaction and returns 204 no content. Else the error is catched inside catch block.

Successful postman request is 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zuvxqigvbwirc5z6ywsa.png)

Note- We use Patient model as method parameter here since we have to adhere to HL7 Fhir model.

Further, delete method is also locked inside database transaction as well in order to maintain data integrity 
```
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeletePatient(string id)
        {
            using var transaction = await _context.Database.BeginTransactionAsync();
            try
            {
                var patientEntity = await _context.Patients.FindAsync(id);
                if(patientEntity == null)
                {
                    return NotFound(new { Message = "Patient not found" });
                }
                _context.Patients.Remove(patientEntity);
                await _context.SaveChangesAsync();
                await transaction.CommitAsync();
                return NoContent();
            }catch(Exception ex)
            {
                await transaction.RollbackAsync();
                return StatusCode(500, new { Message = "An error occurred", Details = ex.Message });
            }
        }
``` First patient is searched using id and then if patient with given id is found then this line `_context.Patients.Remove(patientEntity);` will successfully remove patientEntity from db only when this line `await _context.SaveChangesAsync();` is executed and transaction is complete and if there is any error, the transaction will be rolled back and error message is displayed accordingly.
Successful postman request for this operation is as here. 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p5h0ig95lyy0khie2u3c.png)

And the Search operation is by id or by name as here.
```
 [HttpGet("search")]
 public async Task<IActionResult> SearchPatient([FromQuery] string? name = null, string? id = null)
 {
     try
     {
         IQueryable<PatientEntity> query = _context.Patients;
         if (!string.IsNullOrEmpty(name))
         {
             query = query.Where(n =>  n.FamilyName == name || n.GivenName == name);
         }
         if (!string.IsNullOrEmpty(id))
         {
             query = query.Where(p => p.Id == id);
         }

         List<PatientEntity> patients = await query.ToListAsync();
         if (!patients.Any())
         {
             return NotFound(new { Message = "No Patient found" });
         }
         return Ok(patients);
     }catch(Exception ex)
     {
         return StatusCode(500, new { Message = "An error occurred", Details = ex.Message });
     }
 }
```
Since, we are directly interacting with database here that's why i have used `IQueryable<PatientEntity> query = _context.Patients;` **PatientEntity** type instead of Patient type. Then null and empty string check operation is performed for both id and name and the results are filtered using Where extension method. This line `List<PatientEntity> patients = await query.ToListAsync();` results a list of patients matching the records from db and if any error it will be catched inside catch block. Successful postman request is as below.

**Search by name**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nh5bj15yea4rwtnbmt74.png)

Search by id 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/claaap7ler9cxvmfmznr.png)

You can close use/play with below github. No permission is required. In case of any issue feel free to message me i will try to respond asap!

Repo- https://github.com/mannawar/fhir-msft-sqlserver

Thanks for your time!