I want to convert my SQL query into Entity Framework code-first but unable to do it.
This is my SQL query
select * from tests where id in(select testid from PatientTests where PatientId=@id)
This is Test Model from this model I want to fetch records.
public class Tests
{
[Key]
public int Id { get; set; }
[Required]
[Display(Name = "Test Name")]
public string TestName { get; set; }
[Display(Name = "Short Name")]
public string ShortName { get; set; }
[Display(Name="Technical Name")]
public string TechName { get; set; }
[Required]
[Display(Name ="Test Price")]
public float TestPrice { get; set; }
[Display(Name = "Sub Department")]
public int SubDeptId { get; set; }
[Display(Name = "Center")]
public int CenterId { get; set; }
public string Separate { get; set; }
[Display(Name = "Sub Department")]
[ForeignKey("SubDeptId")]
//relation of departments table
public virtual SubDepartments subDepartments { get; set; }
[Display(Name = "Centers")]
[ForeignKey("CenterId")]
//relation of departments table
public virtual Centers centers { get; set; }
}
this is patient tests model
public class PatientTest
{
[Key]
public int Id { get; set; }
[Display(Name ="Patient Id")]
public int PatientId { get; set; }
[Display(Name ="Test Id")]
public int TestId { get; set; }
[Display(Name ="Doctor")]
public int DoctorId { get; set; }
[Display(Name="Center")]
public int CenterId { get; set; }
[Display(Name = "Test")]
[ForeignKey("TestId")]
//relation of Tests table
public virtual Tests Tests { get; set; }
[Display(Name = "Doctor Reference")]
[ForeignKey("DoctorId")]
//relation of Doctors table
public virtual Doctors Doctors { get; set; }
[Display(Name = "Center Reference")]
[ForeignKey("CenterId")]
//relation of Centers table
public virtual Centers Centers { get; set; }
[Display(Name = "Patient")]
[ForeignKey("PatientId")]
//relation of Patient table
public virtual Patient Patient { get; set; }
}
So I want record from tests table where id should be matched with patientTest table testid and only given patient Id record must be fetch.
Your Tests
model seems to be missing a navigation property to PatientTest
. It can still be done though.
Guessing a bit here for how your context properties are named.
var tests = context.PatientTests
.Where(pt => pt.PatientId == patientId)
.Select(pt => pt.Tests)
.ToList();