SELECT the patients that have similar disease name EF core 3.1

I have the following tables

1. Patients

     ID   PatientName   PatientIDNumber  
    ---- ------------- ----------------- 
      1   Jo                  411420607  
      2   Mark                206047758  
   

1. Records

     ID    DiseaseName    PatinetID  
    ---- --------------- ----------- 
      1   Liver Disease           1  
      2   Heart Disease           1  
      3   Liver Disease           2  
      4   Heart Disease           2  
   

Each patient has many records

• Patient Data model class

  public class PatientEntity : BaseEntity
       {
           [Column("PatientName")]
           [StringLength(150)]
           public string Name { get; set; }
  
           [Column("OfficialIDNumber")]
           [StringLength(10), MinLength(10)]
           public string IDNumber { get; set; }
  
           [Column("SystemIDnumber")]
           public int SystemID { get; set; }
  
           [Column("PatientDateOfBirth")]
           public DateTime? DateOfBirth { get; set; }
  
           [Column("PatientEmailAdress")]
           [StringLength(300)]
           public string EmailAdress { get; set; }
  
           public IEnumerable<LookupsEntity> MetaData { get; set; }
  
           public IEnumerable<RecordEntity> Records { get; set; }
       }
  

• Record data model class

  public class RecordEntity : BaseEntity
   {
       [StringLength(50)]
       public string DiseaseName { get; set; }
  
       public DateTime TimeOfEntry { get; set; }
  
       [StringLength(300)]
       public string Description { get; set; }
  
       [ForeignKey("Patient")]
       public int PatientId { get; set; }
       public PatientEntity Patient { get; set; }
  
       public BillEntity Bill { get; set; }
  
   }
  

I want to create an API that takes a specific patient id as a parameter to get a list of other patients with similar diseases

• Patient report API result (DTO)

  public class PatinetReportResource
   {
       public PatientResource patient { get; set; }
       public IList<PatientResource> SimilarPatinets { get; set; }
   }
  

Note: *Similar diseases mean that the two patients have in common 2 or more disease

How to List patients with similar diseases in EF core 3.1?

Answer

This is the bare bones of what you need, without relying on your disease names being in the same order.

If you need additional info like PatientName, you can just add them in by wrapping the select below in a cte and then join onto the Patient data:

As a fiddle.

declare @p table(ID int,PatientName varchar(10),PatientIDNumber int);
insert into @p values
 (1,'Jo',411420607)
,(2,'Mark',206047758)
,(3,'Paul',552065834)
,(4,'Lisa',653025132);

declare @r table(ID int,DiseaseName varchar(30),PatientID int);
insert into @r values
 (1,'Liver Disease',1)
,(2,'Heart Disease',1)
,(3,'Liver Disease',2)
,(4,'Heart Disease',2)
,(5,'Liver Disease',3)
,(6,'Lung Disease',3)
,(7,'Arm Disease',4)
,(8,'Lung Disease',4)
,(9,'Liver Disease',4)
,(10,'Heart Disease',4);

declare @id int = 1;

select r.PatientID
        ,r2.PatientID as MatchedPatientID
from @r as r
    join @r as r2
        on r.DiseaseName = r2.DiseaseName
            and r.PatientID <> r2.PatientID
where r.PatientID = @id
group by r.PatientID
        ,r2.PatientID
having count(r2.PatientID) >= 2;