Please, read my comment to the question first.
As i mentioned your query is not efficient due to the usage of [
in
] clause two times.
Let's try to improve your sql query. So, this:
select * from PatVisit p
inner join Device d on d.PattId = p.PatId and d.SyncDate is not null
where p.VisitDate in
( select max(VisitDate) from PatVisit pv where VisitId in (4,7,2,8,9) group by PatId )
and DATEDIFF(d,p.visitDate,GETDATE()) <10
can be replaced with this:
DECLARE @finaldate DATETIME
SELECT @finaldate = DATEADD('d', -10, GETDATE())
SELECT p.*
FROM
(
SELECT p1.*, ROW_NUMBER() OVER(PARTITION BY p1.PatId ORDER BY p1.VisitDate DESC) RowNo
FROM PatVisit p1
WHERE p1.VisitDate >= @finaldate AND p1.VisitId IN(4,7,2,8,9)
) p INNER JOIN Device d on d.PattId = p.PatId and d.SyncDate is not null
WHERE p.RowNo=1;
As you can see, i've used
ROW_NUMBER() function[
^], which is very useful to get only the first row from nested query.
Note, that
WHERE
condition does not contain
DATEDIFF[
^] function, because you can limit your data by compare
VisitDate
to the final date (stored in a
@finaldate
variable).
Above query could be - probably - improved more, but i have no access to your data and don't see your data structure...
As to the linq query...
There's no corresponding [
IN
] clause in Linq, but... we can use
Where[
^] together with
Any[
^] method. See:
int[] vid2get = new int[]{4,7,2,8,9};
var qry1 = PatVisit_context
.Where(x=> vid2get.Any(y=>y==x.VisitId));
So, the final version of linq query may look like:
int[] vid2get = new int[]{4,7,2,8,9};
DateTime finaldate = DateTime.Now.AddDays(-10);
var qry1 = (from pv in PatVisit_context
.Where(x=> vid2get.Any(y=>y==x.VisitId) && x.VisitDate >= finaldate)
.GroupBy(x=> x.PatId)
.Select(grp=> grp.OrderByDescending(y=>y.VisitDate).First())
join d in Device_context on pv.PatId equals d.PatId && d.SyncDate != null
select pv)
.ToList();
Good luck!