Convert query from SQL to Entity Framework code first approach

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

Question

I'm trying to translate my SQL query into Entity Framework code, but I'm having trouble.

Here is my SQL command.

select * from tests where id in(select testid from PatientTests where PatientId=@id)

This is a test model, and I want to get records from it.

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; }

}

Model for patient testing

 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; }

}

I thus want a record from the tests database where the patient's ID must match. Only the specified patient Id record and the test table testid must be fetched.

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

Popular Answer

Your Tests It seems that the model lacks a navigation feature.PatientTest . But it's still possible.

I'm speculating a little bit on the names of your context attributes here.

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