sql generator performance with "in" queries

[bolcal]

Hi,
I am sure that there is a better way but I could not figure out..
The block below takes 60 seconds
var consultantApps = appointmentRepository
.FindAll(a => a.ConsultantProfile.Status && a.AppointmentStatuId == 3, a => a.ConsultantProfile);

but these 2 queries both takes less than 100 ms
SELECT * FROM Appointments A
where
A.AppointmentStatuId = 3 AND
A.ConsultantId in (select P.ConsultantId from ConsultantProfile P where P.Status =1)

OR

select * from Appointments A, ConsultantProfile P
where A.ConsultantId = P.ConsultantId
AND P.Status = 1 AND A.AppointmentStatuId = 3

Do you have any suggestion? What am I doing wrong?

Kind regards

[phnx47]

@bolcal Show query generated by

var consultantApps = appointmentRepository
  .FindAll(a => a.ConsultantProfile.Status && a.AppointmentStatuId == 3, a => a.ConsultantProfile);

How long does it take to execute this sql?

[phnx47]

@bolcal Did you check generated query? The generated query should be with 'join' not with 'in'.

[bolcal]

Hi,
sorry for late reply, it was my mistake, everythink is OK now, thank you for your reply.