Click here to Skip to main content
15,570,202 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I am new to writing linq queries. I did write SQL query which is giving results as expected , I need to convert that in to LINQ c# query,we don't have permissions to install Linqer , Can you please anyone help me on this. Thank you. Here is my SQL query

SQL
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


What I have tried:

Tried LinqPad but I am unable to convert there.
Posted
Updated 17-Dec-21 0:30am
v2
Comments
[no name] 25-Feb-20 15:50pm    
"Tried LinqPad but I am unable to convert there."
*yawn*
a.) What was _exactly_ the problem, where you failed?
b.) Any atempt to try something other?
Maciej Los 25-Feb-20 16:41pm    
You're using in clause twice, so your query is inefficient. You should rethink it!

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:
SQL
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:
SQL
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:
C#
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:

C#
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!
 
Share this answer
 
v2
Comments
phil.o 26-Feb-20 3:37am    
5'd
Maciej Los 26-Feb-20 3:51am    
Thank you, Olivier.
chinnuTest 26-Feb-20 14:06pm    
Thank you so much for detailed query,i really appreciated. you made my day... the query you have given works perfect. But you didn't join Device table to it if possible can you please join that Device table and condition to below linq query ?

int[] vid2get = new int[]{4,7,2,8,9};
DateTime finaldate = DateTime.Now.AddDays(-10);
var qry1 = 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())
.ToList();
Maciej Los 26-Feb-20 14:20pm    
Check updated answer.
chinnuTest 26-Feb-20 17:41pm    
Thank you so much.. Appreciated for your help...
First Port of Call

Basic LINQ Query Operations (C#) | Microsoft Docs[^]


secondly look over examples here

Using LINQ Queries[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900