Unable to convert SQL query to Entity Framework Core

asp.net-core asp.net-core-2.0 entity-framework entity-framework-6 entity-framework-core

Question

I am unable to convert my SQL query to Entity Framework Core.

The SQL query is:

select * 
from tests 
join TestParameters on (tests.Id = TestParameters.TestId) 
join PatientTests on (Tests.Id = PatientTests.TestId) 
where PatientId = 3

The models are shown here:

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

public class TestParameter
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Categories { get; set; }

    [Required]
    [Display(Name = "Test Parameter Name")]
    public string ParameterName { get; set; }

    [Required]
    public string Unit { get; set; }

    [Display(Name ="Decimal Point")]
    public int DecimalPoint { get; set; }

    [Display(Name = "Help Value")]
    public string HelpValue { get; set; }

    [Display(Name = "Normal Range")]
    public string NormalRange { get; set; }

    public string Minimum { get; set; }

    public string Maximum { get; set; }

    [Display(Name="Test Footer")]
    public string TestFooter { get; set; }

    [Display(Name = "Tests Name")]
    public int TestId { get; set; }

    [ForeignKey("TestId")]
    //relation of departments table
    public virtual Tests Tests { get; set; }
}

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

PatientTests contains relations of patient, tests, testparameters.

I tried to convert it but it's returning single data.

My code is:

var test = await _db.TestParameters
                    .Include(p => p.Tests)
                    .Where(p => p.Tests.Id == pttp.patient.Id)
                    .ToListAsync();
1
0
4/6/2020 6:02:09 AM

Accepted Answer

You might split the query into two queries.

1) Select patient tests

var patientTests = _db.PatientTests
    .Include(x => x.Test)
    .Where(x => x.PatientId == pttp.parient.Id)
    .ToList();

2) Select parameters of the tests

var testIds = patientTests.Select(x => x.TestId).ToList();
var testParameters = _db.TestParameters
    .Where(x => testIds.Contains(x.TestId))
    .ToList();
0
4/6/2020 6:27:25 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