CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
mountaindweller
mountaindweller

Reputation: 85

Linq returning different result than SQL

I'm running this Linq query:

        var patientList = from p in db.Patients
                          where p.ClinicId==11
            select p.Id;
        var patientswithplan = from p in db.Plans
            where patientList.Contains(p.PatientId)
            select p;

It returns 1030 results.

But when I came up with this query I wrote it in sql first to test it out and this displays 956 results

select id from patients where clinicid=11 
and id in(select patientid from plans)  
order by id

I thought these queries would be the same, what is the difference, which one is correct?

Upvotes: 0

Views: 194

Answers (2)

Mehrad
Mehrad

Reputation: 4203

I have written a little code then you could see the difference yourself

void Main()
{
    var Plans = new List<Plan>();
    Plans.Add(new Plan() {PatientId = 1, PlanName = "Good Plan"});
    Plans.Add(new Plan() {PatientId = 2, PlanName = "Bad Plan"});
    var Patients = new List<Patient>();
    Patients.Add(new Patient() {ClinicId = 1, Name = "Frank"});
    Patients.Add(new Patient() {ClinicId = 2, Name = "Fort"});

   // This is your LINQ     
   var patientList = from p in Patients
                     where p.ClinicId == 1
       select p.ClinicId;
   var patientswithplan = from p in Plans
       where patientList.Contains(p.PatientId)
       select p;
   Console.WriteLine(patientswithplan);
   // We return a PLAN here
   // Result
   // IEnumerable<Plan> (1 item) 
   // PatientId 1
   // PlanName  Good Plan

   // This is the equivalent Linq of your SQL    
   var myPatient = Patients.Where(
                           pa => pa.ClinicId == 1 && 
                           Plans.Any(pl => pl.PatientId == pa.ClinicId)
                                 );
   Console.WriteLine(myPatient);
   // Look! We return a PATIENT here
   // Result
   // IEnumerable<Patient> (1 item) 
   // ClinicId  1
   // Name      Frank
}

// Define other methods and classes here
class Patient
{
    public Patient() {}
    public int ClinicId { get; set; }
    public string Name { get; set; }
}

class Plan
{
   public Plan() {}
   public int PatientId { get; set; }
   public string PlanName { get; set; }   
}

Upvotes: 2

msporek
msporek

Reputation: 1197

The queries do two different things:

1) The first query is basically first getting a list of patients, and then it's fetching plans (you choose "from p in db.Plans") that have those selected patients in their list of patients.

2) The second query is filtering and fetching patients of given clinic making sure that those patients exist in some plans.

So of course the number of results will be different as you probably have a different number of rows in the patients and plans tables.

Upvotes: 1

Related Questions