Convert query from SQL to Entity Framework code first approach

entity-framework entity-framework-6 entity-framework-core sql-server

Question

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.

1
0
3/28/2020 2:38:26 AM

Popular Answer

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();
0
3/28/2020 2:49:22 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow